With Airtable, you can create relationships between individual records. Building on our Martian example, we can connect our Mars supplies with the vendors that manufactured them. This will display the name of the vendor in-line and make it easy to pull up the vendor’s information anytime. That’s referred to as “linking tables.”
After we’ve created an association between tables, we can use that to pull information, count records, or calculate values from the other table. For instance, on a vendors table, we could show the products that each produces and the amount owed to each vendor.
Linked records in action
First, we’ll show you what linked records look like and then show you how to create them!
Below, you can see a screenshot of NASA’s Vendors table with a linked field that connects to the supplies table.
We can click on one of the linked records in this linked field to expand it. In this case, ‘Space suits’ is a record in the Supplies table, and we can click on it to expand its record to see all of its details, as well as the record activity and any comments on the right-hand side.
When you link tables, a new linked record field will appear in both tables automatically. You can add as many linked record fields as you want. You can even link to records in the same table.
Tip: How to choose between adding a column or linking to a new table
Create a column when you want to add a single attribute to a record (e.g., color, location, material).
Create a linked table if you need more information about an attribute than will fit in a single column. For example, moving vendors into a new table would let you capture vendor contact information separately and prevent you from entering the same company information repeatedly in the supplies table.
To create a linked record, you can use either one table to create a second table or link matching values from two existing tables. See the step-by-step instructions below.
Linking to a new table
Linking two existing tables
Step by step instructions
First, you’ll need to structure your data so that the text you want to link is in the first column of one of your tables. In the other table, double-click the name of the column and change the column type to Link to another record. Select the name of the table that you want to link.
Note that Airtable treats commas as separators. If your record names contain commas, you’ll need to wrap them as quotes. For example, without quotation marks, Galactic Greenhouses, Inc. would be two separate entries – “Galactic Greenhouses” and “Inc.”
Lookups between tables
The lookup field lets you pull in a specific field from a linked table. For example, every company in our vendors table has an address. If we wanted to show vendor addresses on the supplies table, we could do that by using a lookup.
To configure a lookup, you’ll first need to link the tables. Next, you’ll need to pick the column with the linked record that you want to reference. Then choose the column from the other table that you want to appear.
Counting linked records
The count field calculates the number of linked records in a column. To set it up, just choose the column with the linked records that you want to count. In the screenshot below, we use the count field to show the number of supplies that each vendor produces.
A rollup field is like a lookup field, except it can perform a calculation on a given field. Let’s say we’ve already linked our Mars supplies table with our vendors table. We could use rollups to add up the costs of all supplies and display the total due to each vendor.
To configure a rollup, first select the linked record field for your calculations. Next, select the column in the other table and then choose a function. Average, sum, min and max are a few of the aggregation functions you can use on a rollup. For all the rollup aggregation functions, see our Rollup Field Reference.
Next: Collaborating with a team