Notion VIP: Convert Property Values for Formulas

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 string.

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:

Number
String (Text)
Bolean (true or false)
Date

Formula operations require particular data types for their inputs:

You can only calculate the sum of numbers.
You concatenate, or merge, one or more text strings.
You find the time between dates.
You compare a boolean (true or 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.

Conversion Functions

Convert values to `strings` with `format()`.

The format() function accepts as its argument a number, date or boolean, which it returns as a string. The converted value can then be concatenated, or merged, with other strings.

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 string "Age: ":

"Age: " + format(prop("Age"))

Convert values to `numbers` with `toNumber()`.

Just as format() converts a value to a string, the toNumber() function converts its sole argument to a number, which can be used for mathematical calculations.

Strings like "2" to 2.
For booleans, true and false convert to 1 and 0, respectively.
Dates convert to their Unix timestamp, or the number of milliseconds since January 1, 1970 12:00 AM (GMT) (Unix epoch).

I use toNumber() most often after extracting a number from a string with 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 Checkbox to a number;
adding those numbers; then
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 100;
round that product to an integer using the round() function; then
divide by 100.

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` Configuration

A 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:

Related Notion Databases: Transactions and Invoices

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 number:

That's because 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 Rollup.

In the case of our Payment Date, we can choose Latest date. Upon doing so, the value aligns to the right, as dates do.

It also assumes "relative" formatting, which I find far less useful than a traditional variation of MM/DD/YYYY)

Notion Rollup Configuration: Latest Date

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 Calculation to 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.

Convert Property Values for Formulas