Databases
Relations and rollups
Pro A Relation column links each row to rows of another database, like tasks to their project or invoices to their client. A Rollup column then summarizes those linked rows: how many there are, or the sum, average, minimum or maximum of one of their numbers.
Both column types need the Pro or Premium plan, yours and the workspace's (your effective plan). Existing relations and rollups keep working if the plan drops, but you can't create new ones.
Create a relation
You need two databases in the same workspace: the one where you add the column, and the one it links to.
- In the Table view, add a column (Add column) or pick an existing one.
- Change its type to Relation.
- Under the header, open Choose a database… and pick the other database. The list shows every other database in the workspace, by name, so give your databases clear names.
A relation can only point to a database in the same workspace, and not to its own database.
Link rows
- Click a cell in the Relation column. A list opens with every row of the other database, shown by its title (the first Text column of that database).
- Tick the rows you want to link. You can link as many as you like.
- Untick a row to unlink it. Changes are saved at once.
The cell shows the linked rows as chips. If the other database has no rows yet, the list says "The related database has no rows."
Relations are one-way: linking a task to a project from the Tasks database doesn't add anything to the Projects database. If you want to see the link from both sides, add a Relation column in each database and tick the rows in both.
Set up a rollup
A Rollup column lives in the same database as the Relation column and reads the rows it links to.
- Add a column and change its type to Rollup.
- In Relation column…, choose the Relation column to follow.
- In Target column…, choose the column of the other database to calculate with. Only its Number columns are listed.
- Choose the aggregation:
| Aggregation | Result |
|---|---|
| Count | How many rows are linked. Doesn't need a target column. |
| Sum | The sum of the target column across the linked rows. |
| Average | Their average. |
| Min | The smallest value. |
| Max | The largest value. |
Results are rounded to two decimals. Linked rows with an empty target cell are left out of Sum, Average, Min and Max, and if none has a value the cell stays empty.
The aggregation list also includes Checked and % checked, which count ticked checkboxes in the linked rows. Some templates use them for progress; when you set up a rollup yourself, the target list only offers Number columns, so use Count or a Number column instead.
Example: hours per project
Say you have two databases in your workspace:
- Tasks, with a Text column "Task" and a Number column "Hours".
- Projects, with a Text column "Project".
To see how many tasks and hours each project has:
- In Projects, add a column called "Tasks", change its type to Relation and choose Tasks in Choose a database….
- For each project, click its "Tasks" cell and tick the tasks that belong to it.
- Add a column called "Total hours", change its type to Rollup, choose "Tasks" in Relation column…, "Hours" in Target column… and the Sum aggregation.
- Add another Rollup column called "Number of tasks" with the same relation column and the Count aggregation.
| Project | Tasks | Total hours | Number of tasks |
|---|---|---|---|
| Website redesign | Wireframes · Copy · Build | 34 | 3 |
| Spring campaign | Brief · Ads | 9.5 | 2 |
When someone adds a task or changes its hours, the totals update the next time the Projects page is opened or reloaded.
Limitations
- Same workspace only. A relation can't point to a database in another workspace, or to its own database.
- One way. Each Relation column works in one direction; to see the link from both sides, create a relation in each database.
- Reload to see changes in the other database. Linked rows and rollup results are read when the page opens. If someone edits the other database, reload the page to see the new values.
- Rollups only read Number columns (or count rows). They can't sum Formula columns or combine text.
- Formulas can't read Relation or Rollup columns, and rollups can't be filtered or sorted in views. Relation columns can be filtered with is empty / is not empty.
- The link list shows all rows, without a search box. Keep the other database's titles short and distinct.
- If you change the Relation column's target database, rows linked before stop showing. Deleting a Relation column leaves the rollups that used it empty.