Most advanced Form functions involve the use of a formula. As such it is critical that you understand how to create a formula to get the most out of our advanced features.
Field Data Names
First off you need to know how to address or refer to a field in your Form. This is done by using placeholders wherever you want the system to insert answers from fields of your Form. Placeholders are entered using the unique data name property of the Form field, wrapped in curly braces: e.g., {{dataname}}.
For example, if you have a Page group called "page1" and a Text field named "xyz" inside that group:
- You would refer to xyz as: {{xyz}}
- Refer to page1 with: {{page1}}
NOTE: placeholders and all other functions available for use in a formula are case-sensitive. Data Names cannot begin with a number, contain spaces, or include special characters.
Now that you know how to reference field values, you can use this knowledge to build a formula. A formula contains placeholders and/or various operators and functions which together give a result - much the same as an Excel formula works. When the app runs the formula, any placeholders are replaced with the value currently stored in the field referenced by that placeholder.
Let's look at a simple example:
Say we have a question that asks whether a road has been closed (field's data name is "isclosed"), and a follow-up question that asks what the reason for the closure is (field's data name is "closedreason"). The "isclosed" field has two options: Yes (underlying value Yes) or No (underlying value No).
If the user answers No, then we don’t want to show the "closedreason" field. This calls for a Visibility condition on the "closedreason" question, defined as a formula: {{isclosed}} = ‘Yes’. What this formula says is that the "closedreason" question is only visible if "isclosed" was answered with the Y (Yes) value. Note that we use single quotes ( ' ) to include literal/fixed values - i.e., the 'Yes' part of the formula. This formula would be written in the Visibility property of the data name:
Let’s look at a more advanced constraint example. Say we want to have a "dateofbirth" question that we want to constrain to only allow dates in the past. In this case, we would use a function in our formula to evaluate today’s date:
{{dateofbirth}} <= today()
The function "today()" is one of many functions available for use in your formula.
Formula and Text Builders
Formula and Text Builders are on numerous properties throughout the platform (Hammer Icon). Allowing for dynamic values using formulae or referencing other fields to drive the field's value.
Enter a formula directly into the property's text box or hit the Hammer Icon launching the supported builder.
Where inserting field datanames and/or functions aid formula creation, add comments, and validate before applying.
Insert a Field
Use the dropdown to search and select existing form fields, inserting their datanames into your formula.
Insert a Function
Use this dropdown to search, view usage examples, and select functions, inserting function structure and placeholders for required values.
Formula Builders allow for functions and complex scenarios, whereas Text Builders have a built-in CONCAT() function simply requiring static text with field datanames.
When entering a formula, use the TAB key to search and autocomplete formula function names. Referring to column values of a selected row from a Data Source
The Form Designer allows you to link Choices fields to a Data Source that you have previously uploaded. Data Sources can have as many columns as you like, and you may want to refer to the values in these columns when creating a Form. To do so, you will need to use a formula to refer to the desired column.
By default, the first column in a Data Source (also known as the Value column) is always used as a field's answer. If you want to refer to any other column's value, you need to use an index syntax. Specifically, you need to add square brackets around the column's index number - e.g., {{product[index]}}.
Data Source column indexes always start at zero - i.e., the first column is index of 0, the second column is index 1, the third column is index 2, and so on.
Let's look at an example:
Imagine you have a Data Source called "Products" that contains your product list, with rows such as:
You create a Form named "Sales Order" which you will use to capture orders for your products. The Form has the following fields:
- product_choice: A Choices field that is linked to the Products Data Source.
- quantity: A Number field that captures the quantity of product ordered.
- total: A Number field that will calculate the total amount by multiplying the product's price by quantity.
On the "total" field, you would define a Dynamic Value formula as follows: {{product_choice[2]}} * {{quantity}}
Note: The square brackets and index value of 2 refer to the 3rd column (the Price) in the Products Data Source.
Now you are ready to tackle formulas on our platform, here is a link to our Formula Cheat Sheet. This is a comprehensive list of our available formulas.
Inserting Comments
We've added a new icon to the Formula Builder which, when clicked, will insert a comment for you (denoted by /* */). Use it to make sure everyone understands the more complicated parts of your formulas (and to discourage unwanted tampering)
- 1) Click on the Hammer icon to bring up the Formula Builder
- 2) Click the Comments icon to insert a comment.
Required
The 1st thing to note is that Required properties have a built-in IF condition. If the result is true, the field will require a value to be entered (not blank) before navigating away from the page. If the result is false, the field won't require a value.
Create a formula that returns a true/false result.
The example references the Action field's data name, {{btnAction}}, and compares it using an Equals To function to static text, 'Required'. This static text should match the button's value.
The field will require a value preventing the user from navigating away from the page if the Action field's "Required" button is not pressed.
{{btnAction}} = 'Required'