Formulas in forms
Type of field "Formula"
Formulas allow you to apply arithmetic operations and functions to data fields within a custom form. This is similar to the formulas you can write in a cell of an Excel spreadsheet. Currently, formulas can only be applied to the field of the same record (or "row").
First steps
When creating a form, it is possible to specify a type of field that is a Formula. This feature enables you to add the values of fields within a form, create data strings based on fields, and specify a value based on a particular condition in your form.
NOTE: This feature is currently in BETA. We are rolling out this version to beta testers and continue improving the process's functionality. Please get in touch with us if you experience any difficulties while trying out this new feature.
Add and edit a formula field
To add a Formula field to your form:
- You must be in the creation panel for your form.
- You will see all the different type of fields to the left of the screen and you can drag and drop the type of field Formula to the position within your form where you wish to enter it.
- Now enter the title that you wish to give the field.
- You need to SAVE your form so the available fields appear in the Formula box. But do not PUBLISH until after you have set your formula
- Click on the pencil icon to edit the parameters of your formula
- You will enter the formula in the Formula box
- You will see a list of the available fields for your formula. The name of the field is generated automatically by the system using the title of the field and a unique code.
- Click on the field you want, this will copy it into your clipboard.
- Then click on the formula box and paste the name of the field into the box, this will enter the desired field into the box. Arguments contain the information you want to calculate.
Operators
Arithmetic operators:
The following arithmetic operations are available:
+(addition)-(subtraction)*(multiplication)/(division)
Note: To add/subtract/multiply or divide data, your fields must be of the number format OR unformatted number.
Relational and logical operators
It is possible to use the following relational and logical operators
==(equal; converts the data type to compare values)===(identical; does not convert data type. "1" string is not similar to 1 number)!=(not equal)!==(not identical)<(less than)>(greater than)<=(less than or equal to)>=(greater than or equal to)&&(AND)||(OR)
Functions
For functions, see this article.
Additional examples
Addition of field values:
You can add data within two or more different fields. If you have the following fields in your form:
Number of female participants + Number of male participants
This will give you the Total number of participants
Use formulas to prioritize risks:
You can also use this feature in your risk register if you calculate the level of a risk by multiplying its probability and impact in your project.
Have in your form a field that is Probability (Scale of 1-5). Add a field that is Impact (Scale of 1-5)
Add a formula and make Probability * Impact this will give you your risk's level.
Create a unique ID:
You can generate a unique ID with the RECORDID() function. This could be used to assign identifying IDs to each record.
For example if you have a form that gathers the following information :
- Location of clinic - Date of test
CONCAT(UPPER(MID(location, 1, 3)), date_of_test, RECORDID())
Limitations
Currently the Formula feature is limited to records. This means that you can add a formula and apply it only to data within the same record. In blue in the example below:
For the time being you can use the Analytics module for your column-based calculation needs. In orange in the example below:
