Below is a list of common scenarios that involve using a formula.
This is purely a quick reference guide in a Q&A format.
For more comprehensive explanations, including worked examples, please refer to our Recipes.
Question 1
How do I move fields between Pages in the Form Designer?
You can move fields between Pages when you place the designer into “continuous” mode.
To do this, click the View Mode: “Paged” link near the top right of the designer.
This will toggle the designer to show all pages under each other, allowing you to drag between them.
The other way to move fields is to export your design to Excel using the Export button on the top right of the designer page.
Open the Excel file that downloads and move fields around as needed.
Then, use the Import button to pull your design back into the platform.
The Excel import/export option is worth a look because it’s also a much faster way to build Forms generally.
Question 2
How do I access the selected options of the Choices field?
Scenario:
I have a multiple choice Choices field with options ‘Apple’, ‘Banana’, ‘Orange’, ‘Mango.
Use the SELECTED() function to get a true/false result for each option in your Choices field.
e.g., if you want to control the visibility of other fields in your Form based on if the Apple option is selected:
SELECTED({{mychoicesfield}}, 'Apple')Question 3
How do I join the answers from multiple fields into one single text string?
Scenario:
I have a set of Choices fields with data names of q1, q2, q3, q4. All have fixed answer options – e.g. ‘Y’, ‘N’, ‘N/A’.
Use the CONCAT() function to create the desired result, much like you would with the Microsoft Excel CONCATENATE function.
You can mix dynamic answers with static bits of text as needed.
CONCAT('Question 1 Answer: ', {{q1}}, '; Question 2 Answer: ', {{q2}}, '; Question 3 Was: ', {{q3}})Question 4
How do I count how many questions were answered as ‘Y’?
Use the IF() function to check whether the answer for each question = ‘Y’ and assign either a 1 or a 0 based on a true/false result of the = ‘Y’.
IF({{q1}} = 'Y', 1, 0) + IF({{q2}} = 'Y', 1, 0) + IF({{q3}} = 'Y', 1, 0) + IF({{q4}} = 'Y', 1, 0)Question 5
How do I assign a score to each answer option, and total up the score for all questions? e.g. ‘Y’ = 3, ‘N’ = 1, ‘N/A’ = 0
Answer 1
Make your answer options have a value of the score in question instead of ‘Y’, ‘N’, ‘N/A’.
You can still have the display text of each option be ‘Yes’, ‘No’, ‘N/A’ so that the app user knows what to choose.
e.g., your Yes option would have an answer value of 3 and display text of ‘Yes’.
This is the simplest approach since then all you need to do for a totalling formula is:
{{q1}} + {{q2}} + {{q3}} + {{q4}}Answer 2
Use the IF() function in a nested fashion to check the value of each answer and assign the relevant score based on a true/false result.
IF({{q1}} = 'Y', 3, IF({{q1}} = 'N', 1, 0)) + IF({{q2}} = 'Y', 3, IF({{q2}} = 'N', 1, 0)) + IF({{q3}} = 'Y', 3, IF({{q3}} = 'N', 1, 0)) +IF({{q4}} = 'Y', 3, IF({{q4}} = 'N', 1, 0))Answer 3
Add a hidden field for each question, with the hidden field containing just the IF() formula for its associated question.
e.g., a Hidden field named q1Score would have a Dynamic Value formula of
IF({{q1}} = 'Y', 3, if({{q1}} = 'N', 1, 0))Question 6
I am assigning the NOW() / UTCNOW formula function to a field, but the time part is getting lost.
If you are assigning NOW() to a text field or indirectly to a text value (e.g., a Hidden field), then you will always get just a date value since the app is auto-converting from date/time data to textual data.
To explicitly get the time portion of the NOW() value, you need to wrap the use of the NOW() value in a FORMAT-DATE() function, specifying the time value as part of the desired text output.
E.g.
FORMAT-DATE(NOW(), 'yyyy/dd/MM HH:mm:ss')Question 7
How can I set a date/time stamp to be stored for the exact moment a button is pressed?
The Button field type allows you to set an answer value when the button is pressed – see the “Interaction Result” result.
The result value must be a static text value; we don’t currently support formulae in that option.
So, for example, you can have the Interaction Result be something like “Pressed”.
The reason we mention this is that you can then hook an additional field – e.g., a Hidden field – to be updated when the Button field has it’s value set.
You would do this by specifying a Dynamic Value formula for your Hidden field, where the NOW() function would be called based on whether the Button field has a value or not.
e.g.
IF({{mybuttonfield}} = 'Pressed', FORMAT-DATE(NOW(), 'yyyy/dd/MM HH:mm:ss'), '')Note that in the above example formula, you must use the FORMAT-DATE() function to specify the date/time output you desire.
See the Formula Builder help hints for the FORMAT-DATE function to understand more about the formatting options.
Question 8
How can I create a counter/clicker in my Form that increases/decreases a number each time the user presses a button?
A combination of Action field-type features allows you to create counter functionality.
Use the “Always Trigger On Button Press” option found on the Action field in the Form Designer.
By default, buttons only trigger any dependent formulae once; the first time a user taps – the above option will trigger dependent formulae every time the button is pressed.
Action fields also set their answer to be the title of the pressed button by default.
This is useful for cases where you have multiple buttons in a field and need to run formulae based on which button was pressed.
Let’s assume you have an action field with data name of “actionfield”, with two buttons defined – one with the title “Increase” and the other with the title “Decrease”.
We also assume you have a Number field with the data name of “numfield”.
On your Number field, set the Dynamic Value property to be:
IF( NOTBLANK({{actionfield}}), IF({{actionfield}} = 'Increase', VAL('numfield')+1, VAL('numfield')-1 ), 0 )There are a few things going on in the above formula:
- Firstly, we check if the Action field has a value set via NOTBLANK().
If there is no value, then the numfield will be set to 0 since no buttons have been pressed yet. - If the button field does have a value, then this means one of our buttons has been tapped by the user.
So the next part of the formula is now checking which button got pressed. - If the button titled “Increase” is tapped, then we get the current value of the numfield via VAL() and add 1.
- Otherwise, we assume the “Decrease” button was tapped and we get the current numfield value and subtract 1.
Question 9
How do I get each selection on a multiple-selection Choices field to appear on a new line?
By default, a multiple-selection Choices field will create a pipe-separated list of values of all selected options.
If you want to save each selection on its own line, you’ll need to substitute the “|” character in the Choices field with a new line character “n“.
To do this, use the SUBSTITUTE() function in the dynamic value of another field, i.e., Text field:
SUBSTITUTE({{choices_field_multiple_select}}, '|', 'n')If you wish to save this data to a data source, then make sure that the data source column that this text will be written to is set to data type “Multiline Text“.
The “n” newline character can be used in any text function where a new line is explicitly required.