Tim Dietrich

Custom Software Developer

Home Services Portfolio Blog About Contact Newsletter

Airtable: Filtering Records and Fields When Making API Calls

Back in February, Airtable introduced several new features to their API. One of the new features makes it possible to filter the records that are returned by the API, while another allows you to filter the columns that are returned.

I was recently working on an Airtable integration project, and those new features came in very handy. Let's take a look at the new features as well as a few examples of how to use them. To demonstrate the features, I'll make API calls to an Airtable database that was created using the Personal CRM template.

Getting All Records

Let's start by reviewing the URL that you would use to get all records and all fields from the Contacts table. Here's what that URL would look like:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts

The URL simply consists of the base's ID ("appSqJwsEmP3PeEFa") and the table name ("Contacts").

Filtering Records On A Single Field

Suppose that you want to return records based on a contact's name. In that case, you would use the new "filterByFormula" parameter.

To retrieve records where the contact's name is "Anthony Green," you would use this URL:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(Name="Anthony Green")

The formula being used is: Name="Anthony Green" That works perfectly when you know the full name of the contact. But what if you wanted to retrieve records using only part of their name?

In that case, you could use the filterByFormula parameter with the FIND function. The URL would look like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(FIND("Green", Name) > 0)

Filtering Records On A "Multiple Select" Field

In Airtable, the "Multiple Select" field type allows you to select zero, one, or multiple values from a list of values. A good example of this is the "Category" field in the Contacts table.

Suppose that you want to use the API to select all contacts that are in the "Personal" category. You might try something like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(Category="Personal")

However, that only returns contacts that are only in the "Personal" category. It excludes contacts who are in the Personal category and one or more additional categories.

To get contacts that are in the Personal category, regardless of whether that is the only category that they are in, you would use the FIND function again. For example:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(FIND("Personal", Category) > 0)

Filtering Records On A "Checkbox" Field

Filtering records based a Checkbox field can also be a little tricky. When a checkbox is checked, the value of the field is TRUE. Otherwise, its value is FALSE.

Suppose that you wanted to get a list of contacts where "Should Reach Out" is checked. You might try using a URL like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(Should Reach Out=TRUE)

Airtable will respond with an "INVALID_FILTER_BY_FORMULA" error. There are actually two problems with the formula.

First, the column name ("Should Reach Out") includes spaces. To resolve that problem, you surround the field name with braces. The second problem is that in order to use TRUE, you have to include parentheses.

The correct URL would look like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=({Should Reach Out}=TRUE())

Filter Records On Multiple Fields

So far, you've filtered records using only one field at a time. But what if you wanted to retrieve records based on multiple conditions? You can do that using Airtable's "AND" and "OR" functions.

For example, suppose that you wanted to retrieve records that are in the "Personal" category and whose "Should Reach Out" cell has been checked. That URL would look like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(AND(FIND("Personal", Category) > 0, {Should Reach Out}=TRUE()))

Notice that the filter formula includes the two filters that were discussed earlier, and that they are grouped using the "AND" formula function. So only records that meet both conditions will be returned.

Similarly, we can use the "OR" function to return records that meet at least one filter condition. For example, to retrieve records that are in the "Personal" category or whose "Should Reach Out" cell has been checked, we could use this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(OR(FIND("Personal", Category) > 0, {Should Reach Out}=TRUE()))

Filtering the Fields Returned

You've seen several examples of how you can use the "filterByFormula" parameter to filter the records that are returned by the API. You can also filter the fields that are returned using the "fields" parameter.

For example, suppose that you wanted to retrieve only the Name and Location values for all records in the Contacts table. That URL might look like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?&fields[]=Name&fields[]=Location

Of course, you can combine the "fields" parameter with the "filterByFormula" parameter. For example, to retrieve only the Name and Location of records in the "Personal" category, you would use something like this:

https://api.airtable.com/v0/appSqJwsEmP3PeEFa/Contacts?filterByFormula=(FIND("Personal", Category) > 0)&fields[]=Name&fields[]=Location

Wrapping Up

As you can see, the "filterByFormula" and "fields" parameters add a lot of power and flexibility to the Airtable API.

The key to making the most of the "filterByFormula" parameter is knowing what formulas Airtable supports and how to use them. If you are interested in learning more, check out the Airtable Formula Field Reference. And for more Airtable resources, click here.