If you've been following along with this guide, your table is starting to look pretty robust, with different field types and a variety of different views.
(If you haven't been following along with the guide, then you can copy the embedded base above to get caught up.)
We now have all the tools we need to start building out more tables to store other information that's relevant to running our label, whether that's a table to keep track of our album sales, a table to keep track of our artists' tours and other performances, or a table to keep track of music distributors and our agreements with them.
Importantly, when we make these new tables, they won't be just storing information in isolation from one another: we'll be able to represent just how our artists, albums, and more are related to each other, using a special field type called the linked record field, which takes advantage of Airtable's relational database powers. No more duplicate data or conflicting edits!
- Main points
- Relational databases and linked record fields
- How to set up linked record fields
- Method #1: create a new linked table from an existing field (recommended!)
- Method #2: link two existing tables
Main points
- Airtable is a relational database, which means that it's designed to represent the relationships between different concepts and/or objects.
- A linked record field represents a relationship between two different objects, ideas, or people. You can use it to link two tables together, like Artists and Albums.
- You can convert an existing field into a linked record to create a new linked table or you can link two existing tables together.
- Linked record fields are reciprocal across tables—so making a linked record field that links from the Artists table to the Albums table will also create a linked record field that links from the Albums table to the Artists table.
Relational databases and linked record fields
Remember how Airtable bases are called... well, "bases"? That's because each base gives you the flexibility and power of a relational database. A database is essentially a collection of organized information, and a relational database is a type of database that uses related tables to organize information. When you use a relational database, not only can you better model how different concepts, projects, or people are related to one another, you can also avoid duplicated info and conflicts in your workflow.
In Airtable, we can represent the relationships (links!) between records in different tables with a special field type called the linked record field. So, in our Pacific Records base, if we had a table of musical artists and a table of albums, we could use linked record fields to link the table of artists and the table of albums together so that each artist is linked to their albums, and each album is linked to its associated artist.
Linked record fields contain blue link tokens; each token represents the link from one record to another record. Clicking on the link will expand the record that has been linked to, allowing you to surface and edit important information from another table with a single click.
(The above gif shows what your base will look like by the end of this module.)
Linked record fields can also help you ensure that you only have to enter information one time and make changes only in one location—if, for example, an album name changes right before release, or an artist decides to rebrand, you need only change this information in one place, and it'll update everywhere else that information is linked. With more advanced uses of linked records, you can also use them to summarize information from other tables to answer more detailed questions like "Which of our artists has sold the largest number of records in total?" or "Which of our artists has the highest average peak chart position?"
How to set up linked record fields
Let's return to the Pacific Records base once again. Suppose that we want to start storing more information about each of our artists' albums—like each album's streaming numbers, release dates, and so on. It doesn't make sense to store all this information that's specific to albums in the Artists table because the structure of the Artists table is specifically customized for information about the artists. Instead, we should make a whole new table—Albums—and link it to the existing Artists table.
There are two different methods you can use to link together the artists and albums. If you already made a field for albums in your Artists table, you should use your existing field to create a new linked table.
Method #1: create a new linked table from an existing field (recommended!)
We can quickly create and populate a new Albums table with our existing values by converting the existing Albums field into a linked record field. Neat!
To get started, return to the Albums field that you made way back when you were setting up all of the fields.
This single line text field is doing an admirable job holding just the names of our artists' albums, but it's not great for storing structured information about those albums. We're going to convert this field into a linked record field, and, in doing so, create a new Albums table that's linked to our existing Artists table.
When Airtable is making a conversion from a text field to a linked record field, it looks at whether there are any commas in a cell in order to determine whether or not there are any multiple values. Before doing any conversions, make sure that album names are separated by commas. If there are any album names with commas in them, be sure to wrap those album names in double quotation marks.
Next, go to the field customization menu and select the Customize field type option. Then choose Link to another record. Since we want to create a new table, select the + Create a new table option.
The customization dialog will give you the option to pick a name for the new table. By default, the new table name will be the name of the field you're converting ("Albums," in our case), but you could choose to give your new table a different name if you'd like.
Once you're ready, click the Save button. This not only converts the values in the field into links, it also creates a new Albums table!
Click on the newly appeared Albums table to take a look at your new table. It has two fields: the primary field, which will be a single line text field with the names of the newly created linked records, and a linked record field which links back to the artists from the original Artists table. Notice that by creating the linked record field in the Artists table, you actually also created a second, reciprocal linked record field in the Albums table.
You can customize your table a little further to make it more compelling. Try putting in a date field to store release dates, a number field to store peak chart position, or whatever other fields you'd like.
Method #2: Link two existing tables
As an alternative to using an existing field to make a completely new linked table, you also can build out a new table first and then link the new table together with the old table. In this particular example, it's a bit more work than Method #1, but in other situations, this method might be easier (for example, if you're importing multiple CSVs into different tables and you later want to go back and link them together).
To make a new table, click on the + button next to the Artists tab. You'll see two options: Create empty table, or Import a Spreadsheet. Let's pick Create empty table.
This will create a new table, which will be called "Table 2" by default. Go ahead and rename this table "Albums."
Now, we're going to build out this new Albums table. First, you'll need to fill in the names of the different albums in the primary field. If you'd like, you can also make and customize a couple of other fields to give your table some more color—try putting in a date field to store release dates, a number field to store peak chart position, and an attachment field to store album art. Essentially, what you're trying to do is create a table with a primary field that corresponds with the values in a non-primary field in another table. (Note that you don't actually need to make a field with artist names yet—that'll happen automatically later!)
Return to the Artists table. You should have set up a single line text Albums field way back when you were setting up all of the fields; if not, you can go ahead and make one now. We're going to convert this field into a linked record field that's linked to the Albums table we just created.
When Airtable is making a conversion from a text field to a linked record field, it looks at whether there are any commas in a cell in order to determine whether or not there are any multiple values. Before doing any conversions, make sure that album names are separated by commas. If there are any album names with commas in them, be sure to wrap those album names in double quotation marks.
Once you're ready, go to the field customization menu and select the Customize field type option. Then choose Link to another record, and select the table you'd like to link records from. In our case, we want to pick the Albums table.
Once you've selected the Albums table, click the Save button. You'll see a confirmation dialog alerting you that converting from single line text to single select could potentially clear some cell data; go ahead and click the Convert button. You'll notice that the Albums field in your Artists table has turned into a linked record field, and the album names in the cells have turned into blue link tokens.
Now go to the Albums table, and you'll see that by creating the linked record field in the Artists table, you actually also created a second, reciprocal linked record field in the Albums table.
As mentioned before, you can click on linked record tokens to open the expanded record version of the record being linked to—making it easy to quickly reference relevant information from another table. You can also choose to hide the linked record field on one table without affecting the other table.
Now that you've successfully linked your artists to their albums and albums to their artists, this opens up some cool new functionalities for your base. Linked record fields allow you to use three special computed field types: lookup, count, and rollup to surface and aggregate data across tables.