Hello all! I don't know how else to submit a feature request, but the formulas' support of multi-select fields is counterintuitive, so I thought I would share my experience.
I want to have a computed field named {Is Executive?} where the value is TRUE if the multi-select field "Role" contains any of "Manager", "Label A&R", "Agent", "Distribution" and some others.
There are multiple ways I tried to approach this:
- OR({Role} = "Manager", {Role} = "Label A&R", {Role} = "Publishing A&R", {Role} = "Agent", {Role} = "Distribution", {Role} = "Music Tech", {Role} = "Executive")
Obvious issue: Multi-select is not properly handled (the formula only works if the record has exactly one Role). - FIND statements chained with OR (like https://community.airtable.com/t5/formulas/screen-a-multiple-select-field-for-a-certain-value-to-fil...)
Issue: If any options in the multi-select have common substrings, false matches will result.- It's really important that we get this right, because we want to match "Executive" but not "Executive Assistant". Can't guarantee the names won't have substrings of each other.
- For simplicity, say the field is Blood Type. Its options are A, B, AB, O. Let's pretend multiple values are acceptable. A FIND statement with argument "A" will falsely match "AB", because FIND treats the whole field as a string.
- Doesn't work: Array functions as currently implemented.
- What does work is to create an intermediate field {Role machine_readable} and then match against it with Regex. The formula for {Role machine_readable} puts quotes around each item (the brackets are not strictly necessary):
'["' & SUBSTITUTE({Role}, ', ', '","') & '"]' then the {Is Executive?} field uses Regex and word-boundary characters:
REGEX_MATCH({Role machine_readable}, '"\b(Manager|Label A&R|Publishing A&R|Agent|Distribution|Music Tech|Executive)\b"')
This is an insane workaround and I am only comfortable doing it because I come from an engineering background. I essentially created a virtual primitive for arrays of strings, which should be a native primitive provided by the platform in this context!
- Array functions should work on multi-select values
- A new operator "CONTAINS" or "INCLUDES" would be great, because even if it's ugly, I could write the following, which is easy to understand: OR({Role} CONTAINS "Manager", {Role} CONTAINS "Label A&R", {Role} CONTAINS "Publishing A&R", {Role} CONTAINS "Agent", {Role} CONTAINS "Distribution", {Role} CONTAINS "Music Tech", {Role} CONTAINS "Executive")
- The fact that I had to do this in Regex means no one else on the team with less technical experience will be able to maintain this part of the database -- not good at all.
