Notion’s Formula property offers immense utility, allowing you to manipulate other properties in a variety of useful ways. Formulas can be as simple as
2 + 2 (literally) or elaborate compilations of nested functions.
The examples below are widely useful yet easy to implement for users at all levels. To demonstrate them, we use two sample databases: a basketball team roster and a list of ecommerce orders. Feel free to duplicate them for your own practice.
If you’re new to formulas, consider starting with Meet Notion’s Formula Property for a fundamental understanding of how they work.
What You’ll Learn
We’ll begin with a database of players who comprise a basketball team.
Side note: If you’re a hoops fan, you’ll appreciate The NBA — in Notion.
Merge First and Last Names
Given each player’s First Name (
text) and Last Name (
text), we can create a
Formula property that combines them into a Full Name using the
prop( "First Name" ) + " " + prop( "Last Name" )
Given each player’s Birthday (
date), we can determine their ages with the
dateBetween( now(), prop( "Birthday" ), "years" )
Add Context to Gallery Cards
When we display the roster as a Gallery, some properties stand alone ambiguously without context, such as Age. Using the
concat() function, we can add a label to those properties. When we do so, we also need to convert Age (
text using the
concat( "Age: ", format( prop( "Age" ) ) )
concat( "School: ", prop( "School" ) )
The next set of examples augments a database of orders from an online business. The database contains an item for each unique Order-SKU pairing. For example, Order #1 contains two products (P18 and P21), and therefore has two items in the database: 1-P18 and 1-P21.
Generate a Unique ID
Databases often need a unique ID for each item. Given the Order # (
number) and Product SKU (
text), we can generate a unique ID, which we can easily paste into the ID (
title) property. Because the Order # is a
number, we use the
format() function to convert it to a
string so it can be merged with the Product SKU (
text) using the concatenation operator (
format( prop( "Order #" ) ) + "-" + prop( "Product SKU" )
Markup calculations would typically occur in an independent Products database, but for simplicity’s sake, we’ve included them here.
Say we want our Markup to correlate with the Cost of Goods (
- Where Cost of Goods is less than $10, Markup is 25%
- Where Cost of Goods is $10 or more, but less than $50, Markup is 20%
- Where Cost of Goods is $50 or more, Markup is 15%
We can achieve this with nested
if( prop( "Cost of Goods" ) <= 10, .25, if( prop( "Cost of Goods" ) < 50, .2, .15) )
Calculate Customer Price
With the Markup determined, we can then calculate Customer Price by multiplying the Cost of Goods (
number) by the Markup (
number), then adding the Cost of Goods (
prop( "Cost of Goods" ) * prop( "Markup" ) + prop( "Cost of Goods" )
We then want to calculate tax by multiplying the Customer Price (
number) by the Tax Rate (
prop( "Customer Price" ) * prop( "Tax Rate" )
Calculate Total Cost
Our store is part of the free-shipping movement, so we can calculate the Total Cost by adding the Customer Price and Tax:
add( prop( "Customer Price" ), prop( "Tax" ) )
Show Days Since Order
For tracking the fulfillment process, we can calculate the Days Since Order using the Order Date (
now() function within the
dateBetween( now(), prop( "Order Date" ), "days" )
Show Fulfillment Progress
With five different stages of processing each order, we can create a field that nicely indicates Progress. It includes a series of nested
if() functions to define an output for each Fulfillment Stage:
if(prop("Fulfillment Stage") == "Payment Processing", "•◦◦◦◦ 20%", if(prop("Fulfillment Stage") == "Payment Cleared", "••◦◦◦ 40%", if(prop("Fulfillment Stage") == "Packaged", "•••◦◦ 60%", if(prop("Fulfillment Stage") == "Shipped", "••••◦ 80%", if(prop("Fulfillment Stage") == "Delivered", "••••• 100%", "")))))
With properties for Days Since Order (
number) and Progress (
text), we can establish a field that displays a special character for orders that remain unshipped after a week:
if( and( contains( prop( "Progress" ), "◦◦" ), prop( "Days Since Order" ) >= 7 ), "?", "" )
The formula tests if the Progress property contains two or more empty circles and the Days Since Order is seven or more days. If so, it displays an emergency emoji.
We’ve also sorted the table by this Attention property to amplify the urgency.