Calculated fields
The values stored in form fields can be calculated using an expression.
For more information on Expressions, go to our guide.
To add an expression to a form field, set the field to be readonly:
This now gives you an area to write your expression. The following expressions can be used in a calculated field:
BETWEEN
Use the BETWEEN expression in a Yes/no radios field to determine whether a value is between certain parameters.
For example, I am storing client turnover in a Money field and use this BETWEEN expression in a Yes/no radios field to return "Yes" or "No" based on whether the client turnover is between £50,000 and £100,000:
BETWEEN(client.clientturnover, 50000, 100000)
If the value is between £50,000 and £100,000, the Yes/no radios field will return "Yes", and if the value is outside of £50,000 and £100,000, the field will return "No":
DATEDIF
Use the DATEDIF expression in a Number field to record the number of days between two dates.
For example, I will calculate the number of days between the date the Tax return was submitted and today's date using this expression:
DATEDIF(client.datesubmitted, TODAY())
DEFAULT
The DEFAULT expression is used so that calculations have a default value of zero if there’s no value in either field. If you do not use DEFAULT, the calculation may fail.
You can use DEFAULT for addition, subtraction, multiplication and division.
For example, the expression to add my salary field and additional income field together is:
DEFAULT(client.salary, 0) + DEFAULT(client.additionalincome, 0)
To use subtraction, multiplication or division use the relevant operators.
- Subtraction
* Multiplication
/ Division
IF
Use an IF expression to determine which calculation is needed based on the criteria you set.
For example, in the Services/payment form we have field to record the Accounting fees and a Payment method field to record whether the client pays Monthly or Annually. If we need another field to record the annual total we can use different calculations based on whether the client is paying monthly or annually.
If the client pays monthly, we need to multiply the value in the accounting fees field by 12. If the client pays annually, we simply need to output the value from the accounting fees field. This is the expression to use:
IF(client.paymentmethod = "Monthly", client.fees*12, client.fees)
In the screenshot below, we have chosen the Monthly payment method therefore the Accounting fees are being multiplied by 12:
MIN / MAX
We can use the MIN and MAX expressions to return either the lowest or highest value of fields.
For example, I have four Money fields storing the client's turnover for each quarter and an additional Money field to output the lowest turnover value. We use the MIN expression for this:
MIN(client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover)
The calculated field is returning the lowest value of these four fields:
Equally, if you would like to output the highest value, you can use MAX:
MAX(client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover)
SUM
Use SUM to calculate the total of multiple fields.
For example, I will calculate the total of Net cost and VAT cost using this expression:
SUM(client.netcost, client.vatcost)
