Notion Stakeholder Series — Frequent Functions

I help important Notion stakeholders understand the app’s advanced features. After exploring Notion A-to-Z, we convene for a series of live workshops. Through this Notion Stakeholder Series, I'm making those live workshops available to you. Members of Notion A-to-Z also enjoy access to the interactive lesson, including the working demos and practical exercise. If you’ve yet to join, we’d love to have you onboard.

Here’s what you’ll learn:

• 00:00 — Intro
• 00:54 — Configure Your Sandbox
• 02:07 — Recap :: Function Fundamentals
• 10:16 — min()
• 11:30 — max()
• 12:19 — round()
• 14:03 — concat()
• 15:43 — join()
• 19:21 — now()
• 19:54 — dateBetween()
• 23:06 — dateAdd()
• 24:52 — dateSubtract()
• 25:00 — formatDate()
• 34:04 — format()
• 35:38 — toNumber()

Configure your sandbox.

As in the previous training, let’s create a page where you can practice concepts as we learn them.

Recap :: Function Fundamentals

• Functions are like packaged actions to perform on input values.
• Each function begins with a keyword, which typically indicates its operation, such as multiply.
• That keyword is followed by parentheses containing your input values, separated by commas: multiply(2, 3)
• The input values of a function are known as its “arguments.”

• Many functions require a specified quantity or data type for its arguments.

  For example, multiply() accepts two numbers. (You can’t multiply two text strings.)

• Every value in a Notion database is one of four data types:

  1. Text
  2. Number
  3. Date
  4. Boolean (true or false)

Function References

The Formula Window lists all functions in the left menu. Hover over one to reveal:

• a description;
• its argument requirements, including quantity and data type; and
• examples.

My Notion Formula Cheat Sheet offers additional helpful information about each function.

Common Number Functions

We’ve practiced the add(), multiply() and divide() functions. Let’s look at three more common functions that accept and return numbers.

min()

Given a list of numbers, min() returns the smallest.

max()

Given a list of numbers, max() returns the largest.

round()

Given a decimal, round() returns the nearest integer.

Common Text Functions

• Text functions generally manipulate text strings.
• Remember, when text strings are provided as literal values (rather than referencing other properties), they’re surrounded by double quotes: "William Nutt"

concat()

concat() merges the text strings provided as its arguments.

join()

• join() is similar to concat(), but the first argument is placed between each merged text string.
• For your Full Name, that means you can provide the space as the first argument, then your first name and last name as the second and third arguments (without the space).

Common Date Functions

Remember, dates can only be supplied as arguments in three ways:

1. Referencing a Date property
2. now()
3. fromTimestamp()

Dates cannot be entered as literal values.

now()

• now() accepts no arguments.
• Each time the database is loaded, it returns the current time.
• That makes it useful for determining the amount of time since or until another date, as we’ll see in upcoming examples.

dateBetween()

• dateBetween() returns the amount of time between two dates (or times).
• The first two arguments are the dates, typically the later date followed by the earlier one.
• The third argument is the unit to used for the returned duration. It’s written as a string (between double quotes), in lowercase and plural form, as in "months".
• dateBetween() is typically used with now() to determine the amount of time since a passed date or until a future one.

dateAdd()

• dateAdd() adds time to a date.
• The first argument is the starting date.
• The second argument is the quantity to add.
• The third argument is the unit for that quantity, written as a text string, in lowercase and plural format, as in "days".

dateSubtract()

dateSubtrac() works like dateAdd() but subtracts the supplied quantity/unit.

formatDate()

• Notion offers few options for formatting dates.
• With formatDate(), you can return the date in almost any format.
• The first argument is the original date (typically a reference to a Date property).
• The second argument is a Moment.js format supplied as a test string (within double quotes). You can learn Moment.js formatting in this helpful cheat sheet.
• formatDate() returns a text string, meaning it cannot be used as an argument of a date function. Instead, you must reference the original Date property.

Data Type Challenges

When working with functions, you’ll frequently encounter the Type mismatch error. That’s when your inputs are incompatible data types, such as:

• adding a number to a text string; or
• concatenating a text string and a date.

It’s important to remain actively attentive to the data type of each property in a database. That includes the returned values of Formula and Rollup properties, which can be particularly confusing.

Text is always text.

• A Text property always contains a text string, even if it’s 12345 or March 19, 2020.
• Therefore, the values of Text properties cannot be used as numbers or dates, unless they’re converted.

Formulas can return any data type.

• Depending its inputs and actions, and formula can return a text string, number, date or boolean.
• It’s important to note the data type of a formula’s returned value, not just its argument requirements. This is especially true when referencing a Formula property within another Formula property.

Demo

• formatDate() returns a text string.
  • In other words, it not only reformats a date; it also converts its data type from date to text.
  • Therefore, you can’t use it an argument in a date function.
  • However, you can use the result of formatDate() with concat(), which is what makes it useful.
• In the Gallery layout, when you display a Date property, it’s unlabeled. In other words, the viewer doesn’t know what it represents.
  • With concat(), you can add a label, such as "Birthday: ".
  • You can’t use a Date property with concat() because it requires text strings.
  • But you can use the result of formatDate() with concat().
  • Below, the property Labeled Birthday concatenates "Birthday: " and the property Birthday → MM.DD.YY. (Easily analyzed in the Table view.)

Conversion Functions

format()

• format() accepts any value, of any data type, and returns it as a text string.
• This is useful for Summary properties that concatenate other properties, as well as labeled properties in Galleries, as we saw in the previous example.

toNumber()

toNumber() converts any value, of any data type, to a number.

• “4” → 4
• true → 1
• false → 0
• Dates convert to their Unix time.

Practical Exercise

For this lesson’s practical exercise, the stakeholders created a database of golfers, with properties that employ the lesson’s concepts. The full instructions and final outcome are available to members of Notion A-to-Z.