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!

[Katherine_Duh]

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?

[cyen_obite]

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.

[Sarah_Cliff]

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.

[Katherine_Duh]

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!

[Katherine_Duh]

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

[cyen_obite]

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.

[Martin_Giles]

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.

[Howie]

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?

[Martin_Giles]

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