Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 16, 2023 03:10 PM
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.
Oct 16, 2023 09:10 PM
Is this what you want?
AND({ID} = {Extension}, OR({Type} = 'Type A',{Type} = 'Type C'))
Oct 17, 2023 02:35 AM - edited Oct 17, 2023 02:40 AM
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:-
Oct 17, 2023 06:35 AM
@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.
Oct 17, 2023 07:00 AM
Thanks all! The examples given work!
Oct 18, 2023 11:14 PM
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.