Notion VIP Logo
BulletproofA-to-ZConsultingThe Streamline
0
Subscribe

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:

Formula operations require particular data types for their inputs:

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.

Notion Type Mismatch

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.

Notion Gallery with 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.

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.

Notion Checkboxes to Numbers

The keys to the formula are:

divide(
	toNumber(prop("Req. 1")) 
		+ toNumber(prop("Req. 2")) 
		+ toNumber(prop("Req. 3")),
	3
)

However, that returns an egregious decimal. Thus, we need to:

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:

Notion Type Mismatch

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")
Notion Calculation: Days Late

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")
Notion Database: Calculate Balance
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.

All-in on
the all-in-one
productivity app.
Subscribe →