Help

Multi-select should support CONTAINS/INCLUDES operator which would match whole string values

Topic Labels: Formulas
406 1
cancel
Showing results for 
Search instead for 
Did you mean: 
samwinslow
4 - Data Explorer
4 - Data Explorer

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.
1 Reply 1
RonniEloff_VKDe
7 - App Architect
7 - App Architect

Ya I am trying to do some auto-processing from a Multi-Select field for eligibility and found there was no way to process the multi-select into individual Check marks for what was selected.  We have different people in our NPO handing different eligibility options and I just can't figure a way other than manual review the multi-select and parse it out into the appropriate check boxes.