Calculated fields : Senta

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

BETWEEN
DEFAULT
IF
MIN / MAX
SUM

BETWEEN

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":

DEFAULT

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:

Subtraction

DEFAULT( client.salary, 0 ) - DEFAULT( client.moreincome, 0 )

Multiplication:

DEFAULT( client.salary, 0 ) * DEFAULT( client.moreincome, 0 )

Division:

DEFAULT( client.salary, 0 ) / DEFAULT( client.moreincome, 0 )

IF

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)