Have a question regarding the correct Airtable implementation for a unique situation I am currently facing. I want to have a Subcategory column in my table that is of type multiple select and then have a following column that directs all the possible combinations of Subcategories into 3 different groups (1, 2, both) and a simple conditional statement can determine this. Unfortunately, I cannot use a for loop in the Formula builder correctly so the function does not work correctly. Now I am wondering if I should implement a script to create the desired column or is this overkill and is there a simpler solution? Apologies if my explanation is not clear and can dive into more detail upon request. Thanks!
+13I would generally aim to use a formula for this, though depending on how complicated the logic is, that may become very frustrating to maintain over time. Using good formatting/indentation in your formula can help with that.
Let’s suppose you have the following possible values in your multiselect:
“Apple”
“Banana”
“Caramel”
That means there are 8 possible combinations:
- A
- B
- C
- AB
- AC
- BC
- ABC
- Nothing
We could build a formula like this:
IF(FIND("Apple",{Subcategory}),
IF(FIND("Banana",{Subcategory}),
IF(FIND("Caramel",{Subcategory}),
"Caramel Apple Banana Split",
"Banana Apple Twist"
),
IF(FIND("Caramel",{Subcategory}),
"Caramel Apple",
"Plain Old Apple"
)
),
IF(FIND("Banana",{Subcategory}),
IF(FIND("Caramel",{Subcategory}),
"Banana with Caramel",
"Banana Only"
),
IF(FIND("Caramel",{Subcategory}),
"Caramel Only",
"NOTHING AT ALL"
)
)
)
Wow, that’s a lot of Nested If functions!
The benefit to using a formula is that the table will automatically recalculate the formula field values in more or less real-time as the Subcategory field is edited for a given record.
That said, a Script can certainly do this to and you can even trigger your script to run any time a record is updated. BUT, scripts tend to execute after a bit of a delay so it won’t feel as snappy.
+5I would generally aim to use a formula for this, though depending on how complicated the logic is, that may become very frustrating to maintain over time. Using good formatting/indentation in your formula can help with that.
Let’s suppose you have the following possible values in your multiselect:
“Apple”
“Banana”
“Caramel”
That means there are 8 possible combinations:
- A
- B
- C
- AB
- AC
- BC
- ABC
- Nothing
We could build a formula like this:
IF(FIND("Apple",{Subcategory}),
IF(FIND("Banana",{Subcategory}),
IF(FIND("Caramel",{Subcategory}),
"Caramel Apple Banana Split",
"Banana Apple Twist"
),
IF(FIND("Caramel",{Subcategory}),
"Caramel Apple",
"Plain Old Apple"
)
),
IF(FIND("Banana",{Subcategory}),
IF(FIND("Caramel",{Subcategory}),
"Banana with Caramel",
"Banana Only"
),
IF(FIND("Caramel",{Subcategory}),
"Caramel Only",
"NOTHING AT ALL"
)
)
)
Wow, that’s a lot of Nested If functions!
The benefit to using a formula is that the table will automatically recalculate the formula field values in more or less real-time as the Subcategory field is edited for a given record.
That said, a Script can certainly do this to and you can even trigger your script to run any time a record is updated. BUT, scripts tend to execute after a bit of a delay so it won’t feel as snappy.
Appreciate the timely response Nathaniel!
Unfortunately my case involves around 30 possible inputs for the Subcategory and the 30 are split into 3 possible categories that need to be sorted through a conditional. I don’t think this is possible in this nested format as the formula would be extremely long. Seems like a loop (for or while) is the only viable solution.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.