Creating relationships in Airtable via the UI and API

It's a bold claim to say that Airtable is superior to the spreadsheet—but for so many business and personal tasks, it really is. One of the big advantages of using a database is the ability to create relationships between records. By modeling relationships in your data, it becomes possible to look at information from multiple perspectives. A database that lists staff, for example, might create relationships between employees and their managers, along with relationships between people and departments.

Relational data models make information more understandable and less abstract, since so much of our everyday world depends on seeing the connections between things. Relationships also give you power in presenting your data, letting you look up information from multiple sources and display it in a single view. This power is what drives everyday web applications—and it's what allows you to prototype your own light-weight web apps in Airtable without writing any code.

Creating relationships

While Airtable's UI makes it easy to create relationships between records one at a time, what may be less obvious are its tools for bulk relationship updates in the UI and API. Let's walk through all three examples.

One-off relationships

First, you're going to need a field to capture the relationship. If you don't already have one, create a new field and choose Link to another record. You'll need another table in your base to link to, or you can create one during this step.

From there, it's just a couple clicks to assign a link to a record.

Create link.gif

But this can quickly get tedious if you're dealing with a large dataset, like imported spreadsheets. Airtable has you covered.

Bulk relationships

You can convert a text field into a relationship field. Airtable looks for a match in the text you've entered in the primary (leftmost) field of whatever table you link to.

Field conversion2.gif

One note here: If you link to an empty table, Airtable will create records according to the entries in the text field you're converting. This can be a handy way to get started on a new table.

Creating relationships via the API

While Airtable eagerly documents this last tip, I've got a secret for accomplishing the same task via the API. According to the public API docs, creating relationships requires you know the internal IDs of the target records:

To link to new records in  Media Property, add new linked record IDs to the existing array. Be sure to include all existing linked record IDs that you wish to retain.

Here's the example code using node:

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('appoAITxTMYXNz4Pw');

base('AI in Science Fiction').create({
  "Name": "Durandal",
  "Housing Type": [
    "Shipboard AI"
  ],
  "Media Property": [
    "recRG9adqAEEFM5gc" // < An internal record ID
  ]
}, function(err, record) {
    if (err) { console.error(err); return; }
    console.log(record.getId());
});

Note the record ID being set for "Media Property:" recRG9adqAEEFM5gc.

If you're creating records and relationships via the API, this introduces some complexity. It becomes necessary for your code to look up the record IDs you want to link to, or for you to hard-code them ahead of time. Wouldn't it be nice if you could just provide the record's name?

Turns out, you can! Pass the typecast parameter with your create/update/replace calls, and this becomes valid input:

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('appoAITxTMYXNz4Pw');

base('AI in Science Fiction').create({
  "Name": "Durandal",
  "Housing Type": [
    "Shipboard AI"
  ],
  "Media Property": [
    "Marathon Trilogy" // < Plain text, not an ID!
  ]
}, {typecast: true}, function(err, record) {
    if (err) { console.error(err); return; }
    console.log(record.getId());
});

If Airtable can't find a corresponding record for the text you provide, it will create one as part of this transaction. This makes things a lot smoother: you don't have to look up internal record ID's, and you don't have to handle any errors if your destination record doesn't exist. Of course, this means you'll want to carefully validate any input you use here, or you'll end up with junk in the linked table.

This is undocumented, so subject to change in the future. But my hunch is that this is the very same mechanism the Airtable UI relies on to accomplish the text-to-linked record conversion described above, so at least we knew we're not the only ones using it.

Using relationships

Once you've modeled the relationships between records, it becomes easy to use Airtable's tools to summarize data from multiple tables. Check out these docs on Lookup, Count and Rollup field types to learn more.

Saving a non-profit six figures a year using Squarespace, Airtable and Glitch.com