Update inventory based on the purchases made

[Nirmal_Giri]

I have gone through most Q&A related to Inventory stock management, and in most of the post the use case is different from my current scenario. My issue is somewhat similar to the one raised by @WillowMobileSys. Since there is no resolution so far, hence this post. 

Description:
I have three tables: Inventory,  Purchase_order (parent)  & Purchase_order_details (child). 
In-stock: current qty in the inventory
Qty to reorder: total expected qty to be purchased from the supplier
Qty ordered: qty purchased at the moment
Table: Inventory

Name	In-stock	Qty to reorder	Qty ordered
A	0	12	0
B	0	3	0

Purchase_order contains list of all purchase orders placed till date with respective supplier and is the the parent table. Each purchase order has multiple items for which I am placing order. The quantity for each item may be different.

Table: Purchase_order

Name	po_id	Order value
Order 1	id1	1000
Order 2	id2	500

Table: Purchase_order_details

Order_name	po_id	Item	qty
Order 1	id1	A	8
Order 1	id1	B	3
Order 2	id2	C	1

Once a purchase order is created, a mail is sent to the supplier with the purchase order details (items, qty, terms etc). At the same time, I need the Inventory to update columns 'qty to reorder' & 'qty ordered' based on the items added recently in the purchase_order_details for that purchase order. Essentially at the end of this transaction, my inventory should look like this:

Table: Inventory (after order is placed)

Name	In-stock	Qty to reorder	Qty ordered
A	0	4	8
B	0	0	3

I tried 'excecute an action on set of rows'  from Purchase orders to Inventory. However, when the control is passed to Inventory table, there is no means get the item qty in the Purchase_order_detail. 
I also tried to set value in the Inventory table, but expression is too complicated with multiple LOOKUP() involved and it takes a noticeable time to update & sync.

Am I missing anything here? Is there any way to pass the value from 'Purchase_order_details' to 'Inventory' to update my qty for respective items. 

[TeeSee1 · Accepted solution]

Assuming this is what you want,,

Here is what I did

Create an action on inventory: Use INPUT() function to receive ordered qty from the next action

Create an action on PODetail (POD): Pass the ordered qty via "With these inputs"

Create an action on PO to fire the action above on the [related PODs] and set this on the saved event of the PO form.

[Nirmal_Giri]

@TeeSee1 This is exactly what I want. It took me sometime to understand the functionality of INPUT() and how you applied it. I implemented your solution and it works!! Thank you. 

I made a slight change to the expression for the action on Inventory:
[Qty Ordered] = [Qty Ordered] + INPUT("ReorederedQty",0)
[Qty to reorder] = [Qty to reorder] - INPUT("ReorederedQty",0)

The earlier expression did not consider the existing values and would overwrite them. The modified one add/subtracts only the qty has been ordered. I repeated the same action using INPUT to update my qty-in-stock when the material is received. Thank you once again.