Calculation field

The values in Calculation fields are calculated from the expressions specified by you.
The following types of expressions are supported:

• Numeric - Expression involving numbers.
• String - Strings can also be added together, compared, etc...
• Boolean - Expressions that evaluate to true (1.0) and false (0.0).
• Numeric and string types can not be mixed in a left / right operand pair.

Variables

Variables are values of other fields in the same record.

Variables must be enclosed by a hash mark and open brace #{ and a closed brace }. e.g. expression:

#{field_name1} + #{field_name2}

String values/variables must be enclosed by Quote characters:

'Result: ' + '#{string_field1}' + '#{string_field2}'

Operators

The following operators are supported:

• ( open parentheses
• ) closed parentheses
• + addition (for numbers and strings)
• - subtraction
• * multiplication
• / division
• % modulus
• == equal (for numbers and strings)
• != not equal (for numbers and strings)
• < less than (for numbers and strings)
• <= less than or equal (for numbers and strings)
• > greater than (for numbers and strings)
• >= greater than or equal (for numbers and strings)
• && boolean and
• || boolean or
• ! boolean not

Result type

For each calculation, there must be a correct result type set:

• Real result and Integer result are used for expressions with a numerical result.
• Date/Time result is used to represent the result in the form of date and time. The calculation result is interpreted as the number of seconds that has passed since January 1, 1970 (UNIX time).
• Date result is the same as the previous type, but displays only the date.
• String result is used for expressions that contain string manipulations or functions that return a string.

Link to entry

Use the following syntax to get access to the values of entry fields like Link to entry:

#{field_name.child_field_name}

where field_name – name of field like Link to the entry, child_field_name – name of one field of a related entry.

If a field like Link to the entry contains several links, then use index (starts from zero) to get access to them:

#{field_name@0.child_field_name}, #{field_name@1.child_field_name}, …

You can specify one of the supported aggregation functions instead of the index:

#{field_name@sum.child_field_name}

This expression returns the sum of values of field child_field_name for all entries included into field_name. In addition, the following functions are supported: avg, min, max.

Use the following syntax to get the number of entries in field field_name:

#{field_name@size}

Examples

Example: Sum

Entries contain fields: Price, Count

We add a Calculation field Sum by using the following expression:

#{Price}*#{Count}

Example: Percentage

Entries contain fields: Count, Total
We add a Calculation field Percentage by using the following expression:

(#{Count } / #{ Total }) * 100

Example: Duration

Entries contain fields: StartDateTime, EndDateTime
We add a Calculation field Duration by using the following expression (result is number of seconds):

#{EndDateTime} - #{StartDateTime}

Example: Days

Entries contain fields: StartDate, EndDate

We add a Calculation field Days by using the following expression:

datediff(#{EndDate}, #{StartDate})

Example: Days left

Entries contain fields: Time

We add a Calculation field Days Left by using the following expression:

if( #{Time} > now() , rint((#{Time} - now())/(60*60*24)) , 'in the past' )

Example: References

There’s library Order – with orders and library Item – with items. Library Order contains a link to library Item and numeric field Count. Library Item contains field Price.

To calculate the cost of the order, let’s add calculable field Sum using the following formula:

#{count} * #{item.price}

If an order can make a reference to several different items, their total cost can be calculated:

#{item@sum.price}

The most expensive item:

#{item@max.price}

The cheapest item:

#{item@min.price}

The average price for the item:

#{item@avg.price}