IF Statement Needed

[John_Dlouhy]

Hello Airtable Community!

My table has a single select field with 3 options. The field is called SVC Broad Category:

There is another single select field with 20 options called SVC Area:

When we add tasks to the table, we assign them to a SVC Area and to a SVC Broad Category.

I am hoping for assistance with an IF Statement for SVC Broad Category, so that whatever is selected in SVC Area (e.g. Alcohol) is automatically assigned to the right SVC Broad Category (e.g. Ops/Logistics). The image below shows the Areas and Categories.

As always, any assistance is greatly appreciated.

[Jeremy_Oglesby]

In order for that to work with an IF() statement formula, {SVC Broad Category} will need to be a Formula field, not a Single Select field – which means it won’t have the pretty, color-coded pill design, only text. Is that an acceptable trade-off for your base design?

Alternatively, if you are using a “Pro” or “Enterprise” subscription, this could be handled with a Scripting block, and could auto fill the {SVC Broad Category} as a Single Select field for all records where it is blank, based on the value of {SVC Area} with the click of a button. Would you rather pursue that approach?

[John_Dlouhy]

Thank you Jeremy! I am fine losing the pretty color-coded pill design—text only works for me. I’m assuming we would still be able to filter by the text?

[Jeremy_Oglesby · Accepted solution]

Sure, @John_Dlouhy.

I’ll give you the basic structure of the formula you’ll want in your {SVC Broad Category} field, and let you fill out the rest:

IF(
   OR(
      {SVC Area} = "Alcohol",
      {SVC Area} = "AV",
      ...
   ),
   "📈 Ops/Logistics",
   IF(
      OR(
         {SVC Area} = "Invitation List",
         ...
      ),
      "💰 Metrics/Finance",
      IF(
         OR(
            {SVC Area} = "Event Collateral"
            ...
         ),
         "📱 Marketing/Communications"
      )
   )
)

You need a new, nested IF() statement for each new {SVC Broad Category} you need to accommodate, and you need to add each {SVC Area} option associated with the broad category into the OR() statement associated with that category.

If the nested IF()'s have your head spinning, there’s a simpler, but more verbose/repetitive solution – the SWITCH() function. That solution would look like this:

SWITCH(
   {SVC Area},
   "Alcohol", "📈 Ops/Logistics",
   "AV", "📈 Ops/Logistics",
   "Invitation List", "💰 Metrics/Finance",
   "Event Collateral",  "📱 Marketing/Communications",
   ...
)

In this solution, you’ll have to repeat the text output for each option you want to accommodate in {SVC Area}, but it’s visually simple and straight-forward. You just continue that list by adding the value from {SVC Area} followed by a comma and the value you want to output in association with that {SVC Area}. To add a new line to the SWITCH() function, you need to ensure there is a comma after the output as well – BUT make sure that the very last combination of options (ie, the very last line in the SWITCH() function) DOES NOT have a comma after the output – only the closing parenthesis.

SWITCH() documentation:

[John_Dlouhy]

Thank you so much, Jeremy!! I’ll give this a try and report back. Fingers crossed I can get it right!

Cheers,

John

[Sent on the go from my iPhone. Please excuse brevity and/or typing errors.]