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}
)
More recent articles
- Weeknotes: the Datasette Cloud API, a podcast appearance and more - 1st October 2023
- Things I've learned about building CLI tools in Python - 30th September 2023
- Talking Large Language Models with Rooftop Ruby - 29th September 2023
- Weeknotes: Embeddings, more embeddings and Datasette Cloud - 17th September 2023
- Build an image search engine with llm-clip, chat with models with llm chat - 12th September 2023
- LLM now provides tools for working with embeddings - 4th September 2023
- Datasette 1.0a4 and 1.0a5, plus weeknotes - 30th August 2023
- Making Large Language Models work for you - 27th August 2023
- Datasette Cloud, Datasette 1.0a3, llm-mlc and more - 16th August 2023
- How I make annotated presentations - 6th August 2023