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?
LinkedIn
Twitter
