Formulas in Database Filters

Learn how to use Notion formulas within 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

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.

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

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.

Formulas that output strings are treated like text properties.

Options include:

  • Is
  • Is not
  • Contains
  • Does not contain
  • Starts with
  • Ends with
  • Is empty
  • Is not empty

Formulas that output numbers are treated like number properties.

Options include:

  • = (equal)
  • ≠ (not equal)
  • > (greater than)
  • < (less than)
  • ≥ (greater than or equal to)
  • ≤ (less than or equal to)
  • Is empty
  • Is not empty

Formulas that output dates are treated like date properties.

Options include:

  • Comparisons:
    • Is
    • Is before
    • Is after
    • Is on or before
    • Is on or after
    • Is within
    • Is empty
    • Is not empty
  • Dates:
    • Today
    • Tomorrow
    • Yesterday
    • One week ago
    • One week from now
    • One month ago
    • One month from now
    • Custom date

Formulas that output Boolean/Checkbox values are treated like checkbox properties.

Options include:

  • Comparisons:
    • Is
    • Is not
  • Choices:
    • Checked
    • Unchecked

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.

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.

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.

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

// Property name: Timestamp timestamp(now())
  • Valid: Timestamp > 0
  • Invalid: Timestamp = 0
// Property name: Truth if( 10 > 5, true, false)
  • Valid: Truth is checked
  • Invalid: Truth is unchecked
// Property name: One More Day dateAdd(now(), 1, "days")
  • Valid: One More Day is Tomorrow
  • Invalid: One More Day is Today

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.

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.

About the Author

My name is Thomas Frank, and I'm a Notion-certified writer, YouTuber, and template creator. I've been using Notion since 2018 to organize my personal life and to run my business and YouTube channel. In addition to this formula reference, I've created a free Notion course for beginners and several productivity-focused Notion templates. If you'd like to connect, follow me on Twitter.

🤔 Have an UB Question?

Fill out the form below and I’ll answer as soon as I can! ~Thomas

🤔 Have a Question?

Fill out the form below and I’ll answer as soon as I can! ~Thomas