Creating Filters - And - Or

[cyen_obite]

Hello all!
Really glad I discovered Airtable!

I was experimenting with the filters tonight, and noticed that it doesn’t look like I can combine “And” searches with “Or” searches. Am I over looking something obvious, or is there another way around this?

Example: I have a list of job descriptions, and my client sometimes uses abbreviations.
So I want to search for:
20oz
AND
GT
OR
GREEN TEA

Thanks!

[Mark_Morgan]

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?

[Chester_McLaugh]

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.

[Nick_Page]

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

[Kim_Trager1]

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

cyen_obite:

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

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.

[Justin_Barrett]

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

[Martin_Sohnius]

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”.

[jamesvclements]

Why not introduce Filter Groups, like Notion?

[miguel_scaled]

iTunes smart lists already did this back in 2001!

[Jordan_Scott1]

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!