The power of Airtable's linked records

One of the unique aspects of managing your information in a database is the power of linking records together across tables. Linked records can eliminate redundancy, enforce data integrity, and enable new methods of automation—turning your list of records into a fully fledged custom app. Learn how you can put Airtable's linked records to work for you.

Should my field be a linked record field?

When you want to create a simple table with lists, tags, priorities, and other simple things, you don’t need to look beyond standard field types—like text and select fields. However, when you want to connect multiple tables together to establish a relationship between them, you need a special kind of field: linked records.

To get an idea of when you want to use linked records and when you don’t, let’s take a look at a couple of examples. To begin, if you want to create a base to manage your to-do list you might only need a single table and a series of standard fields. You can create a text field to name the task, a select field to add categorical tags to each task for sorting, grouping, and filtering purposes, a date field for due dates, and a rating field to specify task priority.

Using single select fields for task organization provides color-coded labels that make for easily identifiable information—especially when grouped and sorted—while also powering the kanban view, which organizes your tasks categorically in attractive stacks of cards.

Because these tasks can exist independently, and don’t require relationships with other records in this table or others, adding a linked record won’t provide any benefit.

However, in many cases, you do want to establish a connection between two tables so you can share references to records between them. Linked record fields facilitate these relationships and allow you to utilize the linked information in many helpful ways.

For example, if you’ve decided to create your own CRM in Airtable you will, at least, want one table for contact information and another to track activities related to each contact. If you created only one table for both things, you would have to re-enter details for each contact every time you added a new related activity. That’s a lot of extra work that you can eliminate—easily—with linked records.

In this scenario, every time you create a new activity you can simply add an existing contact to that activity and avoid unnecessary data entry. Any time you want to view that contact’s information, you don’t have to switch tables. Instead, you can expand the linked record and access that information directly from the Activities table.

If you wanted to apply the power of linked records to your simple to-do list, too, you could create a separate table called Projects and link tasks to specific projects you define. You could even create a third table to add sub-tasks to existing tasks.
Linked records also unlock three other field types that depend on them: counts, lookups, and rollups. Count fields give you an always-up-to-date number of linked records in a given cell. Lookup fields can reference a specific field in a linked record (e.g. an email address or phone number of a contact), and rollup fields can summarize multiple linked records in various ways. This additional functionality broadens the capabilities of linked records and we’ll discuss them all, in greater length, later in the guide.

• Whether or not you require the power of linked record fields depends on your specific workflows. Airtable doesn’t attempt to define an organizational paradigm, but rather provides an open environment with the necessary features for you to craft your own. When you don’t know if you need a linked record field or not, start simple and see how it works for you. Should you want to establish relationships between records in the future, Airtable will never get in your way. You can always make changes to add additional capabilities to your base and, if you find you don’t like them, you can always remove them as well.

Linking to existing records in another table with a linked record field

Once you’ve determined you want to use linked records to create a relationship between two tables, you can get started in just a few clicks. If you’ve created both tables, you only need to create a linked record field in one of them, choose the other table that you wish to link, and save the field. This will automatically create a corresponding linked record field in the other table as well.

Let’s jump back to the CRM example in the previous section to see what this looks like.

As you can see, the Activities table contains a linked record field that connects it to the Contacts table. By clicking the + in any empty cell beneath Contacts column, you can add a link to a specific contact. Every time you do, you’ll see a blue label containing the contents of that record’s primary field.

As mentioned earlier, you can double-click on any of the linked records (i.e. the blue labels) to expand them and view the information they contain.

Up top, you’ll see a line connecting the linked record to the table it calls home. Even though you’ll view this record from a different table, you can still edit the information if you want to do more than view it.

Once you get the hang of using linked records they’ll seem simple to implement in any situation that requires them. Nevertheless, it can feel like an unusual process at first when you’re accustomed to the world of spreadsheets. Make sure to check out this video tutorial if you’d like to see these tips, and the process of setting up linked records, in action.

Creating a new table from a linked record field

When building a base from scratch, or adding new relationships, it makes sense to create linked record fields as described in the previous section. When you already have a basic table with plenty of information and want to upgrade it with a linked record field, however, you can save yourself some time by converting an existing field.

If you have a text field with existing entries, simply double-click on its column header and change the type to linked record. Instead of choosing an existing table, choose the “+ Create a new table” option instead and save.

By default, this will create a new table that shares the name of the field you converted but you can always change it to something else if you prefer.

Avoiding manual data entry

Whether you find data entry soothing or tedious, nobody enjoys making an unnecessary effort. Airtable offers multiple field types that use linked records to save you the trouble of entering the same information more than once.

If you only need to count the number of linked records, you can add a count field .

After specifying the linked record field you want to count, save it and you’ll see the numeric total number of records automatically.

When you need more than a simple total, however, you can depend on lookup fields to help you out. For example, if you’ve linked a table of contacts to another table in your base and you want to display that contact’s email address without the need to expand the linked record you can facilitate that with a lookup field. Just like with the count field, you need to specify the linked record field you want to reference for the lookup. Additionally, you also need to specify the field in those linked records that contains the information you want (e.g. an email address).

Once you’ve saved the field you’ll see it now displays the information you chose automatically and will stay up to date as the information in the linked table changes.

You may notice that if you have multiple linked records in any record, the lookup field will display multiple values in a comma-separated list. This works great in most cases, but if you want more control you should use a rollup field instead—and we’ll discuss that option next.

One more thing: When using lookup (and rollup ) fields, sometimes the contents will contain information that requires a special kind of formatting (e.g. currency and percentages). When setting up these field types, you’ll find a “Formatting” tab that will allow you to choose the way it displays the referenced information.

These options will change based on what you choose to look (or roll) up. If no available formatting options apply, or you’re happy with the default option, you can just ignore these settings altogether.

Calculating based on linked records (rollup fields)

Rollup fields offer numerous ways to summarize specific fields in your linked records. For example, if you created an employee directory for your first table and a payroll table for your second, you can link those two together and use a rollup field to display the total amount paid to each individual employee.

Let’s walk through the process of setting up a rollup field after you’ve linked up your two tables.

First, add a rollup field to one of the tables.

Second, choose the linked record field the rollup should target. If you’ve only created one, you’ll only have a single option to choose from. If, however, you find yourself in a situation where you have numerous linked record fields, you can start typing the name of the field to filter your options.

Third, select the field from that table that you want to roll up. For example, if you wanted to summarize payments to an employee, you’d choose the field that contains the payment amount. Just like the last step, if you have a long list of fields to choose from you can start typing the name of the one you want to filter your options.

Finally, enter a rollup formula to determine how Airtable should summarize this field.

The SUM(values) formula will add up the total of a numeric field type (e.g. number, currency, etc.). In rollup formulas, the variable values automatically refers to the entered data values of the chosen field in your linked records.

The SUM() portion of the formula represents the function that formula must perform. SUM() indicates that you want a numerical sum of all the values, but rollup fields can perform several other types of calculations as well and many support other field types—not solely the numeric varieties. Consult the rollup field reference guide to get to know all your options.

Related video content