Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Script or Function?

64 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

2 Replies 2

I 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.