Calculation values
Calculation values bring spreadsheet functionality to Aptono. A calculation field takes a formula as input and calculates the answer based on information in other fields. A formula can reference to a field within the same record or to a field that is in a reference record.
To add a calculation field to a record, you first need to have fields with number data in them or reference records that have number data in them. Then add a new field which will be the calculation field. At 'Type of field' click 'Calculated value'. To reference a value from another field start by typing 'record.' in the formula. A list of available fields will be shown in a dropdown. If there are no fields visible, it means the collection does not have any suitable fields yet.
Fields that can be reference
Aptono currently only allows fields to be referenced in a calculation if it is directly within the same collection as the calculation field, or if it is one reference field away.
Using reference fields in a calculation
When using a reference field in a calculation, please keep the following behavior in mind:
- When a reference field is not set to a record (empty reference), Aptono assumes that the value is 0.0 (zero) and uses that to calculate the end result.
- When using a multi reference field in a calculation, Aptono will look up the value for each reference, calculate the sum, and use that to calculate the end result. This behavior can be used to calculate totals or sums of values.
Implications of referencing a field in a calculation
To make sure that results of calculations stay correct, Aptono applies some restrictions to the record template. Definitions of fields that are referenced in a calculation:
- cannot be changed while being referenced in a calculation.
- cannot be deleted while being referenced in a calculation.
Maximum dependents of a calculation
Currently Aptono limits the amount of dependents that can be part of calculation to 32 fields.
Adding a calculation field to a collection with existing records
A calculation field can be added to a collection that has existing records. But Aptono does not calculate values for the existing records directly. They need to be calculated manually by right-clicking on the name of the field, and selecting 'Redo calculation'. Note that right-clicking is currently not supported on platforms with touch screens.
Changing the formula of a calculation when there are existing records
When a calculation field has records with non placeholder values (see also here), its formula cannot be changed anymore. This is the case when:
- a record was added to the collection after the calculation was defined
- a manual request to recalculate the field was done This limitation prevents a large amount of fields to be changed at the same time and is applied to avoid large peaks in required calculation power for the server. In the future Aptono will support this via background processing jobs.
You can define a new field with the updated formula and delete the old field.
Units of calculation
It is possible to add units to fields that have number data. When a field has a unit, Aptono can determine what the unit of the calculated value is automatically. Note that units are not required for the calculation. In the example below:
- Budget has unit [€]
- Hour rate has unit [€ / hour]
- Hours has unit [hour]
The formula Remaining budget = Budget - Hour rate * Hours will give a unit of [€]. You can scale the calculated unit by specifying a display unit:
Loop detection
Note that loops in calculations are not supported. When a definition of a calculation would case a loop, the user will receive an error message.