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, date, list, person, or page).
  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

Information coming soon.

Information coming soon.

Information coming soon.

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

Coming soon.

Coming soon.

Coming soon.

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.

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