Simon Willison’s Weblog

The Airtable formulas at the heart of everything

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”:

Staging__CA_COVID_vaccines_copy__Counties_-_Airtable

County total reports:

Staging__CA_COVID_vaccines_copy__Counties_-_Airtable

County percentage yes:

Staging__CA_COVID_vaccines_copy__Counties_-_Airtable

These rollups use formulas on the Locations table:

Latest report:

Staging__CA_COVID_vaccines_copy__Locations_-_Airtable

Number of reports:

Staging__CA_COVID_vaccines_copy__Locations_-_Airtable

Latest report yes?

Staging__CA_COVID_vaccines_copy__Locations_-_Airtable

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}
)

CA_COVID_vaccines__Locations_-_Airtable

This is The Airtable formulas at the heart of everything by Simon Willison, posted on 23rd March 2021.

Part of series VaccinateCA internal blog

  1. New call queue ready to test. Also geography. - March 7, 2021, 5 p.m.
  2. APIs for importing locations - March 9, 2021, 5 p.m.
  3. VIAL is now live, plus django-sql-dashboard - March 15, 2021, 5 p.m.
  4. The Airtable formulas at the heart of everything - March 23, 2021, 5 p.m.
  5. VIAL: Preparing for some collaborative testing - April 1, 2021, 5 p.m.
  6. A CSV export, JSON import workflow for bulk updating our data - April 28, 2021, 5 p.m.

Next: Weeknotes: SpatiaLite 5, Datasette on Azure, more CDC vaccination history

Previous: Weeknotes: django-sql-dashboard widgets