I have an app that includes minimal management of Inventory. For example, Purchase Orders (PO) can be added to show the quantity of an item that is “On Order”. When the PO is received, a “Received” button is tapped that sets a “Received?” flag and then a Workflow processes the items in that PO to move the counts from “On Order” to “On Hand”.
In order to update the Inventory table based on the items in the PO, I collect the “Product Code” of the PO items and use that list to update the associated Inventory rows. When updating the Inventory row, I select the PO Item rows based on that “Product Code”, SUM the values and update the “On Order” count. This works fine when there is only a single “PO Item” row with that “Product Code”.
The problem is when a product is on multiple PO’s (to be received on different dates). When updating the Inventory row, I need to select the “PO Item” row for the specific PO that was just received. By the time processing has drilled into the Actions structure to where the Inventory row is being updated, the information of which PO was being processed is lost.
One approach I have considered, is adding a flag column that indicates which rows should be selected with the SELECT() statement from “PO Items” when updating the Inventory table values. This means I would need to set the flag at the start of the Workflow, process the marked rows and then clear the flag. This seems overly complicated but maybe the only approach I have available.
In this use case, instead of using a data flag, what is the best approach to be able to select the “PO Item” row from a specific PO? Am I missing some feature that would help in this scenario?
LinkedIn
Twitter
