From a Workflow Action, how can I update a row with values from a SPECIFIC row in another table?

[WillowMobileSys]

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?

[WillowMobileSys]

I did solve my issue by setting a flag, “Process?”, to indicate a record that needs processed to update Inventory counts. The flag was added at the “Purchase Order” level since this is where I indicate when an order is received. In the depths of the Workflow processing on each PO Item, I can still see if it is a record that needs applied by inspecting the “PO ID”.“Process?” flag.

This processing structure is needed in several places in my app and I am certain I am not the only one who has needed this type of processing. Maybe I am severely overlooking a feature to help with this??

If not, and there is no easier way to achieve this kind of calculation, then maybe we need to add another Action feature to help simplify this?

We have “add a new row to another table using values from this row”. Maybe we also need “update a row in another table using values from this row”?

[Aleksi]

Should you structure/update work if only the last PO is used? Or could you built it in a way that when the PO is updated to your inventore table, you mark that row as “Done”?

[WillowMobileSys]

I may have not been clear on the structure. I have a Parent/Child relation be tween PO and PO Item. The Inventory codes are part of the PO Item record. But any particular code could be part of any of several different PO’s.

When a PO is received and updates to Inventory are needed, I need to be able to clearly identify which PO Item records (for the same code) I need to update the Inventory records from. This means I need to “mark” them somehow BEFORE the processing begins.

I tried creating a grouped action on Save to mark the PO Items but the Workflow is triggered before this group completes. Having a step in the Workflow to mark them at the start doesn’t work either - it seems the data updates from previous Workflow steps are not available to subsequent steps?

In the end I marked the Parent PO record with Process? flag at the same time I marked it as Received?. I can later inspect the Process? flag through the Parent/Child relationship when updating the Inventory records and selecting the PO Item records.

This seems it is probably a very common construct needed in many different apps - update a value in another row based on values from the current row. In fact I have seen many questions around this over the past couple years.

If there is a better way to do it…I’m all ears!!

[Aleksi]

So… you want to update the inventory records that belongs to PO as a child records, correct?

[WillowMobileSys]

If I understand your question then yes you are correct. I want to update the Inventory record from the child records of the PO - these are the PO Item records. PO Item’s contain the Product Code and Quantity needed to update Inventory.

[Aleksi]

It sounds doable with actions and without flags. Let me check…

[Aleksi]

Now when I’m thinking this little more, it sounds it’s only doable if you count the total from PO Item records. You can write that value when the PO Item is saved with an Event action. The problem with this method is that if you want to remove the history, the inventory value is then wrong when you next time add a new PO Item. If you can keep all records in the app, then it might work. If you need to use security filter so it would be light to open, you should use sheetformula for that calculation.

[WillowMobileSys]

Yes I think we are on the same page. Please don’t spend any more time on it if you have other more pressing matters. I do have a working solution. Just always looking for a better, simpler approach.

It might have been lost in the thread that what I am doing is tracking the “On Order” items first - this shows what number is expecting to be received. Once the PO is received then I “move” that count from “On Order” to “On Hand” where “On Hand” is the current number physically available in Inventory. This is where I need to identify the specific PO Items because at any given time the “On Order” number could be a sum from several different PO Items received at different times.

In AppSheet, I think using flags is the only way we have of handling this type of processing because we currently do not have the capability to perform data updates operating on 2 specific rows without performing a SELECT() first.

This is why I was suggesting a new Action type like “update a row in another table using values from this row”. The “row in another table” is selected by some join like [_THISROW].[Product Code] = [Product Code]. This would help cut down on the number of Action blocks and eliminate the need for a “selection” flag. I’ll open a feature request for this soon.

In my app I have Products and Materials that I am tracking incoming numbers (Purchase Orders) and outgoing numbers (Work Orders). There are several similar workflows creating a stack of Action blocks. But just imagine when I start tackling Corrections and Cancellations that require adjustments to the Inventory numbers! Fun, fun, fun!!!

[Aleksi]

In a “paper” this could work but there is always a problem if two or more users are doing this at the same time. If you first need to read the existing value and then add some values into it… the last one wins. That’s why it’s better approach to calcluate the inventory value with the virtual column (or with the spreadsheet formula if filtering is needed).

[WillowMobileSys]

Agreed. And I do filter and sum all “active” records for the “On Order” count. But I don’t think this is feasible for the “On Hand” count. Too much history and too many moving parts go into that number.

Wait! Can I change a value in the Inventory record with a Virtual column on a Purchase Order record?

[Aleksi]

Yes you can if you know what record to read.

[WillowMobileSys]

I will know by the Product Code selected. What is the statement used to perform the update? And I presume this will physically occur on the Save?