Skip to main content

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), 😎, “COR”).


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

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.

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.


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.

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, epattern, result … , default])


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, epattern, result … , default])



Thanks again @kuovonne.

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

Thanks again @kuovonne.

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



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

Reply


Terms & Conditions