This article describes how to use the Formula editor to perform various calculations based on the data in your Form(s).
Adding a Formula to a Calculated field
To be able to apply a Formula, you need at least one Form and a Calculated field. Calculated fields are used for quick calculations based on the collected Records.
To add a Formula to a Calculated field, follow these steps:
- Navigate to the Form.
- Click “Form settings.”
- Hover the mouse over the Fields in the Form until you see the “+” sign.
- Click on the “+” to add a Calculated field to show the result of the Formula you wish to use and display.
In this case, “First and Last name” as we want to combine the partner names into one text string.
- Scroll down to “Default value, Enter formula here” and either type in the Formula, or click on “Formula editor” (recommended).
- When you open the Formula editor, you can see the list of all available Formulas on the left side panel. Browse this list, and select the Formula you want to use.
- Click “Enter your Formula here” and type in the chosen Formula. In this case,
CONCAT(
, which operates on theCONCAT(FIRST_NAME, " ", LAST_NAME)
logic.
“The formula is invalid” system message appears until the Formula you’re entering is complete and correct.
On the right side panel, select the Fields you wish to use in the Formula. This process is based on the logic of the dot notation, which essentially gives you a map of the Fields in your Form(s).
In this example, click on “First name”.
Add a comma and a space (
,
).
- Type in two double quotation marks. The character you add between the quotation marks is the separator between the first and second selected Field values. Possibilities include
“ “,
where a space separates the values, or“.”,
where the full stop (dot) is the separator.
- On the right side panel, click on the second Field, whose value you wish to add to your Formula.
- Close the parentheses to finish the Formula and click “Done” to apply it to your Form.
- Click “Done” on the Form settings page to add the Calculated field.
- Click “Save” to apply the changes to your Form.
Success, the you set up a Formula in a Calculated field.
In this example, when adding Records, the First name and Last name text Field values are combined automatically.
Using the Formula editor with referenced data
You can use Formulas to pull in info from other Forms by adding a Reference field to another Form that can reference the values from the original Form. Then you can add a Calculated field and pull in the data from the second Form using dot notation in the Formula editor.
The Formula in this case would read as: CONCAT( clchw8tm7t8nb0ua.[First name], "-", [Middle name], "-",
clchw8tm7t8nb0ua ``.[Last name])
When using the Formula editor, you can see the Code value of the available Fields, which helps to identify which Field to use for respective Formulas. Therefore, filling out the Code property in the Form Designer is highly recommended, especially when working with formulas or the API.
So if you’ve assigned a Field Code, then the Formula of this example could look like this: CONCAT(Rightsholder.first_name, "-", middle_name, "-", Rightsholder.last_name)
Great success, the Calculated field now displays data from a Referenced Form as well.
You can update an already existing Formula through the Form settings.