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?
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?
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?
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?
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:
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:
Thank you so much, Jeremy!! I’ll give this a try and report back. Fingers crossed I can get it right!
Cheers,
John
CSent on the go from my iPhone. Please excuse brevity and/or typing errors.]
