# IF Statement Needed

Topic Labels: Formulas
Solved
971 4
cancel
Showing results for
Did you mean:
7 - App Architect

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.

1 Solution

Accepted Solutions
14 - Jupiter

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:

4 Replies 4
14 - Jupiter

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?

7 - App Architect

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?

14 - Jupiter

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:

7 - App Architect

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