Most advanced form functions involve the use of a formula. It is important that you understand how to create formulas in order to get the most out of our dynamic features.

First, you should know how to address, or refer to, a field in their form. This is done using 'placeholders' wherever you want the system to insert answers from the fields of your form. Placeholders are entered using the unique DATA NAME property of the form field, and wrapped in curly braces: ##{{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 as: ##{{page1}}

Note: Placeholders and all other functions available for use in a formula are case sensitive.

Now that you can reference field values, you can use this knowledge to build a formula. A formula contains placeholders and/or various operators, and functions, that together give a result, much the same as an Excel formula. When the app runs the formula, placeholders are replaced with the value currently stored in the field referenced by that placeholder.

For example, you have a question that asks whether a road has been closed (field's data name is 'isclosed'), and a follow-up question that asks the reason for the closure (field's data name is 'closedreason'). The 'isclosed' field has two options: Yes (underlying value Y) or No (underlying value N).

If the user answers No (N), we will not want to show the 'closedreason' field. This calls for a visibility condition on the 'closedreason' question, defined as a formula: ##{{isclosed}} = ‘Y’. The above formula says that the 'closedreason' question is only visible if 'isclosed' was answered with the Yes (Y) value.

Note: We use single quotes ( ' ) to include literal/fixed values - i.e. the 'Y' part of the formula.

Let’s look at a more advanced constraint example:

Say you want to include a 'dateofbirth' question that only constrains to 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.

Referring to column values of a selected row from a data source

The form designer allows you to link the 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. This means you need to add square brackets around the column's index number - e.g. ##{{product[index]}}.

Data source column indexes always start at zero - e.g. the first column has index of 0, the second column has index 1, third column is index 2, and so on.

If you have a data source called 'Products' that contains your product list with rows such as:

You will need to 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 2nd column are used to refer to the 3rd column (the Price) in the products data source.

Did this answer your question?