SWITCH() Problem

[E_K]

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.

[kuovonne]

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]

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.

[kuovonne]

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]

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

[kuovonne]

@E_K

Unfortunately formula field reference does not say anything about “pattern” or “result” possibility to be formulas.

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