Inventory, Lineitems, and Purchase Orders
I have a database that's set up to track inventory and ordering. It's separated into some tables as follows:
- Purchase Orders
So I can create a purchase order, and in this table there is a lineitem portal where I can put several lineitems on the purchase order to place orders for more than one product from a company all at once. What I need (and have tried, but can't seem to get it right) is for the status of the purchase order to affect the inventory. So if the status is "Sent" then the inventory shows that the qty ordered is "on order". Then when the status is changed to "Shipment Received" what was ordered is then subtracted from "on order" and then added to "on hand".
The Problem - Everything I've tried doesn't add up right. It either tries to add the quantities of all the lineitems on the purchase order up and then add that number to the inventory of the first item listed only, or it only takes into account the first item listed. It won't go through the individual lineitems separately.
Does anyone have any suggestions on how to create a calculation, summary, script, function, whatever that will accurately track the shipping and receiving of products? Or maybe a different way to set up the tables and relate them?