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.
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
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.
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.
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_nameis 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_namebecause 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
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_lengthand 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– 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
loopstable. We’ll use it to access the generated list of foreign keys from the
compile_total_list_of_loops– Joined List column, which takes values from the
final_loopscolumn from each row in the
loopstable 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_nameto 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
partstable based on
lookup_list_of_loops– Lookup column that brings the beautiful list we put together in the
partstable to this table
split_loops– Split text column that takes our list and splits it on commas. Now we have an array of
loop_nameswith 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
indexvalue and select items from the
split_loopsarray 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.
rel2stepscolumn – Relation column that uses the values we just got from
select_loopto relate to the
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
rel2loops_order– Relation based on
complile_total_list_of_steps– Joined list column that reads from
complile_stepsand creates the final complete list of steps.
And a link to prototype.
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.
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 =)