A calculated field works out a value from a formula. This article explains how a formula is put together, so you can describe the field you need when you request it from Kolleno.
Choose the record
Every calculated field belongs to one type of record:
Customer
Invoice
Credit note
Payment
Bank line
Task
Customer group
Contact.
Choose the record the value describes. For example, "Net overdue" for a customer belongs to the Customer.
Building blocks
A formula is made of steps. Each step adds (+), subtracts (−), multiplies (×) or divides (÷) one of the following:
A field of the record, for example a customer's Balance overdue or Prepayments
A field of a linked record, for example the balance of an invoice's customer
The total of linked records, for example the sum of the balance of all of a customer's invoices. A formula can use one total of linked records.
A fixed number, for example × 100 to show a percentage
A bracket group: a set of steps inside brackets
Visual Example:
How the steps are worked out
Steps apply from left to right, like a calculator. A bracket group is worked out first, then used as a single value in the step it belongs to.
For example, Net overdue = Balance overdue − Prepayments − Credit notes remaining:
Start with Balance overdue
Subtract Prepayments
Subtract Credit notes remaining
And Overdue share % = (Balance overdue − Prepayments) ÷ Balance × 100 works out the bracket first, then divides by Balance and multiplies by 100.
Visual Example:
Limits
A formula can have a minimum (never below) and a maximum (never above). Limits are applied last. For example:
Never below 0, for an amount that should not go negative
Between 0 and 100, for a percentage
Comparing two values
To check whether one value equals another, use a field that subtracts one from the other, then filter on it:
is 0: the two values are equal
is not 0: they differ
greater than 0 or less than 0: which one is larger
How results behave
Empty fields count as 0 in the formula.
Results are rounded to 2 decimal places.
Dividing by zero leaves the value empty for that record, for example a percentage of a balance that is 0. Empty values match neither "greater than 0" nor "less than or equal to 0". Use the is empty or is not empty filter to find or exclude them.
Totals of linked records can be filtered on, but lists cannot be sorted by them. Adding up every linked record for every row would slow the list down.
What to send Kolleno
Contact Kolleno Support (support@kolleno.com) with:
The name of the field, as it should appear on the column and filter
The record it belongs to
The formula, step by step, including any brackets
Any limits (never below / never above)
One or two example records and the value you expect for each
Kolleno previews the result with you before the field goes live. For where calculated fields appear once set up, see Calculated fields: your own columns and filters from a formula.