In which table are you tracking inventory?
In Products, LineItems or a third table?
I'm tracking the inventory in the Products table. It has a "quantity on hand" field and a "quantity on order" field. Once the order gets in, of course, the "quantity on order" will go down and the "quantity on hand" will go up ideally.
Either Products or LineItems can be used to track inventory. Both in fact can be used in the same solution.
When using the line items table, you can see your inventory levels change transaction by transaction. From the Products table, you can just see the totals for Quantity on Hand and Quantity on Order.
How do you currently document which ordered items have been recieved?
Presumably partial shipments may be sent with less than the full number of ordered items included in the shipment. How do you handle such partial shipments?
Once we work out how you do that, we can take a look at a way to construct a relationship that matches records in LineItems to products, but returns the two different totals.
The received shipments are currently documented by changing the shipment status on the Purchase Order to "Shipment Received". However, I had not given thought to partial shipments yet. I will most likely be adding a field to the LineItems table for qty received to keep track of how much has been received and adding a status to the Purchase Orders that says "Partially Received". This seems to me the best way of handling it, but if you have a better way, I'm more than open to it.
With a "Received" field in each line item, you can set up a calcualtion field in line items as: QtyOrdered - Received to produce the quantity on order for that line item. This gives you two fields, "Received" and this calculation, cOnOrder for your two amounts that you need for your inventory counts in Products.
If you have this relationship between LineItems and Products:
Products::ProductID = LineItems::ProductID
Then Sum ( LineItems::Recieved ) will compute the total received for the current product record.
Sum ( LineItems::cOnOrder ) will compute the total on order.
Seems like you'll need one other field for documenting the quantities removed from inventory (Sold, shrinkage, discontinued, dontated, etc.)
YOu can create records in the Line items table where you document these inventory changes as well.
Note: there are several thread here that discus an "inventory Ledger" you may want to examine if you want to see a way to compute these totals from within the line items table instead of products so that you can track how your inventory levels change over time in order to better adjust re-order levels.