Statement that retrieves in-stock data for customer POs

[couevans]

Hello,

I was hoping if someone could help me create a statement for a set of data I have?

Basically, I am trying to create a statement that can confirm purchase orders for a customer based off my current on-hand inventory. There are several purchase orders placed by my customer, but they all include duplicates of the same item and I want to be sure that I can be true when fulfilling their desired quantities.

I know that I can run a VLOOKUP to find my available stock, but I don't want to run into over-confirming unit's quantities. For instance, let's say I have a customer that places 3 POs for item "A". The first PO ordered 1 unit of item "A", second PO ordered 5 units of item "A" and third PO ordered 10 units of item "A". I only have 11 units of item "A" in stock. I need a statement that can get me to confirm the third PO in full at 10 units and the first PO at 1 unit - leaving the second PO at 0 qty accepted. I want to be sure that I can fulfill the most out of a PO.

Any help is greatly appreciated.

[dflak]

The best way to run an inventory system is to base it on transactions. You have an initial transaction to add the starting stock then transactions for receiving, distribution, and Inventory adjustment. The balance is the sum of the transactions. The actual value on hand is never stored, it is always calculated.

In the attached you may be able to use the manufactured item as a purchase order. PO 123 requires 130 Units of A, 200 Units of B ...