Iterating a list with a formula

Is there a way to iterate through a list with a formula?
I want to change a property of a cell based on the RG index number, but I only can go up to 20 items. When I get to 21, it should start over at item #1.

Similarly, RG index 37 should return item #17
and RG index 105 should return item #5

You’d have to create a backend workflow

You could use a backend workflow to do this. If the list is not too long (below 50), you could go with the “Schedule API Workflow on a List”. Send the List into the workflow and create a “Make changes to thing”. Once the workflow is triggered, you will automatically iterate through the entire list.

I’m essentially trying to create a formula to assign a random colour to and I have 20 colours to iterate through.
I think a backend workflow would be a little overkill to change a list of things every time the repeating group is loaded. For the worst case scenario, I could assign an iteration number to each thing as they’re created, but this does run the risk of having the same colours side by side by chance.

If I was able to apply excel formula to the expression, it’d look like the following:

A=Total no. of items in the repeating group.
B=Total no. of Option Sets that will be iterated.


I got this to work by using the modulo expression.
I decided to run it from a database trigger but it could easily run within a repeating group.

current cells index < - modulo - > OS_Colours:count

The only caveat is that when you reach the option set count, you need to replace that value with a condition. i.e. if you have 20 option set values, when the RG index reaches 20, it’ll return 0, so you’ll need a condition to state that item’s value should = OS_Colours:count.

This topic was automatically closed after 70 days. New replies are no longer allowed.