So my thinking now is that the Entry and Deplete tables arent needed. Just have and Entry and Delete Layout. Then based on what layout the user is using, populate a status field in Inventory Lines and use that for calculations and summaries....
You might consider a "ledger" approach for logging reagents into inventory and then also logging their depletion. Think of receiving reagent shipments as "deposits" and logging consumption as "withdrawels".
You might have these fields to track each inventory change:
kf_ItemID Date Description Qty Received Qty Depleted
cBalance: Qty Received - Qty Depleted
sBalance: Summary, Running Total of cBal; restart totals, group by ItemID
With this approach you make a new entry each time you receive or use a reagent and the summary field, sBalance will compute the remaining quantity for you. Put this in a list view layout sorted by ItemID and you can show how much reagent you have on hand and also see how it is being used.
kf_ItemID should link to a separate table where you record each lot of reagent and a primary key serial number links it to kf_ItemID in this field.
Thank you for your reply. I am chewing on your answer.
The ledger idea makes sense. I am using one table now called INVENTORY_Lines to mark items entered or received, although altered in method of entry as the field Received or depleted is calculated depending on what layout they are using as to avoid them entering reagent, when they should be removing it, etc.
What I am now working on is a way to get totals. I will get summaries based on Reagent Name, as well as sub totals of each lot. This should be pretty straight forward.
Also some kind of warning of when they remove a lot #, and there is something still in stock with a closely approaching expiration date compared to what they are trying to use....
To get summaries based on reagent name, you can have running total summaries and use the group by option to specify the reagent name. You can then sort your entries first by reagent name and then by Lot# to group your layout changes.
THank you for your help. I was able to make the entry work how I needed it to. Works great when I sort the records on my list view to have all the lot#s grouped together.
Followup Question: How do I get the list view layout to automatically sort each time. Is this something I would have to script along with a onlayout enter trigger?
That's probably the simplest to way to set it up.