Notion Formulas 2.0: A Concise Overview

Technology

Nov 3

It has been a few months since the official release of Notion Formulas 2.0. This is what Formulas 1.0 looked like:

screenshot of Notion formulas 1.0 editor — A screenshot of the Notion Formulas 1.0 editor containing a formula to count the number of days between dates

And this is what Notion Formulas 2.0 looks like:

A screenshot of the Notion Formulas 2.0 editor containing a formula to count the number of days between dates

The formula editor itself has mostly stayed the same. However, the user experience of writing formulas in Notion has improved thanks to several new features. These include the introduction of tokens that make formulas more readable, the inclusion of line breaks within the formula editor, the ability to add comments in the formula editor, and the addition of new functions that allow for more versatile data manipulation. For instance, users can now work with arrays (also known as lists) using the map() function, and they can define reusable variables using the lets() function.

Currently, the best course for learning Notion Formulas 2.0 in-depth, at various levels of abstraction, is Formulas Fundamentals 2.0 by Notion Mastery. This course covers every detail of Notion Formulas 2.0 and also provides theoretical and higher-level explanations of programming concepts. These explanations are beneficial for understanding Notion Formulas 2.0 from first principles.

Many of the actions available in Notion Formulas 1.0 are still possible with Notion Formulas 2.0. In addition, you can now create advanced formulas that can go multiple levels deep into relational properties through Formulas 2.0. This is made possible by the new language engine, which provides specific data types for formula outputs. A data type determines how the result of your formula is interpreted by the database and represents the core nature of the data. Data types include date, string, number, arrays, and more. For example, if your formula outputs a date, you can use it in a calendar or timeline view. If it outputs a number, you can display it as a percentage, integer, currency, or even as a ring or progress bar. If it outputs an array (list), you can iterate through the list and extract properties from related items. Arrays (or lists) are one of the most important innovations in Notion Formulas 2.0. Going forward, I will use the terms "arrays" and "lists" interchangeably, as "arrays" is the technical name while "lists" is the term used by Notion to define array data types in the new formulas.

This is a simple array:

[“one”, “two”, “three”]

An array is a list of items separated by a comma. The items can be strings, numbers, dates, or any data type. In the Notion ecosystem, arrays are primarily used in relational properties. Relation properties are arrays that contain one or multiple related items. Each item is clickable and contains rich information because it is stored in another database with all its associated properties. In Notion Formulas 2.0, we can iterate through relation properties to automatically retrieve property data from the related items, eliminating the need for Rollups. The video below provides an example of how to use this concept in Notion.

In general, rollups are the ideal choice when you intend to retrieve data from a directly related database and no complex calculations are required for the related items. For instance, if you want to display the progress percentage of a project based on the completed tasks, you can use a rollup to calculate the percentage based on the "Projects<>Tasks" relationship property.

When it comes to more complex calculations that involve multiple levels of relations, Formulas 2.0 offers a solution that eliminates the need for multiple rollups and extra helper properties in your databases. With Formulas 2.0, you can directly obtain the desired data output.

For example, let's say you want to display the next task in the calendar related to a project, based on the task's status (not complete) and due date (the earliest one should appear). If there is a relation between the Projects and Tasks databases, you can achieve this with just one Formula in the Projects database. The formula may look like this:

prop("Tasks")

/*filter incomplete tasks*/

.filter(current.prop("Status")!="Done")

/* Find all the due dates for each task */

.map(current.prop("Due Date"))

/* Sort them... */

.sort()

/* Look up the Task page by due date*/

.map(

/* Set a temporary "date" variable */

lets(

date,

current,

prop("Tasks").find(current.prop("Due Date") == date)

)

).first()

Let's break down the Notion Formula above. You can also read the comments in the formula for a quick understanding of what each line does.

First, we retrieve all the tasks related to the project with a status not equal to "Done". To do this, we use the following code: prop("Tasks").filter(current.prop("Status")!="Done").

After the "Tasks" array, we append the filter() function. We use the "current" variable to retrieve each task associated with the project. The filter condition is defined as "Status" not equal to (using the "≠" sign) "Done". This assumes that there is a "Status" property in the "Tasks" database and that one of the options in the property is "Done".

The output of this computation is an array of tasks. These tasks are clickable within the formula property output. To retrieve a list of Due Dates associated with the tasks, we can use .map(current.prop("Due Date")). The map() function allows us to retrieve a specific property from the list of values obtained in Step 1. We append this snippet to the previous formula for readability and convenience. The result of this mapping operation is an array of due dates for the tasks involved.

This brings us to the next step of the formula. We will use .sort() to arrange the due dates in ascending order, from the closest to the farthest one. The result will still be a list, allowing us to proceed to the final step in our multi-layered formula: obtaining a clickable task that is next on our task list for the project. To achieve this, we will once again utilize .map(). This time, we will first assign a variable to represent the earliest date in the sorted date list with lets(date, current). Finally, we will locate the task using the find() method, which retrieves the first task that matches the specified filter condition: prop("Tasks").find(current.prop("Due Date") == date).

That concludes the implementation of Notion Formula 2.0 to retrieve the next task in the pipeline based on its date and status within the project. This use case demonstrates the potential of Notion Formulas 2.0, particularly in terms of data manipulation capabilities. With this feature, you can achieve the desired outcome without relying on multiple Rollups or additional helper properties that may clutter the user experience in your Notion databases.

Thanks for reading - for any questions or feedback, let’s chat.