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

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

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.

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