The Power of Relations and Rollups

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:

1. Create “global” categories, tags and other classifications, which you can use across databases in place of independent Select properties. For example, you may have a Clients database, where each client has associated items in your Projects, Resources and Contacts databases. With the Relation property, you can link each project, resource and contact to its associated client rather than creating an independent Select property within each database. Should you modify the client name, all related databases reflect the change.
2. 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.”
3. Aggregate and analyze information for related items. The Rollup property 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.
4. Summarize related items. The classic example: expense tracking. By linking each expense to a category, you can use a Rollup property to calculate the total spent for each category. You can do the same for vendors.
5. “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.
6. Calculate progress from completed “children.” With a Rollup property, you can display the percentage of child items checked, such as tasks completed within a project.
7. 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 Relation and Rollup properties, with detailed implementation instructions, through practical examples.

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 Select properties.

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 Relation as the Property Type.
• 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.
• Click + Add a view to 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 Filter, then + 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 New button 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 Relation property 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.

Calculate progress.

The 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 Rollup property. 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 Checkbox property. For Calculate choose Percent checked.

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 Relation property. 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 Rollup property named “Total Spent.”
• Click into any cell within Total Spent to configure the Rollup property. For the Relation, select the Expenses property. For the Property, select the property containing the amount spent, such as Cost. For Calculate, choose Sum.

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 Relations from your Expense Categories and Expenses database.
• Add a Vendors database.
• Using Relation properties, 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 Rollup property. 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 Calculate, choose Sum.

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 Rollup property. For the Relation, select the Vendor property. For the Property, select Category. For Calculate, choose Show Original.

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 Relation property to relate the Expenses and Months databases. Name the properties appropriately.
• Choose a month for each expense.
• In the Months database, add a Rollup property named “Total Spend.” For the Relation, select the Expenses (Relation) property. For the Property, select Cost. For Calculate, choose Sum.
• At the bottom of the Months database, below the Total Spend property, hover your cursor over the Calculate option and choose Average.

Questions? Tweet @WilliamNutt.