Stock/Inventory System Design Ideas

you need to get the document in View A via a view - findbykey.

See the view methods in the design help.

You also need to make sure that you set a field in Form B, when you have finished updating the stock value, so that it is not done more than once.

I hope this helps !

GetDocumentByKey method is probably what you will need, assuming  you have a unique key for each item in stock.

I hope this helps !

By Jove, you're asking for something "design", the answers you get are "implementation", but IMHO you're still in the "analysis" phase. With the very short question: WHAT? What do I have to do, what do the users want. Then, later on, you'll go for the even shorter question: HOW? How am I going to do this, when I have to do this in Notes.

My suggestion:
- make an Entity-Relationship Diagram of the current application
- make an ERD of the application you have to add
- try to fit the two together.

How does that sound? Pretentious? Then I apologize ;-)) As always, I try to give some guidance...

Oh sorry SysExpert, didn't mean to offend you... :-$

The question asked was

My question is, how can I adjust the stock level from another document?


shuboarder : do you know how to do an ERD ?
if not, ask SJEF !


I hope this helps !

OOOOOOOH dear... When will I start to read questions and not interpret them...

Time to sneak out the backdoor again...

I do know how to do ERDs yes, and I have as always drawn out exactly the processes that need to take place.
I can see that I shouldn't have a problem with most of the processes. My main concern is the adjusting on view A from form B as I have not done anything like this before.

Thanks.

view A will contain stock (input through form A):

Apple
Apple
Apple
Pear
Pear
Pineapple
Orange

Form B will have a multivalue listbox that uses @dblookup on view A to get its values.
So for example, the user may select Apple, Pear, Orange.

When they save this document through form B I want an Apple, Pear and Orange to move from view A (stock) to view B (items used)

This means that the next time someone uses the listbox on form B they will only have the following options:

Apple
Apple
Pear
Pineapple

(unless someone has since adjusted stock levels of course)

Thanks

Hi SysExpert,

thanks for your reply.
I was thinking it would be easier to have 1 document per item of stock.
However, I would prefer to have a single document with quantity available.

Here are the fields I have on form A:

part_description (editable text to describe the stock)
part_number (editable text to describe the stock)
part_location (editable text to describe the location)
qty_in_stock (editable number to adjust the stock level)
approx_cost (editable number to adjust the approximate cost)
usual_supplier (combobox linked to another view to select the usual supplier)
reorder_level (editable number to flag when stock should be reordered)
compatible_with (multi value dialog list linked to a view which displays things that stock is compatible with.)
      - This returns string using fomula: department + " / " + area + " / " + equipment

N.B Documents created using the above form are displayed by view A

Form B:

has fields:

department (combobox which uses @dbcolumn to lookup view with department)
area (combobox which uses @dblookup to lookup view with area [based on department])
equipment (combobox which uses @dblookup to lookup view with equipment [based on area])

All of the above is currently in place....
Here is where I need help:

Form B should have following field:

parts_required (This should lookup view A to gain a list of compatible parts i.e. items that are in stock and match the department, area and equipment selected on form B)

When the document is saved the parts that have been selected should be removed from view A and placed into an archive view. (Also need a way of subsequent saving reducing stock levels further)

Hope this is a little clearer....?

Hi

thanks again...

In response to your question, it would be good to have an archive of stock used related to form B, but I can see this will be difficult so I wouldn't say its essential.

Your idea of adding a field to form A to handle the stock used sounds good - Simple ideas are usually the best :)

In terms of the Lotus Script....what should I be doing with this?

Thanks

Hi Sjef,

perhaps I should have been more clear...

> I do know how to do ERDs yes, and I have as always drawn out
> exactly the processes that need to take place.

I have done a ERD from which I have created a DFD
This, as I'm sure you know does involve detailing every process.

In terms of the system here, I do not want to make it too complex.
There should be no replication conflicts as this is all done on one server, and no local copies are involved.
There will only be one/two person(s) updating the stock levels up and a maximum of 5 people adjusting the stock down. I do like the sound of your idea a lot, but I wonder how difficult this is?

Hi SysExpert...

>>Use the function ( change as needed )
I'm gonna need more help on this...

Does all this go into the query save?
How much do I need to change?

I have added a field called quantity_used to form A (This will hopefully handle the history)

I have got the lookup from form B (field name: spares_required) however this is a multi value dialog list lookup which only returns the valid part name. Will I have to use something like @prompt in the query save to get the values from the user before adjusting stock levels?

Thanks for your help as always

Hi SysExpert,

thanks for your reply again.

That sounds ok, unless of course more than 1 part is used of the same type. i.e. 3 bolts etc.

I will need lots of help with the lotus script for this...
But basically I can see what needs to be achieved.
Something like this (non lotus script)...

If on save
spares_used = nothing
then goto end

else
set n = 1
do while n = nothing
get first item in spares_used field
prompt user for a quantity
if valid then
collect appropriate document from view A
add value to quantity_used
subtract value from quantity_in_stock
save document
n+1

else prompt "Entered quantity is not valid, please try again"

end

SysExpert,

that is also true....

Maybe instead of a dialog list, I should use a table in a layout region that pops up a form with perhaps 10 lines?

spare_used                 quantity_used
spare_used_1              quantity_used_1
spare_used_2              quantity_used_2

etc...

This may be more like the kind of thing Sjef was suggesting?

Hmmm...

sounds like sarcasm to me Sjef :)

Sorry if I seem to keep changing the goal posts here, but I seem to keep hitting a wall with the ideas and there always appears to be a better way of doing things. I know exactly what the system needs to do, it's the design and way it's going to work I'm struggling with.

I have now added, as I suggested a button to Form B that uses @dialogbox to bring up a layout region.
The user can select the part from a combobox and the enter the quantity used. When they press ok this adds the information to some hidden fields on Form B.

I can see 2 options now....

1. I'm thinking if I make quantity_used field on form A computed and make it the sum of a lookup of the part displayed by view B. This will get total quantity used per part. Yes?

I will then need to somehow calculate the quantity in stock on Form A by subtracting quantity_used from an initial value.

I will then need an agent to refresh the documents regularly?
Will this method cause problems if an item has just gone out of stock and then someone might be able to use the item again before the agent has updated the stock level?

2. I somehow try to use the query save so that when form B is saved it adjusts the document created using Form A there and then. I will also need to prevent subsequent saving of the document reducing the stock level further if I go this approach.

Thanks to you both for your help on this!

Ok, so its option 2....

Which seems more difficult.
So to recap, I have these fields on form B now:

spare_used                 quantity_used
spare_used_1              quantity_used_1
spare_used_2              quantity_used_2
spare_used_3              quantity_used_3
spare_used_4              quantity_used_4

and I will need something like the following:

If on save
spare_used | spare_used_1  | spare_used_2 | spare_used_3 | spare_used_4 = nothing
then goto end

else
using spare_used field as a key
collect appropriate document from view A
subtract quantity_used from quantity_in_stock
add quantity_used to quantity_used
Save the document

'Repeat for other spare_used fields

end

Hi SysExpert,

this is what keeps making me go round in circles...

Either way has benefits or flaws.

Another problem with this approach is like you say, I will have to prevent Form B from any further updating.
This is not possible! So I'm looking back at option 1 again....

I think this question is pretty much exhausted...

In the end I've gone for adding the parts used data to Form B directly through a layout region
then using a view to collate all the parts used.
Finally the stock view displays the inventory item documents - the sum of parts used.
(Basically option 1 above)

Positives:

This makes it easier to update
Gets around the issue of form B being updated further
No agents required

Negatives:

Takes about 1 minute to adjust stock levels after update to Form B

Overall:

With the small number of users (5) ever updaing stock, I compromised the instant stock level adjustment for the ease of setting this thing up.

Thanks for all your suggestions.

If there are no further comments, I shall close this question this week.

So it works? Congratulations!! And there's no sarcasm in that, because I know that these things are always very difficult to implement in Notes. Before everything is thoroughly tested... it takes ages...

Glad you found something that works for you !

Yes, it works very well.

After updating Form B, you do have time to get back to view A and see the stock level adjust on refresh,
I just added a refresh formula command to the query open event on the stock document. So that going into the document shows the real-time stock quantity.

similar to ebay... sometimes an auction will say it has 30 minutes left, but when you actually open up the document it only has 10. - Not saying that ebay is Notes based, just trying to explain that it is similar behaviour.

Not great, but quite impressive for Notes in my opinion.

Thanks to both of you for your advice and help!