Skip to main content

Hello Airtable Community!

I'm facing a challenge with constructing a filterByFormula and came across a similar issue discussed here: https://community.airtable.com/t5/formulas/formula-with-multiple-values/td-p/174768.

I have a column named Klasse, which can contain multiple values. I want to check if my search terms ('A', 'A1', or 'A2') match any of the entries in the Klasse column. Using the method suggested in the linked discussion, I crafted the following formula:

 

 
OR(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) > 0, FIND('A2', {Klasse}) > 0)

This formula is intended to verify if 'A', 'A1', or 'A2' exists in any of the Klasse entries. However, I encounter an issue where FIND('A', {Klasse}) incorrectly matches entries containing 'A' as part of a longer string (e.g., 'AM146'). I am looking for a way to ensure that only exact matches are identified.

Could you provide some guidance on how to refine this formula to achieve exact matches only?

@simentengs 

You can use an AND() statement as one of your 3 OR() statements like this:

OR(
AND(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) = 0, FIND('A2', {Klasse}) = 0),
FIND('A1', {Klasse}) > 0,
FIND('A2', {Klasse}) > 0
)

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

FIND treats your multi-select as a string, eg. "A, A1, A2". See in the "Calculation" field below.

With regex, we can account for all 4 cases (A is the only one, is in the beginning, is in the middle, is at the end):

 

OR(
REGEX_MATCH(Klasse, "^A,"),
REGEX_MATCH(Klasse, "^A$"),
REGEX_MATCH(Klasse, ", A$"),
REGEX_MATCH(Klasse, ", A,")
)

 

This way, the formula will only match if "A" is among the values, and will still match if A1, A2 etc. is also among the values.

Hope this was helpful 🙂

I'd love to explore further use-cases for Airtable and automation in your business, feel free to book a call with me if you're interested!

-- Best, Milan - Automable.AI

@simentengs 

You can use an AND() statement as one of your 3 OR() statements like this:

OR(
AND(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) = 0, FIND('A2', {Klasse}) = 0),
FIND('A1', {Klasse}) > 0,
FIND('A2', {Klasse}) > 0
)

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld


Thank you for your suggestion! However, I think there's been a slight misunderstanding. When i search for OR(A, A1, A2) with the find() method, I wrongly receive rows where none of those entries are present. For example, I receive rows that only contains a Klasse entry "AM146", because the string 'A' is found in AM146. 

I've now tried to adopt a delimiter-based approach in my formula to ensure precise matching, to avoid getting only partial matches.

Here's the refined formula I'm trying:

OR( FIND('|A|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0, FIND('|A1|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0, FIND('|A2|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0 )

This approach wraps each entry in the 'Klasse' column with pipes ('|') to form distinct boundaries, ensuring that 'A' is not confused with 'AM146',

It seems to work fine for rows where there is only one entry in the Klasse array, but when theres more it doesnt doesnt seem to return anything. 

I've attached a screenshot of the Klasse column to illustrate the Klasse setup better.

FIND treats your multi-select as a string, eg. "A, A1, A2". See in the "Calculation" field below.

With regex, we can account for all 4 cases (A is the only one, is in the beginning, is in the middle, is at the end):

 

OR(
REGEX_MATCH(Klasse, "^A,"),
REGEX_MATCH(Klasse, "^A$"),
REGEX_MATCH(Klasse, ", A$"),
REGEX_MATCH(Klasse, ", A,")
)

 

This way, the formula will only match if "A" is among the values, and will still match if A1, A2 etc. is also among the values.

Hope this was helpful 🙂

I'd love to explore further use-cases for Airtable and automation in your business, feel free to book a call with me if you're interested!

-- Best, Milan - Automable.AI


This seems to be exactly what I'm looking for. Thanks! 🤝😄

Happy to help @simentengs 🙂

Also, I just realized this can be simplified to a single regular expression:

 

REGEX_MATCH(Klasse, "(^A$|^A,|, A$|, A,)")

 

(The "|" means "OR" within the expression)

Which is perhaps more straightforward to work with, especially if you want to check for multiple possibilities in the same expression, so you want another nested OR outside of it.

-- Best, Milan - Automable.AI Free Airtable consultation

 

Reply


Terms & Conditions