I have a "Main" Table where I have a [Item_Quantity] number column. I want for each row added to that table, that X number of rows to be created on another “Items” Table… this can be large quantity (50-100 rows, sometimes more)
So I did this :
In “Main” Table, I created a [_Item creation counter] column, which have [Item_Quantity] as Initial value…
I created a bot in “Main” Table ADD or Update event, with condition [_THISROW].[_Item creation counter] > 0. that run a Grouped Action : “Add Item Loop”, with 3 actions as follow :
Add a new row to “Items” Table
Decrement the [_Item creation counter] by 1, with the action "Set these column : [_Item creation counter] = [_THISROW].[_Item creation counter] - 1
Retrigger the loop with a “Execute action on set of Row” type action, on "Main" Table, with “referenced rows” to LIST([_THISROW].[KEY] and the grouped action “Add Item Loop”
All these 3 grouped actions (along with the bot) have a" Only if this condition is true" of [_THISROW].[_Item creation counter] > 0
The Workflow seems to work as expected. I can see some rows created in “Items” table, and I can see the [_Item creation counter] decrementing…
But sometimes the loop seems to stop…
I tried with 10 as Quantity > its okay, 10 rows created and counter to 0…
tried with 20, Counter stopped at 7
tried 10 again, OK counter to 0
tried 50, Counter stopped at 26
tried 50 again, Counter stopped at 38
tried 10, Counter stopped at 8
tried 15 , OK counter to 0
Each time I tried, I absolutely left the app and databases untouched. Doing nothing. just letting run the automation and watching the “decrement”.
I am using “Smartsheet” as data tables. Could there be some “Delay” issues with conection beetween the APIs, that somehow Stop the loop ?
Is my workflow OK to your expert eyes ? Can this be achieved via other simpler ways ?
Whenever I’ve got looping for large amounts of things, I find it’s typically better to switch to a parallel processing strategy instead.
Right now you’ve got a loop for each item
Switch to executing an automation for each item
This way everything happens in parallel on the server, which will be much faster.
In order to do something like this, it usually requires a reworking of the database to accommodate this type of functioning; you can’t do something if you don’t have a thing to do with with (in AppSheet). Sometime it means you need to build out another table, with records that represent each of the “things” that need to be iterated over - or repurposing a table that already exists (like the Items table) and using a <<START: >> block with an internal API call to iterate through the records you need,.
For your situation…
I actually think you might be better serves switching from a manual “current stock” method, to a virtual one.
Right now you’ve got a physical column that holds the current quantity of something
Which means you’ve got to update that number for every change that happens - physically
If you switch to using a virtual quantity column, everything is dynamic and updates automatically as new data comes into the system.
No update mechanism to keep straight
No missed updates
No duplicates
It just works
The key to making this work is the following:
You’ve got a column on the “Items” table that holds the [Item_Starting_Qty]
The idea being that we have a starting number, and add/subtract from that
Then you’ve got all adjustment records ref connected to the Items table
This gives use a [Related Adjustments] reverse reference on the Items table, through which we can get all the adjustment numbers
When someone adds something to stock, we make a record with a positive number
When someone takes something out of stock, we make a record with a negative number
Then create an additional virtual column (on the Items table) that holds the [Item_Current_Qty]
This takes the starting quantity (from the item record) and adds all the numbers of adjustments to it… giving us the current qty.
Every now and then go through and archive the adjustment records (moving them to a long-term storage table) while updating the [Item_Starting_Qty] to whatever the number needs to be now.
By building things out this way the QTY is always updated, no matter what; as long as the adjustment records are there, the Item will have the correct up-to-date qty.
This also has the effect of removing all these update mechanisms that you otherwise need to build out.