There is not a lot of documentation around advanced usage of the Airtable API, but it has some fantastic features that let you do some really powerful things, like custom sorting and filtering with formulas.

In this post, I’d like to shed some light on how I used the filterByFormula parameter for a recent project of mine.

Using the Formula

One of the main features of EatFreeOrCheap.com (a website where you can see restaurants where kids can eat either free or really cheaply) is the ability to view restaurants by day of the week, so if it is Monday today, you can see all the restaurants that have good deals on Mondays for you to enjoy. This is easily possible with Airtable Views, but I wanted something a bit more dynamic than having to create and maintain a separate View for each day of the week.

Our criteria (in this case):

• “Days” field is a “Multiple Select” Airtable field type
• We want to show all records where “Days” includes “Monday”

The Code

Since our field is a Multiple Select and we want to find rows where ANY value is “Monday”, we will use the Find() function with our field name and our value, like so:

filterByFormula=Find("Monday", Days)

If we were looking for rows where they “Days” field was ONLY “Monday”, we would use equality:

filterByFormula=Days="Monday"

When we properly escape our formula value, it will look like this:

filterByFormula=Find(%22Monday%22%2C+Days)

When we combine it all into a URL for the API, it looks like this:

https://api.airtable.com/v0/<accountId>/<baseName>?view=PublicAPI_v1&filterByFormula=Find(%22Monday%22%2C+Days)

In this case, I am using a custom View for the API named PublicAPI_v1, which allows me to apply other default filtering and sorting rules, like whether or not the deal has been approved to be live on the website. When using filterByFormula, the new filter will be applied on top of the existing View filters to further filter the results down. It will not replace filters already on your View.

Properly Escaping Values

Values should be escaped with encodeURIComponent. If you are having trouble knowing how to properly escape the parameters for the API when using formulas (or just want the value because it will be hard-coded), Airtable has published a Codepen that helps with this: https://codepen.io/airtable/pen/rLKkYB

Airtable as a Database

The end result with the API along with custom Views, sorting and filtering, is that Airtable can effectively be your database, enabling you to deploy a static site that pulls the data in a lot more easily.

In the case of EatFreeOrCheap.com, it is made with Next.js and deployed with Zeit Now so I can get the benefit of server side rendering on a modern React app. This setup is completely free, and so easy to put together, it’s like cheating somehow.