Calculation field

Revision as of 07:03, 22 July 2016 by Admin (talk | contribs)

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

Entries contain fields: Price, Count
We add a calculating field "Sum" by using the following expression:

#{Price}*#{Count}
Example 2

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

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

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

#{EndDateTime} - #{StartDateTime}
Example 4

Entries contain fields: StartDate, EndDate
We add a calculating field "Days" by using the following expression:

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

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

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}