Return rows that contain multiple select item

[Kevin_Batdorf]

Hi, I would like to return all rows that contain a specific term.

For example, I have rows that have two terms, [Default, Bar]. Using AND(term='Bar') will not work, but using FIND('Bar',term) will work. Except that it will also return results that have the term “Barber” as well.

Is there a way to select rows that contain a term? Like FIND([Bar], term) for example?

Thanks

[Databaser]

Hi @Kevin_Batdorf and welcome to the community!

A formula field only works on the same record, so if you want to select multiple rows, are you looking to display a value in every record using a formula field or to filter your result via the filter option? What field type are u using for the multiple “terms”?

[Kevin_Batdorf]

Hey @Databaser thanks for the reply. The field is a “multiple select” and I’m using the API via filterByFormula (sorry if I wasn’t clear on that).

So essentially I want to get “All rows that have the term “Bar” applied” keeping in mind there can be multiple terms (so it’s not an exact match lookup) and it shouldn’t return rows that have similar terms (so a loose search also won’t work).

Hope that’s more clear. If not let me know, and thanks for helping out.

[Databaser]

Aha, if it’s API stuff, I’m not your man, but there certainly experts here who should be able to help you out.

[kuovonne · Accepted solution]

If none of your multi-select options contain commas or quotes, you can use a formula like this:

IF(
    OR(
        AND(
            FIND("Option", {MultiSelect}),
            LEN({MultiSelect}) = LEN("Option") 
        ),
        FIND("Option, ", {MultiSelect}),
        FIND(", Option", {MultiSelect})
    ),
    "Got it!"
)

This formula looks for one of three case:

• the desired option is the only option selected, and thus the length matches
• the desired option is one of multiple options selected (but not the last option), and thus will be followed by a comma and a space.
• the desired option is one of multiple options selected and is the last option selected, and thus will be preceded by a comma and a space.

This formula will not work if your option has a comma or a double-quote because Airtable will add extra quotes to the multi-select option when evaluating the formula.

[Kevin_Batdorf]

Thanks @kuovonne that looks promising. We’re not using commas or quotes so it should be fine. I’ll try it on Monday and follow up.

Also, thanks @Databaser for taking the time to look too.