Simon Willison’s Weblog

The Airtable formulas at the heart of everything

23rd March 2021

Originally posted to my internal blog at VaccinateCA

While working on building a Counties.json API endpoint for VIAL I realized I wasn’t entirely sure how the “Total reports” and “Yeses” numbers in this piece of JSON were calculated:

    {
      "County": "Glenn County",
      "County vaccination reservations URL": "https://www.countyofglenn.net/news/press-release-public-health-public-information-public-notice/20210301/covid-19-vaccine-interest",
      "Facebook Page": "https://www.facebook.com/GlennCountyHHSA/",
      "Notes": "(Updated: March 22) **Eligibility**: 65+, A...",
      "Total reports": 11,
      "Twitter Page": "https://twitter.com/glenncountyoes",
      "Vaccine info URL": "https://www.countyofglenn.net/dept/health-human-services/public-health/covid-19/covid-19-vaccine-information",
      "Yeses": 3,
      "id": "rec0QOd7EXzSuZZvN"
    }

This lead down the fascinating rabbit-hole that is our Airtable formulas. Numbers like this are calculated using a combination of Airtable Rollups and Airtable Formulas, which invisibly form the heart of our entire organization.

Of particular interest: the “Latest report yes?” column on Locations, currently defined like this:

IF(
  OR(
    SEARCH("Vaccinating essential workers", ARRAYJOIN({Availability Info})) != "",
    SEARCH("Scheduling second dose only", ARRAYJOIN({Availability Info})) != "",
    SEARCH("Yes", ARRAYJOIN({Availability Info})) != ""
  ),
  1, 0
)

Here are screenshots I gathered as I followed them back to figure out how they worked:

County “Yeses”:

County total reports:

County percentage yes:

These rollups use formulas on the Locations table:

Latest report:

Number of reports:

Latest report yes?

And a bonus one: here’s is_callable_now which I need to better understand how our call targetting works:

AND(
  OR(
    {Do not call until} = BLANK(),
    {Do not call until} < NOW()
  ),
  OR(
    {Next available to app flow} = BLANK(),
    {Next available to app flow} < NOW()
  ),
  NOT({is_soft_deleted}),
  NOT({do_not_call}),
  {Phone number}
)