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”:
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: Parquet in Datasette Lite, various talks, more LLM hacking - 4th June 2023
- It's infuriatingly hard to understand how closed models train on their input - 4th June 2023
- ChatGPT should include inline tips - 30th May 2023
- Lawyer cites fake cases invented by ChatGPT, judge is not amused - 27th May 2023
- llm, ttok and strip-tags - CLI tools for working with ChatGPT and other LLMs - 18th May 2023
- Delimiters won't save you from prompt injection - 11th May 2023
- Weeknotes: sqlite-utils 3.31, download-esm, Python in a sandbox - 10th May 2023
- Leaked Google document: "We Have No Moat, And Neither Does OpenAI" - 4th May 2023
- Midjourney 5.1 - 4th May 2023
- Prompt injection explained, with video, slides, and a transcript - 2nd May 2023