Avatar of shuboarder
shuboarder
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Stock/Inventory System Design Ideas

I need to design a stock/inventory system in Lotus Notes.

This is to form part of an already existing database.

I imagine I will use a form to allow users to add/adjust stock levels, then have a view to display the stock.
In an already developed part of the database I will have an area that will allow users to say which stock they have used.

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

i.e. if user A adds stock of 5 bolts through form A
user B then says that he has used 2 bolts through form B

how can I get stock to show at 3?

I imagine I will need to use a lookup from form B to view A to get available stock (no problem)

It's how I adjust the document that is looked up.

Does this make sense?

Any help much appreciated.
Lotus IBM

Avatar of undefined
Last Comment
shuboarder

8/22/2022 - Mon
SysExpert

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 !
SysExpert

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

I hope this helps !
Sjef Bosman

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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sjef Bosman

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

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 !
Sjef Bosman

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

Time to sneak out the backdoor again...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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.
shuboarder

ASKER
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
SOLUTION
SysExpert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
shuboarder

ASKER
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....?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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?
shuboarder

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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....
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shuboarder

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sjef Bosman

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...
SysExpert

Glad you found something that works for you !
shuboarder

ASKER
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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23