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
Best answer by kuovonne
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:
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.
+25Hi @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”?
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.
+25Hey @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.
Aha, if it’s API stuff, I’m not your man, but there certainly experts here who should be able to help you out.
+29If 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:
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.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.