Designing linked records and dependencies

When creating a base, linked record fields can unlock all sorts of clever ways to work with the information you put in it. While linked record fields typically link two tables together, you can also use the linked record field type to link records within the same table, allowing you to do even more with that table’s records. Let’s take a look at how this type of linked record works, how you can easily set it up, and how different approaches can add new functionality to your base.

Linking records within the same table

Airtable’s linked record fields let you establish relationships between tables, allowing you to maintain a well-organized base, avoid manual data entry and perform complex calculations with simple functions. Rollup, lookup, and count fields use linked records to display their contents in unique ways.

When linking two tables together, by creating a linked record field, Airtable automatically establishes a co-dependent relationship by creating another linked record field in the second table. This relationship allows the two tables to share information freely with one another based on the records you choose to link. You can learn more about using linked record fields for this purpose in our power user’s guide.

Sometimes you’ll find yourself in a situation where you don’t want to create a relationship between tables, but rather create a table that has a relationship with its own records.

For example, let’s say you have a table to track expenses and each expense has its own record. In some cases, you may want to associate specific expenses with others. Instead of creating a second, redundant table that also holds expenses, you can use a linked record field that references records in its own table.

You can also set up linked records within the same table to represent dependencies. For example, if you have one task that you can’t complete until finishing another task, you can create a linked record dependency to establish this.

In this example, you would create a linked record field with a name like “blocked by” that links to other tasks in the same table. Any tasks added to that field would establish that those tasks need to be completed before the task that links to them.

To create a linked record field that references other records in its own table, simply create a linked record field and choose the same table as its source.

Why would I want to link records within the same table?

When designing your base, you won’t always know if it makes sense to create a second table and link to its records or link records within a single table—or if you should even use linked records in the first place. There are two main reasons you’d want to create a linked record that references records in its own table:

You want to share information between two records of the same kind.

In many cases, you may want to share information between records of different kinds. For example, a base that manages tasks might have an “Employee directory” table that contains the names of employees but also their job titles, departments, and contact information.If you want to link each task to the employees working on it, and then share information between those task and employee records, you’ll need two tables to hold these two different types of information.

However, when you want to share information between records of the same kind, you can create a linked relationship within a single table. As mentioned earlier, you’d find yourself using this method to associate expenses in the same table with one another. You could then use a lookup field to display those associated with the linked expenses, use a rollup field to calculate the sum or average of all the linked values, or a count field to simply count them all. If none of that gets the job done, you can always lean on formula fields for more options.

You want to create a dependent relationship between two records of the same kind.

Sometimes, you may want to create a link between two records of the same kind to represent a dependent relationship between them. In this case, the link you create doesn’t have the primary purpose of sharing information between records. Instead, the existence of the relationship serves as the primary purpose.

Take our earlier example of specifying tasks that block other tasks: If “Task1” blocks “Task2” you’ve created a dependency that defines what linking “Task2” means in context. The link has little to do with what “Task2” actually contains in its own record, but rather indicates through its linkage that it needs to get done in order to complete “Task1.” This type of dependency often comes in handy in project management and the creation of Gantt charts. The Gantt view in Airtable can use this kind of dependency to determine and display priority paths.

You may create linked records within the same table for one of these purposes—or for both. In the blocking-task scenario, for example, you can establish a dependency between two tasks as well as share the linked task’s information by using lookup, count, or rollup fields.

Choosing the right direction for your linked record field

There’s an important difference to keep in mind when using linked record fields within a single table: While fields that link records between two tables create a reciprocal, or bi-directional, link between the two records, fields that link within a single table only link in one direction. In other words, creating a link from one record to another in the same table will not create a corresponding link in the other direction.

When you establish this type of one-directional link, you have to think about what that link says about the records it contains. For instance, if you created a self-linking linked record field to hold tasks that block other tasks you can go about it in more than one way. If the record containing the links blocks all the task records linked to it, then it links in a forward direction and acts as a parent with linked records as its children. If that same record links to tasks that block it, however, the inverse is true and it acts as a child with linked records representing its parents.

How do you decide which direction to choose for your links? If you’re only trying to represent a dependent relationship between records, you can simply choose whichever direction feels more logical to you for your specific purposes. If you’re creating a relationship between records in the same table in order to share information between them, however, you’ll want to choose your direction carefully.

Because you can only link records within the same table in a single direction, you can also only share information in a single direction. This means that any lookup, count, or rollup fields you want to build based on this type of linked record can only function in that same direction.

Let’s return to our expenses example. We’ve created a linked record in order to share information between related expenses within the same table. Let’s say we’ve logged an expense for a new car, and later added new, separate expenses for maintenance of that car. If we want to see the total cost of maintenance reflected on the original car record, we’ll want to link to the maintenance records from the car record. This will allow us to create a rollup field that summarizes the total cost of all the maintenance records we’ve linked to our master car record.

Conversely, if we wanted to reference the original cost of the car in every successive maintenance record, we’d need to create our link in the opposite direction. In this case, we’d link to the original car record from each of the individual maintenance records. Then, we could create a lookup field to bring in the car’s total expense to each of those records.

Building a Gantt chart using dependencies

When you want to create a Gantt chart using the Gantt view, you need to create a dependency like the one we’ve discussed in previous sections and plug it into the app’s settings. This only takes a few easy steps.

First, choose the table containing the records you want to plot on your Gantt view. From there, create a self-linking linked record field and call it "Blocked by ” (or whatever name you prefer).

Second, link up your records to establish which ones block others.

Finally, create a Gantt view (if you haven’t already) and locate the Dependency field section of its settings. Choose the Blocked by field you just set up and Predecessors as the type because it contains records that block the ones they’re linked to. If you set up the field to link to projects that the parent record itself blocks, then you’d choose Successors instead.

With that, you’ve added your dependency to the Gantt view and you’ll be able to see it reflected in the arrows that connect records on your Gantt chart. Your chart will automatically update according to any changes you make in the Blocked by linked record field. You can also update the contents of your linked record field by drawing new arrows between records in your Gantt chart.

Related video content