The values stored in form fields can be calculated using an expression.
To add an expression to a form field, set the field to be "read only":
TABLE OF CONTENTS
Use the BETWEEN expression in a Yes/no 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 my Yes/no 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 field will return "Yes":
If value is outside of £50,000 and £100,000, the Yes/no field will return "No":
The DEFAULT function is used so that calculations have a default value of zero if there’s no value in either field. If you don't use DEFAULT, the calculation will fail.
For example, You might record the salary of an individual client in a money field within the client details form, plus any additional income in a separate field:
The expression to use in your "Total income" field would be:
DEFAULT(client.salary, 0) + DEFAULT(client.additionalincome, 0)
Taking the above as an example, you can perform additional basic arithmetic operations by following the same principle:
DEFAULT( client.salary, 0 ) - DEFAULT( client.moreincome, 0 )
DEFAULT( client.salary, 0 ) * DEFAULT( client.moreincome, 0 )
DEFAULT( client.salary, 0 ) / DEFAULT( client.moreincome, 0 )
The field "Annual fees" could be automatically calculated using this expression:
IF(client.paymentmethod = "Monthly", client.fees*12, client.fees)
In the above example, client.paymentmethod refers to the "Payment method" field and client.fees refers to the "Fees" field. These are the fields that are used to perform the calculation. Taking a specific client as an example:
The Annual fees field has automatically calculated client.fees*12=£600, or as a basic calculation for this client £50*12=£600. This is based on the condition that the Payment method is set as Monthly.
If the Payment method was set to "Annually", the value from client.fees will be returned:
MIN / MAX
We can use the MIN and MAX expressions to return either the lowest or highest value of fields. For example, if I store the turnover for each quarter for my client and can add a field for "Lowest turnover quarter", I can use the MIN expression to return the lowest value from these four fields.
MIN (client.q1turnover, client.q2turnover, client.q3turnover, client.q4turnover)
The calculated field will return the lowest value of these four fields:
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:
These are the fields on the form with Total Cost being our calculated field: