The ability to establish a relational data model through interrelated databases is among Notion’s most powerful features. With the
Relation property, you can connect items in one database to items in another database. For example, you can link contacts to their companies, projects to their tasks, and recipes to their ingredients.
In most cases, you can consider items in Database A as “groups” for Database B: companies group contacts, projects group tasks, and recipes group ingredients. In some cases, the “grouping” may work reciprocally: an ingredient may be part of multiple recipes, in which case you can “group” recipes by ingredient. That creates powerful opportunities for the organization and filtering of databases within your workspace.
Relating databases presents a world of capabilities. Here are a few:
- Create “global” categories, tags and other classifications, which you can use across databases in place of independent
Selectproperties. For example, you may have a Clients database, where each client has associated items in your Projects, Resources and Contacts databases. With the
Relationproperty, you can link each project, resource and contact to its associated client rather than creating an independent
Selectproperty within each database. Should you modify the client name, all related databases reflect the change.
- Automatically filter “child” databases. Within its inner page contents, each client may have filtered views of its associated projects, resources and contacts. You an also display a project’s tasks from a master Tasks database. Using database templates, you can automatically display “child” items within their “parents.”
- Aggregate and analyze information for related items. The
Rollupproperty allows you to combine information for “grouped” items. For all tasks within a project, for example, you can form a list of due dates. With that list, you can calculate the next deadline, the final deadline, and more.
- Summarize related items. The classic example: expense tracking. By linking each expense to a category, you can use a
Rollupproperty to calculate the total spent for each category. You can do the same for vendors.
- “Look up” properties for related items. With clients linked to projects, and projects linked to tasks, you can associate a client with a task by identifying the project’s client. By the same token, you can automatically add categories to expenses based on the vendor.
- Calculate progress from completed “children.” With a
Rollupproperty, you can display the percentage of child items checked, such as tasks completed within a project.
- Create reciprocal groups. Recipes are linked to multiple ingredients, and ingredients are linked to multiple recipes. Within each recipe, you can display its ingredients. For each ingredient, you can identify the recipes that contain it.
By introducing such useful capabilities, related databases ensure accurate, consistent information while avoiding superfluous, redundant work.
This guide explores many of the powerful capabilities of
Rollup properties, with detailed implementation instructions, through practical examples.
Here’s what you’ll learn:
- Clients, Projects, Tasks & Resources
- Relate clients to projects and resources.
- Configure reciprocal properties.
- Display related projects and resources within a client.
- Automate it with a template.
- Add tasks to projects.
- Calculate progress.
- Expenses, Categories, Vendors & Dates
Clients, Projects, Tasks & Resources
Take two databases based on popular productivity methodologies, including Bulletproof, and commonly used in Notion workspaces: Projects and Resources. In many workplaces, those projects and resources will align with a client.
Without relations, you might create a
Select property for each database, each with an option for each client. As clients evolve, you’d need to adjust the options for each of those
Alternatively, by relating the Projects ad Resources databases to a centralized Clients database, you foster efficiency, accuracy and consistency while introducing an abundance of new capabilities.
Relate clients to projects and resources.
With existing Projects and Resources databases:
- Create your central Clients database as the “parent” or “group” database. Your clients could also be stored within a broader Companies or Areas database (another methodological concept).
- For each “child” database (Projects and Resources), add a new property. Name it “Client” and choose
- When prompted, choose the Clients database.
The relation is in place. For any project or resource, you can click into the new Client property and choose an item from the Clients database. Those added clients serve as links to their pages within the Clients database.
Configure reciprocal properties.
When you add a
Relation property to a child database, a reciprocal
Relation property appears in the parent database. As you choose a parent for each child, the children appear in that reciprocal property.
In this example, the Clients database will receive two reciprocal
Relation properties: one for Projects, a second for Resources. The default name of these properties (“Related to…”) is undesirable, so you’ll likely want to rename them to match their respective related databases (“Projects” and “Resources”).
As you select clients for your projects and resources, those projects and resources will appear in their corresponding
Relation properties within the Clients database. You can also add the projects and resources from their respective properties within the Clients database.
Display related projects and resources within a client.
Among the foremost uses of these parent-child relationships is the presentation of child items within parent items. In other words, when you open each client as a page, you can see its related projects and resources:
- Open a client that has related contacts and projects.
- Insert a Linked Database; choose Projects.
+ Add a viewto create a new view. Choose a preferred format; Gallery, Board and List formats work well. Name the view whatever you like.
- Filter the view to show only the projects linked to the current client: Click
+ Add a filter. Configure it to show items where the Client property “Contains” the current client.
Repeat the above steps for the Resources database, then you’ve nicely displayed only the projects and resources related to the current client.
Automate it with a template.
Rather than taking the above steps for each new client, you can establish a “New Client” template to display related projects and resources automatically:
- Within the
Newbutton at the top of your Clients database, click the arrow and choose
+ New template.
- Name it “New Client.”
- Within the page, create and format Linked Databases just as you did for the above client. The only difference: for the client within the filter, choose “New Client,” which is the current template (it should be the uppermost option).
For each new client created with the template, the linked Projects and Resources databases will be automatically filtered for the new client. This is known as a “self-referencing filter.” When you create new projects and resources from those Linked Databases, they’re relations to the client will be automatically configured.
Add tasks to projects.
Projects are typically completed through a series of tasks. In Notion, there are many advantages to keeping master Projects and Tasks database. By relating each task to its project, you can display each project’s tasks within its inner page, just as you configured each client to display its projects. You can then create a “New Project” template that contains an automatically filtered view of the Tasks database.
- Create your Tasks database.
- Within you Projects database, add a
Relationproperty and choose the Tasks database.
- Rename the property and its reciprocal within the Tasks database.
- For each task, choose its associated project.
- Create a new template within your Projects database. Name it “New Project.”
- Add a Linked Database within the page contents. Choose the Tasks database.
- Create a view. Lists generally work best for tasks.
- Filter the view to show only the tasks whose Project property “Contains” “New Project.”
Each new project you create with the template will automatically include a list of that project’s tasks. When you add tasks through that view, they’ll be automatically related to that project.
Rollup property allows you to aggregate and operate on linked items and their properties. In the classic example, expense categories can be linked to individual expenses. With a
Rollup property, you can total the values of a category’s associated expenses. We’ll do just that with the next set of databases, but first, we’ll apply
Rollups to our project-task relations.
Tasks managed in Notion typically include a
Checkbox property to denote completion. The
Rollup property allows you to calculate the percentage of checked values among linked items. Therefore, you can display a project’s progress by calculating its completed tasks:
- Building on your previous configurations, check a handful of tasks within one of your projects.
- Within the Projects database, add a
Rollupproperty. Name it “Progress.”
- Click into any cell within the Progress property to configure the
Rollup. For the
Relation, choose the Tasks property. For the
Property, choose Complete — or whatever you named the
Your progress is displayed.
Expenses, Categories, Vendors & Dates
Calculate Total Spending by Category
On to that classic use of
Rollup: totaling expenses within their categories.
- Start with Expenses and Expense Categories databases.
- Link them with a
Relationproperty. In Expense Categories, rename the property “Expenses.” In Expenses, rename the reciprocal property “Expense Categories.”
- Within the Expenses database, select a category for each expense.
- Within the Expense Categories database, add a
Rollupproperty named “Total Spent.”
- Click into any cell within Total Spent to configure the
Rollupproperty. For the
Relation, select the Expenses property. For the
Property, select the property containing the amount spent, such as Cost. For
For each expense category, you’ll see the sum of all expenses.
Calculate Total Spending by Vendor
For more granular insights, you can relate the Expenses database to a Vendors database. This ensures consistent vendor entry and allows you to find totals, averages and other calculations by vendor.
- For the sake of this exercise, clear the
Relationsfrom your Expense Categories and Expenses database.
- Add a Vendors database.
Relationproperties, link Vendors with both Expense Categories and Expenses. Rename those properties, as well as their reciprocal properties, appropriately.
- Within Vendors, select a category for each vendor.
- Within Expenses, select a vendor for each expense.
- Back in Vendors, add a
Rollupproperty. For the
Relation, select the property linked to Expenses, probably “Expenses.” For the
Property, select the property containing the amount spent, such as “Cost.” For
You can now view total spending by vendor. From there, you can configure category totals by “rolling up” vendors in the Expense Categories database.
Automate Expense Categories
By linking categories to vendors, and vendors to expenses, you can automatically populate the category for each expense.
- Within Expenses, add a
Rollupproperty. For the
Relation, select the Vendor property. For the
Property, select Category. For
The category will automatically populate for each expense based on its vendor.
Calculate Average Spending by Month
By linking your Expense database to a Months database, you can determine your average monthly spend:
- Create a Months database, and populate it through the rest of the year using this format: “January 2020.”
- Use a
Relationproperty to relate the Expenses and Months databases. Name the properties appropriately.
- Choose a month for each expense.
- In the Months database, add a
Rollupproperty named “Total Spend.” For the
Relation, select the Expenses (
Relation) property. For the
Property, select Cost. For
- At the bottom of the Months database, below the Total Spend property, hover your cursor over the
Calculateoption and choose
Questions? Tweet @WilliamNutt.