Formulas are used to do calculations using values from fields of other controls. They work like formulas in Excel.
Formula fields can be added to your forms from the template editor:
You can click on the cog to open the Settings panel for a formula field, for example:
- Give the field a Display Name
- Edit the formula
- Show formula refs (that is, show the cell references alongside each field in the template - see below)
- Hide the field (so field users don't see the formula - this can be useful for intermediate calculations, or calculations that are needed for Exports).
See below example of a formula field that adds two numbers together, while showing formula references along the left of the screen:
Note that, unlike Excel, you do not need to include the “=” sign (i.e. the correct formula is “A+B”, not “=A+B”.
Types of values
There are three types of values that are recognised by formulas:
- Numbers, which can be references to Number fields, or else numeric constants like 1, 2.5, 3004.0, etc. For example: A + B (where both A and B refer to numeric fields), 100 + 1, A + 100 (where A refers to a numeric field)
- Times represent the time elapsed since midnight. These can be references to Time fields, or else time constants like 08:00, 17:30, etc. For example: B – A (where both A and B refer to Time fields), 17:30 – 08:00, A – 12:00 (where A refers to a Time field)
- Strings, which are everything that is not a Number or Time (most commonly, these would be string constants or Text Fields. For example, the following uses the IF function to display different strings based on whether a number is zero or not: IF(A = 0,"No","Yes")
Mixing types in expressions
- Time and Number: This will attempt to treat the number as a time. The value of the number is treated as a number of days. E.g. 1 = 24 hour, 0.5 = 12 hours, etc. So “08:00 + 0.5” should become “20:00”.
- Time and String: This will attempt to convert the string to a time (and will return an error if this cannot be done).
- Number and String: This will attempt to convert the string to a number (and will return an error if this cannot be done). E.g. using 0+A in a numeric formula (instead of A) helps to handle situations where a user inputs a non-numeric entry for A.