In the previous post, we saw that spreadsheets and databases are insufficient for rendering proper accountings that will make our internal and external stakeholders effective.
Spreadsheets have an accessible UI, but a poorly-structured model for managing data. Databases have a solidly-structured model for managing data, but their interfaces are inaccessible outside of a class of technical cognoscenti. In this post, I’ll demonstrate how AirTable provides a middle path.
Kathy Sierra once said that when your tool sucks, people say “This tool sucks.” When the tool is great, people say “I’m so awesome with this thing.” AirTable makes me think I’m very awesome indeed.
AirTable’s interface uses a spreadsheet-like interface for updating the
schema (“adding columns”) and for adding content (“adding rows” or “adding
records”). It provides features for documenting the intention of certain
columns (“self-documenting”) and provides a set of rich
Reference functions so that columns can be operated upon to produce the
values in other columns.
While using a spreadsheet as a visual interface façade is genius, to cleverly hide the power of a database under that facade is even more brilliant. In a vanilla spreadsheet, a cell couldn’t easily link to another cell in another sheet (or, “table”) and, as we saw, this creates a breakdown of referential integrity. Preservation of integrity seems, to me, to be the raison d'être of AirTable.
In it, we can link to other records and we can also link through those records to get their rows’ data. This can actually happen at multiple levels of depth.
To sum it up: It looks like a spreadsheet, but it has the rules, rigor, and intelligence of a database.
I’ll dig into these features in the rest of this post.
Let’s review AirTable’s scoring in my rough assessment matrix:
- Permit cycles (1)
- Permit calculated attributes (3)
- Changeset support (2)
- Schema changes are easy and cascade easily (3)
- Self-Documenting (3)
- Data can be delivered natively in a variety of views (3)
- Data is readily accessible to non-technical staff (3)
- Data is readily updated by non-technical staff (3)
- Price (2)
TodoList in AirTable
If you saw how we built an accounting in a spreadsheet, you’ll be familiar with the ideas that we’re now going to implement in an AirTable “[data]base.” For readability, this demo took me about 10 minutes to set up. If you’re not interested in the details, feel free to skip over the disclosure triangles and get the gist from the text.
Modifying Schema without SQL
One of the big drawbacks of a database is that we have to use (or we have to beg our DBA to run) SQL commands to build our database’s schema. AirTable makes schema management easy and uses a spreadsheet-based interface. This example shows that being done with the simple, intuitive spreadsheet UI.
Implementing a TodoList schema in AirTable is better than with a plain database or a spreadsheet.
AirTable provides a basic table with a few columns to train our guesses about how to use the application. This is a move that goes a long way to creating the impression of “intuitiveness.”
“Empty” tables provide us with a “Name” column that’s the primary key, a text “Notes” column, and an “Attachments” field that’s for attached files.
I right-clicked on “Name” to make it an auto-incrementing
Then I created the other columns: a text
Item field and an
Owner field. We
have obvious choices between text, email addresses, automatically-incrementing
numbers, etc. In seconds I wound up with this table:
Double-clicking the name lets us change the table name.
Let’s hop do similar work to this over in the
Members table and document that
First Name should be a
Right-click on the column:
…and add the info:
And add an email address column:
So we end up with a table like:
As we saw earlier, this is an incredibly long-winded and SQL-heavy operation in a database. This was mere seconds in AirTable.
Adding Content Without
INSERT But With Cleanliness
Adding data is just as easy as it was in a spreadsheet. Notably, we don’t have
to do any painful SQL
INSERT queries to get data loaded into
something with all the protections of a database. We can add content in two
ways: typing it in and calculating it with formulae. Additionally, we can
import CSV exports of Google or Excel sheets. AirTable supports the
right UI for making users “feel fast.”
Filling in a TodoList application AirTable is better than with a plain database or a spreadsheet.
Typing It In
This really doesn’t require much to say, but, you can type values into cells like a spreadsheet.
At any point, “Shift + Enter” lets us start a new row or “record:”
While any record is active, we can activate the “detail view” modal for entry by hitting the space bar.
Let’s return, for a moment, to our
Members table. In that table I typed in
the names of our various members. But something bothered my programmer brain
about doing so: if everyone’s at the same domain, why not calculate?
Let’s make email address something that’s calculated
But if we know that the email addresses will follow a formula, we can use our our AirTable formulae to generate it:
Well that’s pretty keen! The spreadsheet-like interface handily bests doing the same work with SQL commands.
AirTable’s Superpower: Associated Records and Lookups
Let’s assign tasks to owners, the Achilles’ heel of our former example.
Associate Records as Column Data
Let me remind you of the interface provided by a plain-old spreadsheet:
Here, we’d like to get “name from drop-down’s associated email address.” In AirTable, this is possible. Specify that we want to refer to another record (i.e. “row”) for the type.
Demo: associate records _as if_ they were column data
Specify the table that provides the records:
Decide whether to constrain number of links. This should probably be single-owner, but for purposes of demonstration I won’t make that call yet.
That’s it! Now let’s use it to do the linking:
If we click the little
+, a detailed modal pops up:
Rise, wash, repeat and….
Reaching Through Linked Records
Based on the work we just completed we see the autonumber
id of our linked
records. We’d really like to see the human
First name and
of our linked records. In AirTable, once a record is linked, we can reach
through it to get data held in the foreign record.
Let’s add columns to
TodoItems as if they were local. When we reach
through, we retain referential integrity
And specify the source:
We can do the same for an email address. With these changes in place…
With these lookup relationships set up, our
Team Members table has more data as
At this moment AirTable has won my heart. It’s providing a spreadsheet UI to get the power of a database in a way that’s graphical, explorable, and, honestly, fun.
Supporting Multiple Views in AirTable
You might have noticed this “Group” icon at the top. It does what you think:
group records by a common criterion. Let’s pair this with a feature of
AirTable that multiple views can be created per table. So let’s create a
new view on
TodoItems where items are grouped by the
We can name it as we like, I chose “Tasks by Owner.” I then used “Group” to group by “Owner Name:”
With this pattern, we can imagine other organizations creating views to accelerate their processes. As AirTable gets more records, your investments in clean data continue to yield benefits across your entire organization.
This document really only scratches the surface of AirTable. I’ll list a few other key features:
- We can hide columns we don’t want to see (if we like).
- Filtering (show things matching given criteria, etc.)
- Version history (rollback, if necessary)
- Private views (great for managers, auditors, etc.)
- Blocks (tools that create advanced views on your data; many of the good ones require account upgrade)
In this post, we’ve seen how AirTable provides a balance of ease of use à la a spreadsheet while providing a strong structure for data à la a database. It makes the data easily expressible to a wide variety of rules and, owing to its simple interface, invites other teams and organizations to join in leveraging it as a central brain for large projects.
Next: Overview of the Accounting for Flatiron School’s Software Engineering Curriculum