Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: filterbyformula with values from array

2233 0
cancel
Showing results for 
Search instead for 
Did you mean: 
scb
4 - Data Explorer
4 - Data Explorer

Is there a way to use filterbyformula to filter with two options from the same column?

I have successfully retrieved all my records and now I want to be able to filter by 2 values from the same column.

This is what I have so far filterByFormula: AND( {ID} = {Extension}, {Type} = 'Type A'). I want to also include Type C in this filter so it shows both Type A and Type C. I cannot find the documentation to do this. 

Please advise. Thank you.

5 Replies 5

Is this what you want?

AND({ID} = {Extension}, OR({Type} = 'Type A',{Type} = 'Type C'))

 

dilipborad
9 - Sun
9 - Sun

Hello @scb,

Yes, the syntax is the same as @kuovonne mentioned.

However, there is a slight difference in results with using Regular Expression functions.

Now I'd put it as an example. All the explanations based

 

filterByFormula=AND({Group}="Marvel", OR(AND({Categories}="Theatre"),AND({Categories}="Property and Parks")))

 

  The only important thing you need to consider is the first example is used for exact matches only. When there are multiple categories are selected that include any of these categories it does not return those rows in results because as I mentioned it only matches those where only a single category is selected.

Now check this second example using the REGEXP_MATCH function.

 

filterByFormula=AND({Group}="Marvel", OR(AND(REGEX_MATCH({Categories},"Theatre")),AND(REGEX_MATCH({Categories},"Property and Parks"))))

 

It returns all the rows that contain any of the categories, No matter what other categories are selected with it.

I hope this helps, If not then let me know more about the data and structure you've used.

Reference Links:-

Airtable Web API - Using filterByFormula or sort parameters

Guide to REGEX() Functions | Airtable Support

@scb Is your column a single select, a multiple select, or some other field type? Screen shots with example data would be helpful. Are your field values hardcoded or do you need to dynamically change the field values for the formula at runtime?

 

@dilipborad Can you explain your choice to use the nested AND() functions? I am also curious about your choice to use REGEX the way you did (versus FIND(),  SEARCH(), or a single REGEX_MATCH() function)? There are many different ways to write formulas and I like to understand reasons for different choices. 

scb
4 - Data Explorer
4 - Data Explorer

Thanks all! The examples given work! 

Hello @kuovonne ,

You're absolutely right, there is no need to use AND functions with REGEX_MATCH.

I've also tested and it works fine as well.

Thanks for clarifying it. This is also working now.

filterByFormula=AND({Group}="Marvel", OR(REGEX_MATCH({Categories},"Theatre"),REGEX_MATCH({Categories},"Property and Parks")))

 Another thing about FIND(), SEARCH() and a REGEX_MATCH().

I mostly prefer to use Regular Expression on all different codes and matching so I just used it. I don't know the actual exact difference. But I'll also check for it as well.