Formulas in Database Filters

Database filters in Notion can target any type of property, including formula properties. However, there are some important rules to understand about how formulas and filters interact.

This page will explain those rules, and hopefully help you understand Notion’s limitations when combining filters and formulas.

Here’s the TL;DR version:

1. Filter options for formula properties depend of the data type of your formula output (string, number, Boolean/Checkbox, or date).
2. Formula properties are read-only.
3. A formula filter will prevent a new row from being inserted into current view unless the filter fits the default output that formula would already have.
4. In other words, formula filters cannot act as forcing functions. They cannot change the output of the formula. Formula output is determined solely by the formula itself and the variable data within any properties the formula references.
5. Created by, Create time, Last edited by, and Last edited time properties are initially empty. This can cause confusion when filtering by them, or by formulas that reference them, so it’s good to understand this bit of nuance.

If you’d like to learn more about how to use filters in Notion databases, check out the filter section in my databases guide:

Notion Databases: The Ultimate Beginner’s Guide

Databases are Notion’s most powerful feature. This guide will teach you everything about them – including views, filters, sorts, grouping, and more!

thomasjfrank.com

Example Database

All of the concepts explained in this article can be seen at work in this example database, which contains several unique views. Each view filters by one or more formulas.

View and Duplicate Database

Notion – The all-in-one workspace for your notes, tasks, wikis, and databases.

A new tool that blends your everyday work apps into one. It’s the all-in-one workspace for you and your team

thomasfrank.notion.site

Target a Formula with a Filter

Creating a filter that targets a formula property is no different than creating any other kind of filter.

You can create either a basic or advanced filter; either way, simply choose the formula property you want when setting up the filter:

Notion’s filter builder treats formula properties differently depending on the data type of their output.

In most aspects, a filter targeting a formula will behave exactly like a formula targeting another property type that outputs the same type of data.

However, this is one major difference: Formulas are read-only!

This has two major implications:

• You cannot create a forcing function that influences the output of a formula property.
• If your filter does not match the initial value of the formula, new rows will open directly as pages. They may also not show up in the database view that contains that filter.

The term “initial” is important in that latter point because the Created by, Created time, Edited by, and Edited time properties have a little-known quirk: Notion initially sees them as empty when a new row is created!

Likewise, any formula that references a property that has one of these types will also start off empty from Notion’s perspective.

The time/person is filled in so quickly that users never perceive this, but it does have important implications for filter design. Click here to go to the section of this article that’s about this quirk.

Formulas are Read-Only Properties

The most important thing to understand about formula properties in Notion databases is that they are read-only properties.

This means that you cannot click into a formula and directly change that formula’s output on a per-database-row basis.

In each database row, the return value of a formula property is determined solely by:

• The underlying formula – a.k.a. the “code” that you’ve entered into the formula editor
• Any properties which the formula references

Formulas can have variable results on a per-row basis only when writable properties they reference contain variable data.

Consider this formula:

// Property name: Times 2
prop("Number") * 2

This formula takes the value of the Number property and multiplies it by 2. Let’s say we have three database rows, with the following Number property values:

• 5
• 10
• 15

The formula’s output for these rows will be, respectively:

• 10
• 20
• 30

Now that you hopefully understand this concept, let’s cover the first major rule of formula/filter design.

Filters Must Match Default Formula Output for New Rows

If you’re designing a database view in which new rows do not need to be created directly, then this section isn’t important.

Example: You’re creating a “reporting” view for a database. This view simply gives you information about rows that have already been created in other views. In this case, set your filters however you want.

However, in many database views, you want to retain the ability to create new rows directly in the view.

To retain this ability, your filter critiera must match the default output that the formula would have. In other words, the filter cannot attempt to change the output of the formula.

Filters cannot act as forcing functions, applying specific values to formula properties.

Good to know: It’s worth noting that they also cannot do this for any read-only property type, including Rollup, Created by, Created Time, Last edited by, and Last edited time (additionally, they can’t set a non-empty value on a Files & Media property).

Here are a few examples of formulas (one for each data type), along with a valid and invalid filter for each. Again, this is only important in views where you want to be able to directly create new rows that stay in the view.

String Example

// Property name: Priority
"Priority: " + prop("Priority")

• Valid: Priority contains “Priority:”
• Invalid: Priority is “The Priority:”

Note how the invalid filter is looking for output that the formula doesn’t output by default.

Number Example

// Property name: Timestamp
timestamp(now())

• Valid: Timestamp > 0
• Invalid: Timestamp = 0

Boolean/Checkbox Example

// Property name: Truth
if( 10 > 5, true, false)

• Valid: Truth is checked
• Invalid: Truth is unchecked

Date Example

// Property name: One More Day
dateAdd(now(), 1, "days")

• Valid: One More Day is Tomorrow
• Invalid: One More Day is Today

Formula Cannot Change Referenced Property Values

It’s worth noting that a filter/formula combination cannot influence or change the output of a property that is referenced within that formula.

What does this mean? Let’s go back to our multiplier example. The formula there is:

// Property name: Times 2
prop("Number") * 2

Here, I cannot simply create a filter like:

• Times 2 = 30

Notion will not automatically set the Number property’s value to 15 in order to make the Times 2 formula property return 30.

For this to work, you’d need two filters:

• Number = 15
• Times 2 = 30

However, at this point you do not need the filter for Times 2. If Number is set to 15 via a forcing function, then Times 2 will be 30 by default. Therefore, its filter is redundant.

“Created Time” and Similar Properties

Notion sees the following properties types as initially empty whenever a new database row is created:

• Created by
• Created time
• Last edited by
• Last edited time

When you create a new row in a database, it looks like these values are instantly filled in. But under the hood, they start empty. They’re just filled so quickly that users don’t perceive this default empty state.

This has important implications for filter design.

For instance, let’s say you want to design a database view in for a Notes database called Today’s Notes (My Ultimate Brain template has such a view in its Quick Capture dashboard).

To accomplish this, you’d naturally think to create the following filter, targetting your Created time property:

• Created time is Today

But this doesn’t work.

You’ll quickly find out that new rows created in this video open as pages, rather than simple coming in as new rows (in table views).

However, if you create the following filter combo, it will work:

• Created time is Today OR
• Created time is empty

This should be considered for formulas that reference these properties as well.

For example, what if you wanted to create a view in your Notes database for notes created within the current hour (on any day)?

You’d probably think to create the following formula:

// Property name: This Hour
hour(prop("Created time")) == hour(now())

Then, you’d filter by:

• This Hour is Checked

But since the Created Time property is empty when it is initialized, your new page will open fully.

To fix this, make the following tweak to your formula so it accounts for the initial empty value:

// Property name: This Hour
hour(prop("Created time")) == hour(now()) or empty(prop("Created time"))

With this change, there’s no need to adjust the filter; “This Hour” will immediately output true, so the filter will allow new rows to be created directly in the database view.