Intro to formulas

Set a project’s due date as two weeks after its start date

Due date

dateAdd(Start Date, 2, "week")

dateAdd() adds time to the date. The unit argument can be one of the following: years, quarters, months, weeks, days, hours, or minutes. In this case, we’ll use weeks.

Mark a project as overdue if the due date has passed and its status is not Done.

Mark a project as overdue in bold, red letters if the due date has passed and its status is not Done.

Overdue?

if(and(now() > Due Date, Status != "Done"), "Overdue", "")

if(and(now() > Due Date, Status != "Done"), style("Overdue", "red", "b"), "")

if() returns the first value if the condition is true; otherwise, returns the second value. This allows for a conditional outcome.

and() is a logical operator. This allows to evaluate multiple factors as being true.

now() returns the current date and time. This allows to evaluate where we stand now, compared to the project due date.

> is a comparison operator. This allows to compare the condition of due date being larger than current date.

!= is a comparison operator. This allows to define the condition of status not being equal to Done.

style() adds styles and colors to the text. Valid formatting styles include b (bold), u (underline), i (italics), c (code), or s (strikethrough). Valid colors are gray, brown, orange, yellow, green, blue, purple, pink, and red. Add _background to colors to set background colors. In this case, we’ll apply bold and red.

Show the number of overdue tasks associated with a project

Tasks remaining

length(Tasks.map(current.Status != "Done"))

length() returns the length of the text or list value. This allows for a count of remaining tasks.

map() returns the list populated with the results of calling the expression on every item in the input list. This looks at the tasks database and pulls in all applicable tasks.

!= is a comparison operator. This allows to pull tasks whose current status is not Done.

Based off of the scores for Reach, Impact, Confidence, and Effort (RICE), calculate a prioritization score

Score (RICE)

Reach * Impact * Confidence / Effort

When someone clicks the upvote button, count them toward the number of total votes and add their name to the database page

Total votes

length(Upvoted by)

Every time a task is set to Done, trigger the automation

Trigger: When status set to Done

-

Define the parent task by finding the first page related to the task that triggered the automation

Action 1: Define Parent task variable

Formula: Trigger page.Parent item.first()

Trigger page references the page in the database the automation was triggered from, and . allows you to access properties associated with that page.

Parent item returns the contents from that property, which in this case is a list of related pages.

Applying the .first() function on the Parent item property returns the first related page from that list.

If the parent task has subtasks, verify whether all its subtasks have a status of Done. If the parent task has no subtasks, return false.

Action 2: Define All subtasks done? variable

Formula: Parent Task.Sub-item ? Parent Task.Sub-item.every(current.Status == "Done") : false

The ternary operator X ? Y : Z is a shorthand conditional expression that means: if the condition X is true, return Y; otherwise, return Z. It's a concise alternative to an if() statement.

.every() checks whether a condition is true for each item in a list. Within this function, you can use the keyword current to refer to the row being evaluated. In this case, the condition current.Status == "Done" checks whether the current page’s status property is set to Done. If so, it will return true; otherwise it will return false.

If all subtasks are Done, set the parent task’s status to Done. If not, retain the existing status of the parent task.

Action 3: Edit Status​ property

Formula: All subtasks done? ? "Done" : Parent Task.Status​

Again, the ternary operator is used here to see if all subtasks are done. If they are, the automation will set the parent task’s status as Done; if not, it will keep the parent task’s status as is.