Table of Contents

Advanced filters formulas Airtable block

Pau Sanchez Updated by Pau Sanchez

The Advanced Filter

Whenever you need to search for one or more than one record, the Airtable block offers you 2 options to "filter" those records: with the Basic filter or the Advanced Filter.

For some use cases, the basic filter would be enough, but if you want to set up specific conditions or more than one element to filter, Advanced Filters is the best option.

To activate Advanced Filter you just need to switch it:

There are many types of formulas that you can use, below is a list of the most common ones that you can use:

1. One fixed filter

This is best used when we want to get content from one column (field) that matches completely (case sensitive and the whole string is equal).

In the example below the column (field) is "Type" and the string to match is "Chairs", in the base Furniture:

SEARCH("Chairs",{Type})

2. One user input filter

You should use this option if what you want is to filter based on the input or selection from the user that will be stored in a variable.

In the example below the column (field) is "Type" and the string to match is the landbot variable @type, in the base "Furniture":

SEARCH("@type",{Type})

3. More than one filter

We use AND if we need to match 2 (or more) conditions with specific values.

In this example we check for items that match the landbot variable @color with the column (field) in Airtable "Color" and the landbot variable @type with the column (field) "Type":

Bear in mind that if we use "White" in the color and "Chairs" for type, it WILL NOT match the example above, because is has to be a full match, and color is "White, Brown, Black" in that object.

In case you want a case for "contains", where you are checking if "White" is one of the texts in the cell, please check the next filter:

AND({Color}='@color',{Type}='@type')

4. Search filter "contains" value in cell/column

If the cell for example has more than one value or contains more text, we want to check if a string (text) is contained for that field like in the example below:

Here we use FIND to check if the string "Brass" is in any of the items of the column (field) "Color".

It will match both items in the picture above.

FIND("Brass",{Color})>0

5. Search filter numeric value bigger than X

This is very handy if we need to check for specific values that are greater (>) or smaller (<) than a specified value.

In this example we check in the column (field) "Total units sold" for items with a value greater than 30:

({Total units sold}>"30")

6. Search for 2 conditions (less than and text)

This case is an example that checks numeric values at the same time that checks for a specific string in a column field.

Here the filter is looking for items sold less than 2 times and that their color could be red:

AND({Total units sold}<"2",FIND("Red",{Color})>0)

7. Search filter by more than X days from Today

This is a specific case that we might use to check for the difference of days compared with Today. The best case scenario would be if you want to make sure there is a difference of days between today and the date contained in a cell.

In the example below we check in the column (field) "Fulfill" for items fulfilled more than 60 days ago:

DATETIME_DIFF({Fulfill},TODAY(),'days')<-60

8. Filter by dates

Just like with numbers, we can check for dates (greater or smaller) with filters. Bear in mind that the date has to be set with the YYYY-MM-DD format.

In the example we check for orders fulfilled after the 2020-10-17:

{Fulfill}>"2020-10-17"

It is not possible to filter date equal date, we can check only for dates greater or smaller.

Checkboxes are handy for boolean values, we can verify if they are either checked (1) or not (0).

In the example we filter for items that in the column (field) were not checked (0):

{In stock}=0

10. Search this OR that

It is similar to AND, but in this case, only one of the conditions is met. Let's say we want to search either by name or email, then we will need to use OR.

In the formula below we are checking either if a product is not in stock or if theirs gross sales is greater than 0:

OR({In Stock}=0,{Gross sales}>0)

11. Search for an exact Match

In this case, we want that input from the user (@email) is in the Airtable base.

({emails}='@email')

12. Search for two (or more) exact Matches

In this case, we want that input from the user (@vendor and @phone) is in the Airtable base.

AND(({Phone number}='@phone'),({Name}='@vendor'))

13. Search for records that a specific field is empty

In this case, we want that input from the user (@email) is empty in the Airtable base.

({Email}='')

14. Search an event that is after/before today or a certain day

  Sometimes we might want to hold a series of events, everyday or every few days we will hold a certain event, and what if the event has passed but the visitors still click on the button of those activities? That will be very misunderstanding, so in this way, we will show the visitors available events, by using the formula IS_AFTER() in Webhook, and use Dynamic Data right after it. 

IS_BEFORE({Fulfill},TODAY())

Airtable Formulas

In case you want to check Airtable official docs for formulas, please check this article

How did we do?

How to Get an Airtable Token

Airtable usecase: Create an event registration bot with limited availability

Contact