Help

Re: SWITCH() Problem

2296 1
cancel
Showing results for 
Search instead for 
Did you mean: 
E_K
6 - Interface Innovator
6 - Interface Innovator

I have a multiple select field {Services} with entries like:
CIQ
COR SKED
CNL
DLA
TECH STOP
etc.

Since it is multiple select, they can be combined in one record, so many combinations are possible.

I want to use the SWITCH() function to apply to this field and produce a result depending on contents like:

SWITCH(
{Services},
MID({Services}, FIND(“CIQ”, Services), 3), “CIQ”,
MID({Services}, FIND(“CNL”, Services), 3), “CNL”,
MID({Services}, FIND(“COR SKED”, Services), 8), “COR”).

Oddly, I receive error message which I can not find the reason of. Any help and ideas will be greatly appreciated.

5 Replies 5

The “pattern” part of a SWITCH must be a simple value, such as a fixed text string or a number. It cannot be a field or another formula.

Try using nested IF functions instead.

IF(
  FIND("CIQ", {Services}),
  "CIQ",
IF(
  FIND("CNL", {Services}),
  "CNL",
IF(
  FIND("COR SKED", {Services}),
  "COR"
)))

On the other hand, what are you trying to accomplish with this? It looks like you are trying to reduce the list of services to a single service.

E_K
6 - Interface Innovator
6 - Interface Innovator

Thank you @kuovonne for your reply. I was reading this post about another SWITCH() case and understood that formulas can be inside SWITCH pattern.

What I am trying to do is: if “pattern” strings are found among listed services, then a complex formula works differently with this field and other fields to generate a long formalized text. This text contents vary depending on contents of {Services} and that is why different output should be produced in the text string.

I have done it with nested IF(), but SWITCH seems to be more elegant and easier for modification approach - when new {Services} are added to the multiple select (life goes on), nested IFs are very difficult to modify, while SWITCH is much easier to update.

Formulas can be the “result” part of a SWITCH. They cannot be the “pattern” part of a SWITCH.

See the formula field reference for more info.

SWITCH(expression, [pattern, result … , default])

E_K
6 - Interface Innovator
6 - Interface Innovator

Thanks again @kuovonne.
Unfortunately formula field reference does not say anything about “pattern” or “result” possibility to be formulas. We learn it experimental way :grinning:

Yes, the formula field reference doesn’t cover everything. If you feel that this is important information to include in the documentation, I suggest that you contact support with your suggestion. I find support to be very helpful in updating documentation. (I do not work for Airtable.)