# Calculation field

0 |

The values in the calculation fields are calculated from the expressions specified by you.

The following types of expressions are supported:

- Mathematical - Expression involving numbers.
- String - String can also be added together, compared, etc...
- Boolean - Expression 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.

Variable must be enclosed by a pound sign and open brace #{ and a closed brace }. i.e. 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 numerical result.*Date/Time result*is used to represent the result in 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 1: Sum

Entries contain fields: Price, Count

We add a calculating field "Sum" by using the following expression:

`#{Price}*#{Count}`

##### Example 2: Percentage

Entries contain fields: Count, Total

We add a calculating field "Percentage" by using the following expression:

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

##### Example 3: Duration

Entries contain fields: StartDateTime, EndDateTime

We add a calculating field "Duration" by using the following expression (result is number seconds):

`#{EndDateTime} - #{StartDateTime}`

##### Example 4: Days

Entries contain fields: StartDate, EndDate

We add a calculating field "Days" by using the following expression:

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

##### Example 5: Days left

Entries contain fields: Time

We add a calculating field "Days left" by using the following expression:

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

##### Example 6: 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}`