Creating Filters - And - Or

Hi! The solution to this problem kind of depends on how your specific table is set up. For example, if you had a column for “Tea Type” as a single select field, then you could create a set of filters like this, which effectively allows you to create an “Or” filter:

Could you give me a little more information about how your table is set up?

Thank you Katherine for the response.
I think I understand your suggestion, but I don’t think it’s a viable solution in my particular circumstance (unfortunately).
Long story short…
Client keeps a handwritten log book with a job number and a job description.
Several of us who run the computers file everything using the job number (as well as our own description, which closely matches hers). So that’s the challenge is to match up descriptions with job numbers.
So while adding the extra ‘field’ of tea type, it would just involve going back into the records (almost 10 years), and adding the various tea types to all of the job numbers.
I do thank you though for the suggestion, and I’ll see if maybe there is a way to incorporate this somehow.
To answer your question directly though, it’s really just the two fields of importance here…
Job number
Description
(we use another field for client name too, but that’s not very important for search reasons).

Probably the easiest solution for me, is to do a search for “GT” and then just add into the description the words “Green Tea” - but again, that would mean doing this to hundreds of records potentially.

I also would like to request this feature. I have inspections that need sorting for a view and I need to see which of them has either signature 1 OR signature 2 fields empty AND are also a certain status.

The best way to get around this problem right now create an extra formula field with IF and OR in it, then to apply an AND filter.

Example:
To filter to show just orders where the amount of tea is 20 oz, and the tea type is either “GT” or “Green Tea.” First, make a formula field (you can name it something like If GT or Green Tea) which will return “true” if the type of tea in the tea type field is either GT or Green Tea:

IF(OR({Tea Type} = “GT”, {Tea Type} = “Green Tea”), “true”)

Then, apply 2 filters:
Where {If GT or Green Tea} is true
And {Amount (oz.)}=20

Hope this technique is helpful!

Also relevant: Filters lock to 'And' or 'Or' after the first selection

Hello Matt, the reason we don’t allow combining AND and OR operators in the filter menu directly is that the order of precedence between the operators would be indeterminate without parenthesis. In other words, it would be ambiguous whether you meant (Condition 1 AND Condition 2) OR Condition 3, vs Condition 1 AND (Condition 2 OR Condition 3).

Thank you for the follow up Katherine.
I understand your reply too regarding Matt’s post where the “precedence” is the issue with using a combined AND OR search.
Might I offer a suggestion, to just base the precedence on the order in which the filters are added?
So it would always be (Cond. 1 AND Cond. 2) OR Cond. 3

So if you wanted it the other way in your example, you would just filter like:
(Cond 2 OR Cond 3) And Cond 1

Just a thought. Thanks again.

Katherine thanks for the explanation. I’ve just done a big project using Airtable and it would have been so much easier if, as the next commenter suggests, you could include functionality that bases the precedence of the search on the order in which the filters are added. It’s perhaps the only drawback that would stop me recommending Airtable. Or come up with a simpler way of creating a filter that doesn’t require laborious typing of terms.

This would make it impossible to achieve most permutations of filter conditions, such as (Cond. 1 AND Cond. 2) OR (Cond. 3 AND Cond. 4). Are there any products with an alternative filter approach that you’d recommend we look at for design inspiration?

Howie

Well it’s impossible to create other useful permutations now in any case without having to resort to Excel-style coding, which undermines Airtable’s natural advantage in being a beautifully designed and intuitive product. I don’t have a better suggestion for design inspiration–that’s why I hope geniuses like you will come up with them! But I would recommend having AND/OR as a default that could be switched off by the user if one wanted to do other permutations using coding (much though I still think that’s a drawback).

Martin

Howie, in one of my former lives, I used to create reports from our various enterprise-wide data warehouses using SAP’s Business Objects software. The way they resolved this requirement to have multiple filters with different Boolean operands was to have brackets to the left that visually connected the components in groupings and indentations.

The default for groupings was always AND, but you just double-clicked the operand and it would toggle between AND/OR depending on what you needed. And, you could drag groupings to indent them when you had various combinations of operands to consider.

Here is a fairly straightforward tutorial on how this worked. Note particularly the section on using the OR operand.

Would something similar be a viable design for Airtable implementation to resolve this feature request? Thoughts?

Would it be possible to allow for a toggle between “Visual Filter Builder” (or whatever) and “Filter By Formula”?

I use the API a lot and I’ve become quite adept at using the filterByFormula parameter to create quite complex filters like: AND(x,y,OR(AND(a,b),AND(b,c)))

when I’m using the Airtable interface and NOT the API, I find I rather miss having that ability.

This is brilliant! People at AirTable; please make this happen.

I really need AND/OR filtering.
I echo this suggestion:

I got a table with +1000 material records that links to projects, and the only way to find each specific record is by pre-filtering the list in the materials table, which at the is not really possible.

As @Katherine_Duh points out earlier in the thread, order alone isn’t enough to determine precedence.

Let’s be a bit more computer-scientific about this:

AND and OR are commutative (i.e. their order does not matter), and, Traditionally and for fairly deep underlying mathematical reasons, AND has precedence over OR, ie. it binds more tightly: " Cond.1 AND Cond.2 OR Cond.3 " does indeed mean " (Cond.1 AND Cond.2) OR Cond.3 ".

But also, " Cond.3 OR Cond.2 AND Cond.1 " should mean exactly the same: it’s the same as “Cond.3 OR (Cond.2 AND Cond.1)” because of the precedence of the AND operator and the order of the pieces can be turned around because of commutativity. Think of AND as * and OR as +: 1*2+3 = 3+2*1. (In some programming languages there are variations on this theme in that the entire thing might stop evaluating as soon as the final truth value of the expression is known - this breaks commutativity and introduces the need to define associativity.)

Without doing too much, Airtable could at least implement the precedence rule, then allow mixing of AND and OR, and leave it at that. If there are no parenthesis operators implemented, one may simply have to repeat some of the AND-tests inside each of the OR’s: just as a*(b+c) = a*b+a*c, one can work around needed parentheses for logical tests: for “Cond.1 AND (Cond.2 OR Cond.3)” one could write “Cond.1 AND Cond.2 OR Cond.1 AND Cond.3”.

Why not introduce Filter Groups, like Notion?

iTunes smart lists already did this back in 2001!

Hi all - I hope you’re doing well. I am happy to share that as of today, advanced filtering is now available in Airtable :tada:

You can check out the full details here, and we can’t wait to hear what you think!