How to use Formulas and Functions?

Note: this article was written for version 1.8 and above

Using a field type of Formula and Function from the Extension we can create complex calculations. Let’s look at different examples.

Simple calculations

([36]+[54])/2

where 36 and 54 — id of numeric fields.

Mathematical functions

In the formula we can apply a mathematical function of MySql. For example, we need to round the calculation result to an integer:

FLOOR(([36]+[54])/2)

The calculation formula with the condition

To add a condition in the formula we need to use an IF statement.

if(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

if([36]>0,([36]+[54])/2,0)

In this example, if the value 36 is greater than 0, then we do the calculation, otherwise, return zero.

If you  need to return a string, such as a blank, or the entered text, then the string must be enclosed in single quotation marks, for example:

if([36]>0,([36]+[54])/2,'no data')

Check the list choices  in the IF statement

It is very often necessary to make calculations only in the case if, for example the field “Project Status” has field type “Dropdown” and value “Completed”. In this case, the condition will be as follows:

if([11]=15,([36]+[54])/2,0)

where 11 is the ID for the Status field, and 15 is the ID of the value “Completed”.

Now let’s look at an example where we need to check multiple values of statues:

if([11] in (15,16,17),([36]+[54])/2,0)

where 15,16,17 are the ids of the choices values.

To check the value of the Boolean Field, use ‘true’ or ‘false’ example:

if([20]='true',([36]+[54])/2,0)

The calculation of the time difference

Very often there is a need to create a calculation of time difference between Start Date and End Date of the Project or Tasks.

Dates are stored in a database in seconds, then calculate the number of days will be the following:

([300]-[159])/86400

where 300 is the ID of the field “End Date”, 159 — ID of the field “Start Date”, 86400 — the number of seconds in a day.

But as the date can be entered incorrectly or be missing altogether, then we need to do checks before the calculation. The correct formula would be as follows:

if([300]>0 and [159]>0 and [300]>[159],([300]-[159])/86400,0)

Note: you can use [TODAY] for current date.

The list of fields that are allowed for use in the formula:

  • Input Numeric Field
  • Input Numeric Field in Comments
  • Formula
  • Date with calendar picker
  • Date with calendar and time picker
  • Boolean Field
  • Drop-down list
  • Radioboxes

Functions

Functions help you to perform calculations using fields from other entities. To add a function in a formula, use brackets. Consider a simple example of using functions:

{12}+[5]

where 12 is the ID function and 5 the ID number field.

The following example will allow you to perform the calculation with several functions:

{12}+{13}

Also functions can be applied in conditions:

if([5]>0,{12},0)

Formula in  Function

Creating a new function, for example to calculate the sum of values you can use in formula numeric fields only and simple mathematical calculations.

The list of fields that are allowed for use in the functions:

  • Input Numeric Field
  • Input Numeric Field in Comments