This is a free version of a lesson from Notion A-to-Z. For all lessons in an intuitive sequence, along with functional demos, practical exercises and certification prep, join this unrivaled learning experience.
Before converting property values, you’ll want to be familiar with the fundamentals of formulas.
The operations your perform with formulas require particular data types. To accommodate these requirements, you’ll often need to convert properties from one data type to another. For example, you’re unable to merge a
date with a text
string to form a phrase like
"Birthday: January 14, 1987". The
date must first become a
Rollups can be particularly confusing because their data types can differ from the properties they retrieve.
In this lesson, we’ll explore how to convert data types and configure
Rollups for use in formulas.
Data Type Refresher
As I cover in Formula Fundamentals, every property value in a Notion database is one of four data types:
Formula operations require particular data types for their inputs:
- You can only calculate the sum of
- You concatenate, or merge, one or more text
- You find the time between
- You compare a boolean (
false) with another boolean.
Therefore, when you reference other properties as inputs for your formula, you need to remain mindful of their data types.
If you attempt an operation on incompatible data types, Notion will will throw a
type mismatch error, which typically means you need to convert the data type of one or more of the input values.
Convert values to
format() function accepts as its argument a
boolean, which it returns as a
string. The converted value can then be concatenated, or merged, with other
Demo: Contextualize Gallery Properties
I often use
format() when adding context to properties in the
Gallery format. In the example below, each card includes the term
"Age: " before the person’s age, which would otherwise be a standalone number out of context.
To achieve this, we create a new
Formula property called “Age: Contextualized.” In the formula, we reference the Age property within the
format() function, and prepend that with the
"Age: " + format(prop("Age"))
Convert values to
format() converts a value to a
toNumber() function converts its sole argument to a
number, which can be used for mathematical calculations.
Datesconvert to their Unix timestamp, or the number of milliseconds since January 1, 1970 12:00 AM (GMT) (Unix epoch).
toNumber() most often after extracting a number from a
replaceAll(), which you’ll learn in other lessons.
Demo: Total Checkboxes
Another useful example is calculating progress from checked
Checkbox properties. The example below imagines a set of requirements, where Progress calculates the percent checked.
The keys to the formula are:
- converting each
- adding those
- dividing that sum by
3(the number of checkboxes).
divide( toNumber(prop("Req. 1")) + toNumber(prop("Req. 2")) + toNumber(prop("Req. 3")), 3 )
However, that returns an egregious decimal. Thus, we need to:
- multiply by
- round that product to an integer using the
- divide by
divide( round( multiply( divide( toNumber(prop("Req. 1")) + toNumber(prop("Req. 2")) + toNumber(prop("Req. 3")), 3 ), 100 ) ), 100 )
Of course, this can be accomplished more simply by using arithmetic operators in place of functions:
round(((toNumber(prop("Req. 1")) + toNumber(prop("Req. 2")) + toNumber(prop("Req. 3"))) / 3) * 100) / 100
Rollup property retrieves a specified property from related items. Typically, those items are in another database.
Consider Transactions and Invoices databases, for example, where each invoice relates to its payments.
Rollup properties in the Invoices database can retrieve the Date and Money In values from the corresponding transactions to populate Payment Date and Total Paid:
Formula properties can then reference Payment Date and Total Paid to calculate Days Late and Balance. When we compose these formulas, however, we Notion throws a
type mismatch, reporting that Payment Date is not a
date and Total Paid is not a
Rollups, by default, are
strings, regardless of the property type they retrieve.
To convert the value to the original data type, Notion requires you to choose a
Calculation other than
Show original when configuring your
In the case of our Payment Date, we can choose
Latest date. Upon doing so, the value aligns to the right, as
It also assumes “relative” formatting, which I find far less useful than a traditional variation of
We can then add our Days Late formula, which utilizes
dateBetween() from Essential Date Functions:
dateBetween(prop("Payment Date"), prop("Due Date"), "days")
For Total Paid, we can change the
Sum, which right-aligns the values, thus indicating
numbers. That allows us to subtract Total Paid from Amount Due to calculate Balance:
prop("Amount Due") - prop("Total Paid")
The “Unique Values” Caveat
As of this writing, an unintended behavior persists in
Rollups: If you choose
Show unique values as your
calculation, then reference the
Rollup in a formula, the input value will be the count of unique values, not the values themselves.
If you hit any snags as you convert property values, feel free to tweet @WilliamNutt.