# Calculation field: Difference between revisions

No edit summary |
No edit summary |
||

Line 1: | Line 1: | ||

{| align="right" | {| align="right" | ||

| __TOC__ | | __TOC__ limit|0 | ||

|} | |} | ||

The values in the calculation fields are calculated from the expressions specified by you.<br /> | The values in the calculation fields are calculated from the expressions specified by you.<br /> |

## Revision as of 08:47, 22 July 2016

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}
```