This guide details the development of our Relations & Rollups template. Dissect it for a better understanding of Notion, or simply adapt its contents for your own needs.
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:
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.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.Rollup property to calculate the total spent for each category. You can do the same for vendors.Rollup property, you can display the percentage of child items checked, such as tasks completed within a project.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.
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.
With existing Projects and Resources databases:
Relation as the Property Type.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.
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.
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:
+ 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, 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.
Rather than taking the above steps for each new client, you can establish a "New Client" template to display related projects and resources automatically:
New button at the top of your Clients database, click the arrow and choose + New template.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.
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.
Relation property and choose the Tasks database.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.
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:
Rollup property. Name it "Progress."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.
On to that classic use of Rollup: totaling expenses within their categories.
Relation property. In Expense Categories, rename the property "Expenses." In Expenses, rename the reciprocal property "Expense Categories."Rollup property named "Total Spent."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.
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.
Relations from your Expense Categories and Expenses database.Relation properties, link Vendors with both Expense Categories and Expenses. Rename those properties, as well as their reciprocal properties, appropriately.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.
By linking categories to vendors, and vendors to expenses, you can automatically populate the category for each expense.
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.
By linking your Expense database to a Months database, you can determine your average monthly spend:
Relation property to relate the Expenses and Months databases. Name the properties appropriately.Rollup property named "Total Spend." For the Relation, select the Expenses (Relation) property. For the Property, select Cost. For Calculate, choose Sum.Calculate option and choose Average.Questions? Tweet @WilliamNutt.