How to make loops of rows and repeat them dynamic number of times

[Tamara_Didenko]

The subject of If/Else loops has been brought up a few times in this community. I have asked the question myself. There are various workarounds depending on the desired outcome, and I’m here to offer you one more. I’m not sure it would solve the issue for everyone, but I think quite a few problems on this forum could be addressed using what I’ve come up with here.

---

What is the problem?

I’ve described the setup of my app here. For my app, I need to be able to present a series of steps, with each step being a separate detail screen in the app. But for this discussion, we need to know that for it to work, I need to have a list of steps in a special dynamic sheet with an index column. Kind of like this:

However, as described here, instructions I’m working with sometimes have repetitions (repeat a certain row X times; repeat a group of rows X times); sizes (do X rows for size A and Y rows for size B); as well as forks (do rows 1-10 for option A or rows 2-20 for option B), etc. This makes storing instructions for each step, at times, impractical – it goes against my row quota, and constantly repeating the same information forces me to update information in 20 different places. Instead, I’d prefer only to store unique_instructions and load them in the correct order when necessary. However, since Glide doesn’t have if/then or for looped actions, I couldn’t figure out how to do it until now

---

Joined lists, Repeat text, and a couple of dynamic tables.

I currently use only 3 levels of hierarchy here because of my specific use case, but I think it can be scaled up and down with no issue. At the minimum, you would need 3 tables, but each level of the hierarchy would add 2 more tables to the setup. Important to say that I’m using Glide sheets for this, but I believe most spreadsheet applications supported by Glide should be capable of this, even though the names of functions might be different.

The setup

Usually, I set up those basic columns first, as this information won’t change. This is the data we’ll be manipulating, so for the tutorial’s sake, we assume that all of the basic (yellow) columns are already set up.

The flow

Here’s how the tables work:

• loops – this is more of a service layer, with little to no user interaction (only during the creation of the “product”), but this is where we calculate our loops and how many times they will be repeated. Here’s what’s happening:
  • loop_name is a primary key for the table. These are the IDs of loops we’ll be using to load information from this loop
  • add_comma – Template column that adds a comma to loop_name. The comma can be replaced with any other character that doesn’t normally appear in your primary key. We need it so that later we can split our joined list.
  • loop_repeats – how many times we’ll repeat our loop. In the most basic version, it can be a simple Number column, but it can also have variables (replaced by a template column later) or be set/changed by an action. For now, let’s assume we know how many times our loop needs to be repeated. I have it set up as a number in my prototype for the ease of this tutorial. I’ve also experimented with it being a text and replacing variables with a Template column later. I haven’t tried it yet, but there are probably ways to use the if/else column here, too, somehow.
  • repeat_loops – this is where the magic happens. This is a Repeat Text column that takes the output of add_comma. We needed to add a comma to our initial loop_name because otherwise, this step would mash loop names together, making it impossible to split them later. Unfortunately, we’ll have one too many commas in our string here after this step. And since we’ll be using commas to split it later, this extra comma will create an empty array object, which we don’t need. So we have to trim the extra comma with the next two steps
  • text_length – Text Length column that calculates the length of the repeat_loops text
  • calc_trim – Math column, since we can’t tell Glide to cut X characters at the end, we have to tell Glide the final length of the text we’re looking for. So since we only need to cut one character at the end, we’re taking the text_length and subtracting one.
  • final_loops – Text Slice column that cuts off the last character, aka the extra comma

At this point, each loop “knows” how many times it needs to be repeated, and there’s a list of loop_name items in each row. Something like this

However, to use it further, we need to roll it up into the parts table:

• parts – the top level of the hierarchy, this is my final “product,” this is what the user is trying to achieve.
  • part_name – primary key for parts. We’ll use it to relate to other tables
  • rel2loops – one-to-many Relationship column to loops table. We’ll use it to access the generated list of foreign keys from the loops table
  • compile_total_list_of_loops – Joined List column, which takes values from the final_loops column from each row in the loops table and creates one beautiful list containing the order and the number of repetitions of each loop.

To use it, we need to expand this array into a table. Enter,Miracle Method

• loops_order – a dynamic table that allows us to expand the values of a comma-separated list into a table:
  • index – this is a Number column, and it’s very important for this method. We’ll use it to select items from the array. Remember that the number of rows you create in this table and assign an index to will be the upper limit of how many rows can be loaded into the table. You need to know your use case and make a decision accordingly. Incidentally, no matter how many rows from this table are “filled” with data, they will still count against your total quota.
  • temp_part – Template column that adds part_name to every row of the column. I’m being lazy here and simply putting in part name manually, but it can also be loaded from a user profile.
  • rel2parts – Relation column to parts table based on temp_part.
  • lookup_list_of_loops – Lookup column that brings the beautiful list we put together in the parts table to this table
  • split_loops – Split text column that takes our list and splits it on commas. Now we have an array of loop_names with all the necessary repetitions, listed in the order in which we need to perform it
  • select_loop – Single value column. This is where we take our index value and select items from the split_loops array one by one in the order in which they appear. This is, essentially, the Miracle Method linked above.

Voilà, we have a table that has an order of loops!

It’s dynamic, too, so if I change the number or loop_repeats, this table will be updated automatically.

Now, for many use cases, this is where you could stop. If you don’t need any extra hierarchy levels, this is as far as you need to go. However, my use case requires another level. So, each of my loops could contain multiple steps, and the final table I’m looking for is the list of steps, not loops. To achieve that, I added the following:

• steps – table contains the most granular level of detail; this is where all the details are stored, but no calculations are happening here. We want to load these unique instructions in other parts of the app.
  • step_name – Text column, Primary key
  • link2loops – the name of the loop this step belongs to; we’ll use it for relations
  • instructions – essentially, just my catch-all for anything you might want to store for your final product. In my case, it’s instructions, but it could be product details.
• In loops_order table:
  • rel2steps column – Relation column that uses the values we just got from select_loop to relate to the steps table
  • compile_steps – Joined List column that creates the same kind of list as what we did in final_loops. So here we have lists of steps for each loop, including repetitions
• In the parts table:
  • rel2loops_order – Relation based on part_name back to loops_order table.
  • complile_total_list_of_steps – Joined list column that reads from complile_steps and creates the final complete list of steps.

---

The final result

And a link to prototype.

Caveats

When setting up your _order tables, the highest index number will be the top limit of how many steps or loops you can load into the table. You can usually make a good enough guess from your data/from talking to users, but one could reasonably set up a warning with some rollups.

Thank you for reading!

Let me know if you have any questions or suggestions. Also, let me know what you’re going to build with it!

---

I’ve recently been laid off from my job as a Senior Product Manager at a startup. If you or someone you know is looking for a Product Manager or Product Designer, and you think they can benefit from my problem-solving and documentation abilities, please send me a DM. I would really appreciate it! Thank you in advance =)

[David_Gabler]

I thought I understood your flow until I tried the prototype.

I don’t understand how 1+3+1 = 12. And perhaps if I saw the data I would understand.

Is it that loop_x has two steps “x,xx” and loop_y has three steps “yy,yyyy,yyyyyy”?

Probably also why I never took up crochet or knitting!

[Tamara_Didenko]

Hi @David_Gabler,

Thank you for taking a look and bringing up your questions. I see how this can be confusing =)

I’ve changed the prototype a little bit; let me know if this makes it any clearer. But yes, you guessed it right! Each loop can have any number of steps, which will be repeated in the same order for each loop. So the total number of steps is the Number of loop repeats x * the Number of steps for each loop.

---

I was using my app as an example, but I can see other implications as well. So, for instance, here’s a question that was brought up on this forum before:

@Tobias

My current situation where I needed this was a production list:

Product A consists of 2x Component A & 3x Component B

When planning to manufacture 100 of product A I’d like to automatically generate orders for 200 units Component A and 300 units of Component B

So, to enable this, the loops table will become a products table. The steps table will become a components table and will need to have the same “Repeat text” logic added. But in the end, you can create a UI that will take input of “I want to order 100 of Product A” and will turn it into a list of products.

I think there are other use cases too.

---

PS: hm, I thought there used to be a way to copy apps. Not seeing it now though
PS2: Yeah, looks like the ability to copy the app and sheets is unavailable in the new Glide. Here are screenshots of my tables though

[David_Gabler]

@Tamara_Didenko

the loops table will become a products table. The steps table will become a components table and will need to have the same “Repeat text” logic added. But in the end, you can create a UI that will take input of “I want to order 100 of Product A” and will turn it into a list of products.

This is a perfect use case (fits in with my job as a warehouse programmer quite nicely!)

Everything makes sense when you put it in those terms.

[Tamara_Didenko]

Would love to hear if you were able to use it!

[Pratik_Shah]

Hi @Tamara_Didenko Thank you for detailed instructions.

I am not sure if my use case fits in this scenario. But I would appreciate if you can guide me.

I have an app from where a client submits the form for new product. Client also has to select “States” in which they want to register. E.g. Product A needs to be registered in 50 states of USA.

Upon form submission, the details will go to Products table with a column of States requested with values “State A, State B, State C etc” upto 50 states.

What I am really looking to achieve is, when Admin submits a button, 50 new rows shall be added in another table (because 50 states are selected)
For. e.g.

Product A - State A
Product A - State B
Product A - State C
… upto 50 States.

Can you help me with this?

[Tamara_Didenko]

What is the purpose of the final table? What are you trying to do there?

[Uzo]

Use 50 columns instead of 50 rows

[Tamara_Didenko]

Works only as long as you are constrained to a single country

[Uzo]

that is what he is asking for

[Tamara_Didenko]

He’s giving it as an example. Nowhere does it say that he only needs to collect this for the 50 states of the US.

Also, I’m not data architect, but adding 50 columns this way just doesn’t seem like an elegant solution. It’s also a pain to change later.

[Uzo]

Add comma-separated string… 1 column (but I prefer to have 50 columns… then 50 new rows for each record)

[Tamara_Didenko]

It depends on what you’re trying to do with it, doesn’t it?

[Uzo]

In some rare cases… maybe

[Pratik_Shah]

@Tamara_Didenko

What is the purpose of the final table? What are you trying to do there?

Final table is required because I would like to track status of each state’s submission for that product. Right now what I have done is, splitting comma separated values of States and using single value columns, I am grabbing each State’s value.

If you can see above image, I have created 50 columns with each state using single value column.

My ultimate goal is, if 20 states are selected while submitting product details, 20 new rows with each selected state shall be added in another table (which is required to track the submission status and for many other things) and that’s why I need it as separate row.

While looking at your this post, I thought my use case can be fitted in this. Do you think an easy way to achieving this, probably using your method?

Since

[Pratik_Shah]

@Uzo

Add comma-separated string… 1 column (but I prefer to have 50 columns… then 50 new rows for each record)

Yes this is exactly how I have done. But its pain to configure Actions for multiple rows based on conditions. Its like, if 2 states selected, 2 rows to be added. If 3, then 3 rows and so on and so forth.

These goes on for 50 conditions which does not look a good way to handle it. Hence I thought if @Tamara_Didenko’s method can be used in my use case.

Do you have any other solution? @Darren_Murphy suggested me to use Glide API using Make. I am yet to finalise it but if I can do it in Glide instead of Make, that would be super fast and great. Right now, I have configured Action upto 22 States. It adds 22 rows super fast. That’s why I am trying to find another way to do this in Glide.

[Darren_Murphy]

@Pratik_Shah

Do you have any other solution? @Darren_Murphy suggested me to use Glide API using Make. I am yet to finalise it but if I can do it in Glide instead of Make, that would be super fast and great. Right now, I have configured Action upto 22 States. It adds 22 rows super fast. That’s why I am trying to find another way to do this in Glide.

You won’t find another way to do this in Glide unless/until Glide give us the ability to create loops in actions. There are some more advanced features coming with actions later this year - maybe this will be one of them (I don’t know).

[Tamara_Didenko]

I’m accessing from my phone right now so can’t try it… but if I’m following your explanation, I think you could use parts of this method to create a “dynamic” table with values you’re looking for… You mostly need the Miracle method here + some roll ups/lookups.

However, the only problem I see here is that my method is not really adding any rows, rather, it’s writing data into an existing table that already has indexes. For the use cases where the final number of items is known (or at least estimatable), it’s not an issue, but I think it might be tricky in your use case…

So, the table you’re looking for is something like

Product | State | Submission status

But it would have multiple products for multiple states? Can any of these ever be deleted or do you need to keep the history forever?

[Pratik_Shah]

@Darren_Murphy

You won’t find another way to do this in Glide unless/until Glide give us the ability to create loops in actions. There are some more advanced features coming with actions later this year - maybe this will be one of them (I don’t know).

I really wish if they can come up with “Loop” / “Repeat” action function.

[Pratik_Shah]

@Tamara_Didenko

But it would have multiple products for multiple states? Can any of these ever be deleted or do you need to keep the history forever?

Yes, it will be for multiple products with multiple states.
E.g. Product A with 20 states selected, then 20 rows for Product A. Product B with 50 states selected, then 50 additional rows for Product B.

As of now, we are not going to delete anything. But yes probably we may think with that option once submissions are closed.