Is there a better way to update values in other tables?

This question has been bothering me for some time now. I’ve needed similar functionality on numerous occasions and I have seen related questions posted by others. I know it somewhat boils down to design and requirements, but I need to know if there is a better way to updates values in “that” table based on changes in “this” table? What I have come up with seems cumbersome and overly complicated and I just want to see if someone has a better way.

Background: I have a service order app that records installed products and materials used - both are managed in Inventory. As orders are entered, I want to track Allocated Quantities to prevent “usage” in future entered service orders. When the service is completed, the Allocated Quantities are removed from Quantity on Hand. I need to also manage Allocated Quantities when orders change or are cancelled. All normal stuff…right?

Inventory, Service Orders, Installed Products and Used Materials are separate tables with Installed Products and Used Materials children of Service Orders.

Here is my processing design: When changes are made to quantities for Installed Products or Used Materials, an associated Wokflow is triggered. That Workflow sends control to a related set of actions. The actions redirects processing to the associated Inventory row. This means the action no longer “knows” for which Product or Material row updates are needed. To compensate, I have added a Processed? flag column used to identify if an Installed Products or Use Materials row needs processed - updates in Inventory. This means I have to manage “flipping the switch” on or off at the proper times. The main idea is that the Processed flag is set to “N” when a row is entered or updated. When the Inventory action is activated, it searched for Product or Material rows for that Inventory item that are not yet processed. Adjustments are made to the proper Inventory columns and then the Processed? flag is set to “Y”.

This all works but I struggle with getting the logic exactly right for all of the different levels of action processing going on. When you consider the need to identify changes to the quantities or outright cancellations, the myriad of actions needed to manage all of this work grows considerably. Al of this makes implementing the logic very tedious each time I need to do so.

Am I missing something? Is there a better way of managing these kind of updates in the AppSheet system?

1 Like

I don’t believe so.

@Steve Ok, not the answer I was hoping for but good to know I am not totally off the deep end? :slight_smile:

I think this processing can be simplified with a new “Do This” property in an action such as: “Data: set the value of a column in a different row”. It would have access to the current row as well as the “different row” to make the necessary changes.

From my experience and others questions, it seems this function is fairly common and would have enough traction to justify the implementation.

I’ll search the Features list and if not there get it added. Fingers crossed!!

You can use the Action “execute an action on a set of rows” to make changes to one or more rows.

Yes, and I use that to “redirect” processing from my Installed Products row to the associated Inventory row. However, these two rows are not related and I have no way to physically get back to the Installed Products row that initiated the Workflow. That means I do not have access to the Installed Quantity value that is needed to update the Inventory row without building in something to remember it.

I have the same problem. Could you show me how you solved it?

Have a look at this post. I have mentioned the sample app in last comment in the same post for your reference.

1 Like