Relations & rollups

Brian Park
Sep 20 '18 ยท 7 min read ยท 50 views
Have you ever wanted to connect the data between two tables? You're in luck! Notion's relation property is designed to help you express useful relationships between items in different databases ๐Ÿค

What is a database relation?

Let's say you have two databases for your theoretical business ๐Ÿ˜‰
  • One that tracks customers
  • One that tracks items purchased
You want to know both which customers bought which items, as well as which items were purchased by which customers. This is a job for relations!
  • In the two tables below, the columns labeled โ†— Items and โ†— Customers are relation properties, which can be added like any other database property.
  • Here, when you add an item bought into the Customers database, the customers who bought them automatically appear in the โ†— Customers column in the Items database.

Customers

NameItems purchased
Abigail Africa  
Ivan Zhao  
Camille Ricketts  
Cory Etzkorn  

Items

NameSizePriceCustomers
T-shirtL$17.00 
Khaki pantsM$32.00 
Tank topS$25.00 

Example use cases

  • Connect your database of restaurants with your database of neighborhoods so you can see which restaurants are in which neighborhoods at a glance.
  • Connect your database of meeting notes with your database of clients to provide quick access to the notes relevant to each client.
  • Connect your database of tasks with your database of bigger projects to understand how projects are broken down into tasks, and how tasks contribute to projects.
  • Connect your database of candidates with your database of interviewers to keep track of who interviewed who.

Create a relation

To relate two databases, you need two databases. So let's assume you've made the Customers and Items databases above for the purpose of this walkthrough.
  • Add a new column/property to your Customers database.
  • Give it a name in the box provided, like Items Purchased.
  • Choose Relation from the Property Type menu.
  • Click the dropdown and search for the database you want to create the relation with.
  • Click the blue Create Relation button.
  • You'll see a new column pop up in the other database showing the two-way relationship. Now anything you add in either relation column will impact the other one, too.
    • Rename this column by clicking on it.
  • Now when you click in a cell in either relation column, you'll bring up a menu where you can search for and choose items from the other database to add.
    • For example, this is how you can add which clothes each customer bought.
    • You'll see the names of the customers pop up in the Items database in the related column, indicating they were the buyers.
    • To add multiple items at once, click the blue +. To delete an item, click the red X.
  • The edits work both ways. So if you add a customer to the Items database in the relation column, the change pops up in your Customers database.
๐ŸŽ’
Tip: To change the database you're connecting to, re-select Relation as the property type for that particularly property. You'll be prompted to choose a new database.

Open a related page

When you create a relation, you're essentially adding Notion pages stored in one database into the property field of another. These pages can be opened and edited like any other:
  • Click on the page in the relation column. Then click on it again in the window that pops up.
  • You can also remove any page by clicking on the red X.

Relate a database to itself

Let's say you want items in the same database to have relationships with each other. For example, you have a tasks database and you want each task to relate to other tasks. There are two ways they can be connected:
  • As related tasks
  • As sub-tasks and parent tasks
Regardless, start off by creating a new relation. Then search for and choose the database you're currently working in.

Related tasks

If you want tasks to simply be related โ€” i.e. you want to do them at the same time, or they're part of roughly the same type of work โ€” you can create a relation that only syncs one way.
  • Let's say you're buying a fish tank, buying a filter at the same time might make sense.
  • After relating your database to itself, choose Use the same property. This will cause your relation to only sync one way.

Sub-tasks & parent tasks

If you want some tasks in your database to belong to other tasks, you can create a relation that syncs both ways. For any given task, you can see which sub-tasks it includes, or which parent task it's included in.
  • Let's say you're writing a product spec (parent task) that will require you to do some user interviews and create a mock up (sub-tasks)
  • After relating your database to itself, choose Create a new property. Your relation will create two new columns syncing two ways.

Rollups

Rollups help you aggregate data in your databases based on relations. Going back to our customers and items example above, let's say you wanted to know how much each customer spent based on what they bought.
  • First, create the relation so you know who bought what (see above).
  • Add a new column/property and choose Rollup from the Property Type menu. Give it a descriptive name.
  • Clicking on any cell in the rollup column will bring up a new menu asking you for:
    • The relation property you want to roll up.
    • The property of those related pages you want to roll up.
    • The calculation you want to apply to them.
  • So, for our example, you'd choose to roll up the relation property Items Purchased and the Price property within those pages. Then you'd choose Sum as the calculation.
    • Doing this adds up the prices of each item related to a customer's name, giving you the total dollars they spent in your rollup column.

Rollup types

There are 16 different calculations you can apply as a rollup. Here are all of them:
  • Show Original: This just shows all related pages in the same cell. It's the same as the relation property itself.
  • Count All: Counts the total number of values in the selected property for all related pages.
  • Count Unique Values: Counts the number of unique values in the selected property for all related pages.
  • Count Empty: Counts the number of related pages with an empty value for the property selected. So, if one item a customer bought didn't have a price and that was the property selected, the rollup column would say 1.
  • Count Not Empty: Counts the number of related pages with assigned values for the property you selected.
  • Percent Empty: Shows the percentage of related pages with no value in the property you selected.
  • Percent Not Empty: Shows the percentage of related pages with a value in the property you selected.
These rollup calculations are only available for Number properties:
  • Sum: Computes the sum of the numeric properties for related pages (like above).
  • Average: Computes the average of the numeric properties for related pages.
  • Median: Finds the median of the numeric properties for related pages.
  • Min: Finds the lowest number in the numeric property for related pages.
  • Max: Finds the highest number in the numeric property for related pages.
  • Range: Computes the range between the highest and lowest numbers in the numeric property for related pages (Max - Min).
These rollup calculations are only available for Date properties:
  • Earliest Date: Finds the earliest date/time in the date property for all related pages.
  • Latest Date: Finds the latest date/time in the date property for all related pages.
  • Date Range: Computes the span of time between the latest and earliest dates in the date property for related pages.

Aggregate rollups

In both tables and boards, you can apply calculations to your rollup column to get a sense of sums, ranges, averages, etc. for your entire database.
  • Let's say you want to find the total money spent by all customers in our example.
  • At the bottom of your Customers table, under your rollup column, click Calculate.
  • Choose Sum to add all the values in that column together and get a grand total.
In a board, you can create relations exactly the way you would in a table, by adding a new Relation property.
  • Below, we've already created the relation (you can see the items purchased on the front of the cards as pages), and the rollup adding prices together for each customer.
  • Here's how you'd find the grand total for all customers:

FAQs

  • Can I export or import relations?
    When you export a relational database as a CSV file, the relation properties will export as plain text URLs. For now, you can't re-import that CSV back into Notion to re-establish the relations with other databases.
  • Can I duplicate a database with a relation?
    Yes, but the following will happen which can be kind of confusing:
    If you create a copy of the Customers database in the example above, you'll see a new relation property automatically appear in the Items database.
    This happens because relations sync two ways in Notion. When a database is duplicated, you also duplicate its relationships with other databases. You'll get the hang of it! ๐Ÿ˜…

Related guides

Something we didn't cover? Message us in the app by clicking ? at the bottom right on desktop (or in your sidebar on mobile). Or email us at team@makenotion.com โœŒ๏ธ
Updating...

Share on