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.
LVL 21
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 !
Sjef BosmanGroupware ConsultantCommented:
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...
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Sjef BosmanGroupware ConsultantCommented:
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 !
Sjef BosmanGroupware ConsultantCommented:
OOOOOOOH dear... When will I start to read questions and not interpret them...

Time to sneak out the backdoor again...
shuboarderAuthor Commented:
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.

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


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:


(unless someone has since adjusted stock levels of course)

You need a status field in your Form A for stock, or a quantity field.

If you are using a Status field then set it to "Used" or "available" .

This will work if you have 1 Docuemnt per item in stock.

If you have a single document that has just a quantity available ( this does not sound like how you are doing it ), then wait until  the Quant is 0.

S view A would show either status="avalable"  , or Quant > 0,

and View B would have  status="used" , or Quant=0

We need more info on how you are implementing your stock form.

I hope this helps !
shuboarderAuthor Commented:
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....?
Ok, this is better.

Now a few more qustions.

1) in your Archive view, will it be sufficient to show wat items have benn reserver/requested, by Item,  or will you also need to know aditional info like dept, or Project #, that may b available in Form B ?

In any case I wold add a Field for "reserved Amount " o requested Amt. r similar on Form A.

For the Simple case, you could simply have the Archive view show all non-blank values of this field.
The update will be handled by the QUerySave or Submit button  on form B.

I'll work on the code.

Here is a sample from my own library
make it a function

Function  Get_NAB_DOC_by_E(  Srvnm As String , NDoc As NotesDocument, key1 As String,  DBnam As String, NType As Integer ) As Integer
Dim sg As NotesSession

Dim n As Long, Tint As Integer, TNM As Integer, INM As Integer
      Dim col As NotesDocumentCollection
      Dim Nbview As NotesView,  ndb As NotesDatabase  
      Dim Message As String, Inet1 As String
      Dim Erl_st  As String,             ERNum_st  As String
      Set nDb =  Sg .GetDatabase(Srvnm,DBnam ) ' Get DB handle via name and Server name
      If nDb Is Nothing Then
            Message =  "ERROR -- Could Not open DB on  ;" &  Srvnm & " DBname=" & DBnam
            Erl_st ="0"
            ERNum_st = "990"
      '      Call CreateLogEntry1(Message, Erl_st, ERNum_st )
            Exit Function  ' V 2.0D 11/23
      End If
      If Trim(  Inetname ) ="" Then
            Message = Ucase("Fatal ERR - Blank Find data ;") & key1 & "; "  &  Srvnm
            Erl_st ="102"
            ERNum_st = "972"
      '      Call CreateLogEntry1(Message, Erl_st, ERNum_st )
            Exit  Function  ' 3
      End If

        Set nbview = ndb.GetView ("Byyourview" ) ' replace with view name for sorted key view
       Set ndoc = view.GetDocumentByKey (key1 )
      If Ndoc Is Nothing Then
                  Message = "ERROR- Could Not Get  Doc ;" & key1 & "; "  &  Srvnm & "; " 
                  Erl_st ="0"
                  ERNum_st = "911"
                  Call CreateLogEntry1(Message, Erl_st, ERNum_st )
            Else ' found 1 - check that it is real and accurate.
     ' process or return with exit code
        end if

exit function

shuboarderAuthor Commented:

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?

In form B, on your submit or querySave, go through all th fields ( table ? ) that contain Stock items that are requested.

Use the function ( change as needed ) to grab the actual stock form, and to change the fileds in form A.
Save it, update the status in Form B, and save it also.

Form A is neve going to be visible since you are acessing it via bacKend functions only.

so using ndoc ( Form A doc ), just update the filesd.

ndoc.qty_in_stock= ndoc.qty_in_stock(0)- doc.Qnt_requested(0)

doc.Qnt_requested(0) is on Form B and should come after the  new parts_required.

SO you will have a parts required, and a quantity.Your new qty_in_stock is the old qty_in_stock - Qnt_requested ( from form B )

You can also update a Qty_used field on Form A if you want to keep track of that on the Parts level, rather than on the requests level ( form A vs. Form B ).

I hope this helps !

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
I try to follow this topic but I must say I can't quite follow it any more. In response to some earlier post:
> I do know how to do ERDs yes, and I have as always drawn out
> exactly the processes that need to take place.
An ERD has got nothing to do with processes...
You could have made a logical ERD, that you could have translated into a physical ERD.

Something else that (I hope) may help you in your quest for the best: if you have a high throughput of your articles, and there are many people who can update stock, then beware for lots of replication/save conflicts.

I'd use a multi-level approach: 1) a stock document, 2) a stock adjustment document, created when items are added or removed, 3) a categorized view that shows totals per article, using a combination of the stock document and the stock adjustment documents, and 4) a background agent that condenses every now and then the (older) stock adjustment documents into the stock documents, in order to keep the view small enough.
shuboarderAuthor Commented:
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?
shuboarderAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
> ... not too complex ...
I agree, although one should start designing the "perfect and ideal" system, and then allow imperfections. I've often seen that systems that were based on incomplete or "tolerant" specifications go down the drain when the organisation expanded and tighter rules were imposed.

It's not the replication conflicts I was worried about, but more the save conflicts. When two people update the same documents at the same time, you're likely to get some conflicts. In your case, with 5 people working on it, you won't see them very often, maybe even not at all. But don't expand your department to 50 people...

The complexity of my idea isn't so very high. I think that the elements I mentioned are simple enough to be implemented. In companies that want to trace the equipment they sell or deliver, using serial numbers, the agent isn't necessary: equipment should be moved from the stock-view to a customer. Just ideas...

All you will ned to do is have a view that uses the part name ( must be unique ) rather than the part number. In can be hidden.

Since apparently you do not have a quantity field, all you need to do is loop through the list, grab the Stock Form ( A) via the subroutine and view by part name,  , and subtract 1 from the Quant, and add 1 to the quantity_used fields, save the doc, and also put an updated flag in Form B, when it is finished and save it.

As sjef_bosman mentioned, this will work fine for small groups of people.

If not, you might have to consider a transaction system or Document locking.

shuboarderAuthor Commented:
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

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

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

This is OK if you are prompting for each quantity.

I thought  that the quantity was only 1, and that if they want 3 bolts, they added bolts,  3  times into your dialog listbox.

It is a shame that you are not doing this in a table where each line is a sngle part, and quantity.

The way it is now, it appears that you are not saving the requested quantities anywhere.

I hope this helps !
shuboarderAuthor Commented:

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


This may be more like the kind of thing Sjef was suggesting?
Sjef BosmanGroupware ConsultantCommented:
What do you mean? Me nagging about the analysis and design, or my 4-part approach of the inventory? I still can't help thinking that you're going too deep into implementation right now.

Here's my train of thoughts. The concept is leading: you need an inventory and the necessary operations on it (please define). Then you take the closest thing to an inventory in Notes, which is to me a view. Intuitively you try to match the required operations on the inventory/view, and you're convinced that it can work. Critical is parallel updates, so maybe special provisions have to be made for that: don't update but only add records. In that way, the view is only updated, but no documents are changed. Document changes can be postponed, hence the agent.

Since it's a totally different way and you were making such fine progress already, I decided not to interfere with my alternative. But now, since you're calling me again... ;-))
shuboarderAuthor Commented:

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!
Sjef BosmanGroupware ConsultantCommented:
Me? Sarcasm?? Naaahh.... ;-)

Your option 1: no, an agent isn't going to do the job, you correctly mentioned the reason why: the delay between the action and the activation of the agent. You'd have to update all documents that require an update immediately, before anyone else uses them. It should be close to a transaction in any regular RDBMS.
shuboarderAuthor Commented:
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

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

Yes, but you need a flag in Form B, saying that you have already updated your inventory in Form A, so that it does not do it again.

You also need to prevent editing of Form B, once the flag has been set.

I think that this should be fine for small amounts of people.

Later you can consider a locking option if it comes to the point where multiple people are vying for the same parts simultaneously.

I hope this helps !

shuboarderAuthor Commented:
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....
ANother option is to keep a history of changed fields in Form B, so if they ned to make changes, yo know the former values and can update accordingly.

You may need to think through your entire strategy, and what other requests will pop up in the future.

I hope this helps !
shuboarderAuthor Commented:
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)


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


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


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.
Sjef BosmanGroupware ConsultantCommented:
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 !
shuboarderAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.