LinkedIn
Twitter
I built this functionality today for a client, it seemed very share-worthy, especially since I wasn’t quite sure it would work until after I built it.
There have been many posts about how to setup looping actions and how to automatically generate a number of new records. If you haven’t read those threads, you should start with those before reading this one. I may or may not come back to edit this post to link some of those other threads. Well here is one at least, since the community is trying to tell me my “topic is similar to…” :
This is an even more advanced setup, where there is a loop within a loop.
The functionality is built to allow employees to request certain shifts across certain dates. We wanted the app users to be able to quickly select multiple shifts and multiple dates, so I setup two EnumLists for them to select multiple from, inside a single record.
Column structure for **shift_request_input** Table
HOWEVER
For the schedulers to do their job, and be assisted by some app automation, it is much easier/better to see individual records linking each single shift to each single date. So what I wanted to do here is to create all combinations of shifts versus dates from these two EnumLists.
As you can hopefully already tell, we can’t just loop across one EnumList and create the records, we need to have an inner loop to loop across the 2nd EnumList within the loop for the first EnumList.
The general overview strategy here is:
1. Outer loop
A. Re-calculate COUNT for inner loop
B. Execute inner loop
i.Inner loop
a. Create a new record
b. Decrement inner loop count
c. Re-loop inner loop (IF count > 0 )
C. Decrement outer loop count
D. Re-loop outer loop (IF count > 0 )
And here is a general overview screenshot of the set of actions:
*Extra bonus tip here too, you get to see the naming schema that I use when creating large grouped action sets.*
Action Set Overview.
And here are the individual Action Definitions.
GROUP 7 | outer group , loop over dates
GROUP 7-1 | reset shift_count
GROUP 7-2 | execute inner group
GROUP 7-2-1 | inner group , loop over shifts
GROUP 7-2-1-1 | create new shift_request
You can see 90% of the expressions there, but they are simply:
INDEX( [enumlist] , [enumlist_count] )
GROUP 7-2-1-2 | decrement shift_count
GROUP 7-2-1-3 | re-loop inner group
GROUP 7-3 | decrement date_count
GROUP 7-4 | re-loop outer group
This entire Action group is set as the Form Saved Behavior for the shift_request_input Form view. I might move it to be executed via Bot so that the users don’t have to potentially sync dozens of records on their end, but I haven’t gotten that far yet.
* [employee_month] is just a Ref column to a parent record. *
This IS absolutely share-worthy
Nice one!
Only hiccup I see is this:
-
This unfortunately doesn’t work.
- Bots can’t run through loops - at the moment at least.
There used to be a loop depth limit listed in the limitations documentation, but it’s not listed anymore (probly since bot-loops don’t work at the moment).
There are 2 different types of users here: “requesters” and “schedulers”
This is the preferred/easiest way for the requesters to enter their shift requests. Here it is just a single record, but it indicates 4 shift requests.
The purpose of the nested loop is to split this single record into 4 separate records, the 4 possible combinations (2 x 2), which is the preferred form for the records to be in to be used by the schedulers. These 4 records would look like this:
A single loop could only handle the case where you’d have one List type column. You need another nested loop for every additional List type column that you want this sort of functionality to act on.
That`s correct, this would be a one time thing for every supplier. I looked over the sheets formula but found it easier and faster with python. Its actually funny considering that for a 3 row deep cartesian join i had to create 15 individual actions and nest them accordingly with Python I was actually able to pull it off with 3 lines of code 
list1 = [a+str(",")+str(b)+str(",")+str(c)+str(",")+str(d)+str(",")+str(e)
for a in SKUSTD for b in BodyMaterials for c in FeetType for d in FeetColour for e in Cover2]list2 = [a+str(",")+str(b)+str(",")+str(c)+str(",")+str(d)+str(",")+str(e)+str(",")+str(f)
for a in SKUSCT for b in BodyMaterials for c in FeetType for d in FeetColour for e in Cover2 for f in Cover3]list3 = list1 + list2
Turns out that COUNT(list3) = 8443080 and that`s for only one product and i have 20 more !
Thank you for your input, this is actually a great ideea and i think its the only way i can go about doing this. Haven`t given it too much thought but how would you keep track of current stock variation options ?
I can't find the step for counting the "date_count".
At GROUP 7-1 | reset shift_count, we count the shift_count. Where do we count the "date_count"? I think that we need the initial number of the "date_count".
Thank you.
