Flavors type "Formula" (calculated fields)

Flavors of type "Formula" can be used to calculate between different numeric and date fields from system fields or other flavors. These calculations can be created at task level as well as at project level (portfolio). They work for simple and for more complex formulas. This article shows how to create a Formula Flavor and edit its formula in the editor, and lists the available fields, functions and operators.

Example

As a simple example: there is a custom field (flavor) for the "hours worked" and one for the "hourly rate". A Formula Flavor can multiply them, and the billable revenue is calculated automatically.

The corresponding formula (with pink formatting 😎) looks like this:

In addition, the list view can calculate an average or a sum in each column with numerical values – including or excluding subtasks:

Create a Formula Flavor

Prerequisite: You are allowed to create Flavors.

1. In the flavor section of a task, select the + next to the group name and then Create flavor. Admins can also create Flavors in the workspace settings under Flavors with New flavor.
2. Select the type Formula in the Advanced area.
3. Enter a name in the Title field, for example "Billable revenue".
4. Click Create.

The Flavor is now created but does not contain a formula yet.

Edit the formula

You edit the formula in the Flavor's menu. There are several ways to get there:

• In a task: In the task details, click the Formula Flavor in the flavor table and select Edit Formula.
• In the project master data: Click the Formula Flavor in the flavor table and select Edit Formula.
• In a list: Open the column menu of the Formula Flavor's column, for example in the task list of a project or in the project list in the portfolio, and select Edit Formula.
• In the Flavor Manager (admins): In the workspace settings under Flavors, move the mouse over the Flavor title, select the icon next to the title and then Edit Formula. Here you then select Task or Project, depending on the level you want to edit the formula for.

In the formula editor, proceed as follows:

1. Enter the formula in the top field, or expand the selection area at the bottom of the editor and insert fields, flavors and functions with Insert.
2. Check the result in the result preview. It shows the current result, provided the formula was entered correctly.
3. Save the formula with Save. With Cancel, you close the editor without saving.

Structure of the formula editor

• The formula is entered in the top field. Functions and texts are highlighted in color.
• The result preview (green field after the equals sign) continuously checks the entered formula and outputs a result if possible.
• The search field can be used to find fields, flavors and functions.
• The arrow at the bottom of the editor expands and collapses the selection area.
• The left column of the selection area contains the sections General, Fields, Flavors and Functions. Under Flavors, you find all Flavors of the workspace.
• The right column shows the description, syntax and, if available, an example for the selected entry.
• The Insert button inserts the entry into the formula field.

Available fields

Standard fields

The Fields section of the editor currently offers the following standard fields:

Custom fields (Flavors)

In addition to the standard fields, all Flavors used in the workspace are available in the formula editor so that their values can be used in formulas.

Available functions

The formula editor offers more than 50 functions. We support many of the common functions you may know from Excel or Google Sheets. Description, syntax and example appear in the editor as soon as you click a function. Detailed explanations with application examples are available in the articles of the category Formula Templates:

• Numbers and sums: abs, asNumber, floor, ceil, round, pow, cbrt, min, max, minAll, maxAll, minFolder, maxFolder, sumSubtask, sumAll, sumFolder
• Text: trim, lower, length, startsWith, endsWith, replace, count, contains, indexOf, padLeft, padRight, segment, sort, subString, format
• Date and time: year, date, shortDate, formatDate, toTimestamp, fromTimestamp, days
• Buttons: createButton, OpenURL, OpenMail, JSONPackage, HTTPPost
• Conditions, flavor values and data: if, flavor, projectFlavor, relation, fromParent, asType, persist, shortenGUID, encodeURI, getUserName, getUserNamedEMail, getUserEMail, selectedColumns, markedColumns, registerFlag

getUserEMail does not have its own article yet; its description is available in the editor.

Operators

Operators allow calculations, comparisons and logical combinations within formulas. They can be used in combination with fields, values and other functions to create dynamic calculations and conditions.

5 * 3 = 15

10 / 2 = 5

7 + 3 = 10

9 - 4 = 5

if(5 > 3 && 2 > 1, "true", "false") = "true"

if(5 > 3 || 1 > 2, "true", "false") = "true"

if(5 == 5, "equal", "not equal") = "equal"

if(5 != 3, "true", "false") = "true"

if(5 > 3, "true", "false") = "true"

if(2 < 5, "true", "false") = "true"

if(5 >= 5, "true", "false") = "true"

if(3 <= 5, "true", "false") = "true"

Related articles

• 🌈 Flavors
• ⚙️ Flavor configuration
• Flavors type "Relation"