Formula Examples (wip)
formula
Search
example
description
formula
results
linked blog post
Applying style yellow, code, and red background.
style("notionwithro", "yellow", "c", "red_background")
notionwithro
Applying style italic and purple.
style("notionwithro", "i", "purple")
notionwithro
Applying style bold, blue text color, and red background.
style("notionwithro", "b", "blue", "red_background")
notionwithro
Applying style green text colour and strike-through.
style("notionwithro", "green", "s")
notionwithro
Applying style bold and underline.
style("notionwithro", "b", "u")
notionwithro
The condition uses the .equal(…) to check if it meets the specified condition.
ifs(
prop("Priority").equal("High"), "
", prop("Priority").equal("Medium"), "", "")
-
Here, let’s say the property Priority is set to High.
-
In this example, if Priority, the Status property, equals "High", it will return "". If the "Priority" equals "Medium", it will return "". If neither conditions are met, it will return "".
", prop("Priority").equal("Medium"), "", "")
-
Here, let’s say the property Priority is set to High.
-
In this example, if Priority, the Status property, equals "High", it will return "". If the "Priority" equals "Medium", it will return "". If neither conditions are met, it will return "".condition 1 and condition 2 are false
-
If both conditions are false, the value associated with ifFalse will be returned.
ifs(false, 1, false, 2, 3)
2
condition 1 and condition 2 are true
-
If both condition 1 and condition 2 are true, the value of the preceding condition that appears first will be returned.
ifs(true, 1, true, 2, 3)
1
Uses the .empty() function on a property as a condition and returns values depending on whether the property is empty or not.
if(prop("Date").empty(), "Date is empty", "Date is not empty")
-
Suppose there is a date property called “Date.” If it is empty (true), it will return “date is empty”; if it is not checked, it will return “date is not empty”.
-
Here, assume that the Date property is not empty.
Date is not empty.
Uses a checkbox property as a condition and returns values based on its state.
if(prop("Task Complete"), "Complete", "Incomplete")
-
Suppose there is a checkbox property called “Task Complete.” If it is checked (true), it will return “Complete”; if it is not checked, it will return “Incomplete.”
-
Here, assume that the Task Complete property is marked checked.
Complete
Returns the value in ifFalse as the condition is set to true.
if(false, 1, 2)
2
Returns the value in ifTrue as the condition is set to true.
if(true, 1, 2)
1
Using the empty() function on a property.
empty(prop("example tags"))
-
In this example, the “example tag” property is a Multi-Select property with two tags assigned to it: example1 and example2.
false
Using the empty() function on [], an empty list.
empty([])
true
Using the empty() function on “”.
empty("")
true
Using the empty() function on 0.
empty(0)
true
Combining two lists of text into a single list.
concat(["a", "b"], ["c", "d"])
a,b,c,d
Combining two lists of numbers into a single list.
concat([1, 2], [3, 4])
1,2,3,4
Calculating how many hours are left (using “hours” as an unit)
dateBetween(parseDate("2024-01-01 13:00"), parseDate("2024-01-01 12:00"), "hours")
-
In this example, the dateBetween() function calculates the hourly difference between January 1, 2024 13:00 and January 1, 2024 12:00.
Calculating how many days are left (using “days” as an unit)
dateBetween(today(), parseDate("2024-01-01"), "days")
-
In this example, the dateBetween() function calculates the daily difference between today’s date and January 1, 2024.
Getting an item at a specific position.
at(["Apple", "Banana", "Cherry", "Date", "Elderberry"], 2)
-
In this example:
• The list consists of five fruits.
• The index is 2, indicating that the function should retrieve the third fruit (since indexing starts at zero).
Cherry
Storing multiple variable using lets()
lets(test1, 85, test2, 90, test3, 95, (test1 + test2 + test3) / 3)
-
In this case, test1, test2, and test3 are defined with values of 85, 90, and 95 respectively. The formula then calculates (test1 + test2 + test3) / 3, yielding average score of 90.
90
Storing a variable using let()
let(price, 150, price * 1.1)
-
In this example:
• The variable price is set to 150.
• The expression then calculates price * 1.1.
165
Mapping a list containing pages.
-
When using the map() function on pages, it transforms the original pages and returns a new list based on the specified expression.
map(prop("Tasks"), current.prop("Date"))
-
Let’s say there’s a relation property named Tasks containing three items: [Item 1, Item 2, Item 3]. Each item has a date property called Date:
- Item 1 with Date Oct 7, 24
- Item 2 with Date Oct 17, 24
- Item 3 with Date Oct 29, 24
The expression current.prop("Created Time") extracts Date of each page and results in a list containing Dates. (Also, notice how this is similar to Rollup!)
,,,,,
Mapping a list containing numbers.
-
When using the map() function on numbers, it transforms the original list and returns a new list based on the specified expression.
map([100, 150, 200], current * 1.1)
-
Suppose you have a list of prices for different items and want to add a 10% sales tax to each price.
-
The original list consists of prices [100, 150, 200]. The expression current * 1.1 adds 10% to each price, resulting in a new list that contains the total prices after tax.
88,165,220.00000000000003
Filtering the list containing pages
-
This will generate a new sub-list that satisfies a specific condition based on the page or its property.
filter(prop("Tasks"), current.Status.contains("Done"))
-
Let’s say there’s a relation property named Tasks containing three items: [Item 1, Item 2, Item 3]. Each item has a status property called Status:
- Item 1 with status Not Started
- Item 2 with status In Progress
- Item 3 with status Done
The filter looks for the page with the Status Done, current.Status.contains("Done"), and filters the pages that meet this condition.
Filtering list containing tags
-
This will create a new sub-list that meets a specific text condition.
filter(example tags, current.contains(1))
-
In this example, we have a multi-select property called “example tags” with 4 tags [example1, example2, example3, example11]
-
The condition identifies tags that contain 1 and filters the tags that meet this condition.
-
Note that the resulting list is returned in text format (refer to our article in the section on Notion data types to see the types supported by Notion).
example1,example11
Filtering a list containing text.
-
This will create a new sub-list that meets a specific text condition.
filter(["Apple", "Avocado", "Cherry"], current.substring(0,1).equal("A"))
-
Let’s say you have a list of text. The condition extracts the first letter using .substring(0, 1) and checks if it is equal to “A” by .equal("A"). The resulting list will include only those items that meet this criterion.
Apple,Avocado
Filtering list containing dates
-
This will create a new sub-list that meets a specific date condition.
filter([parseDate("2023-01-01"), parseDate("2023-12-31"), parseDate("2022-05-15")], current > parseDate("2023-01-02"))
-
Let’s say you have a list of dates. The condition compares each date in the list against January 2, 2023, to check if it is greater than that date. The resulting list will include only those dates that meet this criterion.
@12/31/2023,@5/15/2024
Filtering a list containing number.
-
This will create a new sub-list that meets a specific number condition.
filter([80, 150, 200], current > 100)
-
Imagine you have a list of expenses. The condition applied is current > 100, filtering the list to include only expenses above $100. The resulting list contains those expenses that meet this criterion.
150,200
Slicing a list containg text values given only startIndex.
-
A new list will be returned, starting from the startIndex all the way to the last item in the list.
slice(["Inception", "The Matrix", "Pulp Fiction", "Interstellar", "The Shawshank Redemption"], 2)
-
In this case, using the same list as in example 1, the startIndex is set to 2, meaning extraction begins at the third movie (zero-based indexing). Since the endIndex is not defined, the function will return items from the start index through to the last item in the list, which are Pulp Fiction (idx 2),Interstellar (idx 3),The Shawshank Redemption (idx 4)
Pulp Fiction,Interstellar,The Shawshank Redemption
Slicing a list containg text values given startIndex and endIndex.
-
A new list will be returned, starting from the startIndex and including items up to, but not including, the endIndex.
slice(["Inception", "The Matrix", "Pulp Fiction", "Interstellar", "The Shawshank Redemption"], 1, 3)
-
In this example, the list features the top five movies (text value).
- The startIndex is 1, meaning extraction starts at the second movie (zero-based indexing).
- The endIndex is set to 3, so the returned items will include the movies from index 1 up to, but not including, index 3, which are The Matrix (index 1) and Pulp Fiction (index 2).
The Matrix,Pulp Fiction
Sorting a list of numbers given an expression current * -1.
-
The expression is applied to each item, and then sorting occurs from smallest to largest.
sort([3, 1, 2], current * -1)
-
First, the expression is applied to each item, resulting in values like [-3, -1, -2]. When sorted from smallest to largest, this becomes [-3, -2, -1], which maps back to [3, 2, 1].
Sorting a list of pages provided with an expression.
-
This will sort the list according to the page or the property specified in the expression.
sort(prop("Tasks"), current.prop("Status"))
-
Let’s say there’s a relation property named Tasks containing three items: [Item 1, Item 2, Item 3]. Each item has a status property called Status:
- Item 1 with status Not Started
- Item 2 with status In Progress
- Item 3 with status Done
The sorting will follow alphabetical order based on the status property, resulting in: [Item 3, Item 2, Item 1]
Sorting a list containg texts.
-
Note that, sorting is case-sensitive, meaning uppercase letters will be sorted before lowercase letters.
sort(["b", "A", "B", "a"])
Sorting a list containg texts.
-
Text are sorted alphabetically.
sort(["banana", "apple", "cherry"])
Sorting a list containing dates.
-
The sort() function can also be used to sort dates. Earlier dates will appear before later dates.
sort([parseDate("2023-10-01"), parseDate("2023-09-15"), parseDate("2023-10-05")]
Sorting a list containing numbers.
-
When sorting numbers, the sort() function will organize them from smallest to largest.
sort([3, 1, 2])