Contents x
    Airtable Web API - Using filterByFormula or sort parameters
    • 25 Mar 2024
    • 4 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    Airtable Web API - Using filterByFormula or sort parameters

    • Updated on 25 Mar 2024
    • 4 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article Summary

    Plan availability

    All plan types

    Permissions

    Token-based access - In order to make API requests, you'll need to authenticate using one of the methods outlined here.

    Learn how to filter or sort the results of an API response.

    Filtering records in API requests

    There are two different ways to filter records in API requests:

    1. One way to filter records is to pass a view parameter in an API request containing the list records scope, yielding only the records as they appear in a specific table view. The link above contains more details about how to pass the view parameter in a request.

    2. An alternative way to filter records in a call containing a list records scope is to use the filterByFormula parameter which we will outline in more detail below.

    When filtering an API response using the filterByFormula parameter, a valid formula must be used which will evaluate each record. This formula will need to be included in the table of the base where the API call is being requested. 

    To help demonstrate this, let’s say you have a table of ice cream flavors:

    Including a list records scope in an API request to this table without any parameters will return all the flavors. Now, let’s say we only want to return flavors with 5-star ratings using the filterByFormula parameter:

    Step 1:

    The first step is to create a formula field in the table containing flavors that can evaluate whether the flavor has a 5-star rating in the "Rating" field. In this example, a simple formula could be used (Rating = 5) that performs a boolean true/false output, but your actual use case may involve more complexity.

    Step 2:

    As seen below, the formula entered is working correctly – only “Cookies & Cream” and “Cookie Dough” have a value of ‘1’, because they are the only flavors that have 5 stars. We can now be confident that if we use this formula as our filterByFormula parameter, it will return the appropriate values over time.

    Step 3:

    Next, you will need to add the formula to the filterByFormula parameter in your request.

    Note

    If you are not using our official Javascript client, Airtable.js, the formula must be encoded first before passing it as a value. You can use this tool to encode either the formula or the URL before using it in an API request.

    Step 4:

    You should now have an encoded URL which will look something like this: https://api.airtable.com/v0/{your_app_id}/Flavors?filterByFormula=Rating%3D5

    Step 5:

    You can now test the request in a tool like Postman. In the test shown below, you can see that only ‘Cookies & Cream’ and ‘Cookie Dough’ were returned:

    Filtering dependencies

    • If the response does not filter for the expected records, we recommend double-checking the formula in the Airtable base first to ensure it is returning the expected values in the formula field of the records you were expecting would be filtered. 

    • Any records that appear as 0, false, NaN, [], or #Error! in the formula, then the output will not be included in the response.

    • If the filterByFormula parameter is combined with the view parameter, only records in that view that satisfy the formula will be returned.

    Sorting records in API requests

    There are two different ways to sort records in API requests:

    1. One way to sort records is to pass a view parameter in an API request containing the list records scope, yielding the records in the API response in the order that they appear in a specific table view. The link above contains more details about how to pass the view parameter in a request.

    2. A second way to filter records in a List records request is to use the sort parameter. We'll cover the general steps for this below.

    Using the ice cream flavors table example used above, you may want to return the flavors in order of highest to lowest rated:

    Step 1:

    First, you will need to create an encoded URL to use in your API request. Similar to filterByFormula above, you can use this tool to encode a URL with a sort on the “Rating” field, with the direction of “desc”. It will look something like this:

    https://api.airtable.com/v0/{your_app_id}/Flavors?sort%5B0%5D%5Bfield%5D=Rating&sort%5B0%5D%5Bdirection%5D=desc

    Note

    A value of "asc" could be used at the end of the formula instead of "desc" to sort the results in ascending order instead.  

    Step 2:

    You will then need to add the encoded URL as an object in the sort parameter of your API request.

    Step 3:

    You can now test the request with a tool like Postman to see if the parameter worked, the records returned are ordered by rating, highest to lowest:

    Note

    If combined with a view parameter, the sort parameter overrides the sorting of the view specified in the view parameter. If neither the sort nor the view parameter is included, the order of records is arbitrary.

    URL Encoding

    If you are not using our official Javascript client, Airtable.js, you will likely need to URL-encode your parameter values to get a valid URL. You can use our URL encoder to do this, by filling in your formula and other parameter values and copying the resulting URL:

    360094731073Image2020-09-29at123716pm.png

    FAQs

    Can I use field IDs in my filterByFormula API call?

    No. You can only use field names. We recommend using field descriptions to call out the implications of changing any field names being used in API calls.

    Why isn’t my filterByFormula filtering correctly?

    It may not be a valid formula – we recommend checking the formula by adding it to a field in the Airtable grid. This will help confirm what values the formula would be expected to filter.

    Was this article helpful?
    What's Next