Todo Example in AirTable

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 Genius

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 String, Number, and 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.

Scoring

Let’s review AirTable’s scoring in my rough assessment matrix:

1. Permit cycles (1)
2. Permit calculated attributes (3)
3. Changeset support (2)
4. Schema changes are easy and cascade easily (3)
5. Self-Documenting (3)
6. Data can be delivered natively in a variety of views (3)
7. Data is readily accessible to non-technical staff (3)
8. Data is readily updated by non-technical staff (3)
9. Price (2)

Total: 21

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 id field.

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 String.

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.

Calculating Data

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:

To produce:

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 Email Addresses 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 well:

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 Owner Name:

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.

Other Features

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)

Conclusion

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

• Curriculum
• Education
• Airtable

• Next: Solutions for Modeling the Accounting
• Previous: Solution Evaluation: Spreadsheets