Below is a list of common scenarios that involve the use of a formula to achieve. This is purely a quick reference guide in a Q & A format. For more comprehensive explanations including worked examples, please refer to the rest of our Recipes.
Q: How do I move fields between Pages in the Form Designer?
A: There are a few ways to do this:
- You can use the "Dolly" icon seen when hovering over the field. Click on this and select another page to move the field to that page.
- 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 these.
- 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.
Scenario: I have a multiple-choice Choices field with options 'Apple', 'Banana', 'Orange' and 'Mango.'
Q: How do I access the selected options of the Choices field?
A: 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')
Scenario: I have a set of Choices fields with data names of q1, q2, q3 and q4. All have fixed answer options - e.g., 'Y', 'N', 'N/A.'
Q: How do I join the answers from these fields into one single text string?
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}})
Q: How do I count how many questions were answered as 'Y'?
A: 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)
Q: 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
A1: 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 will know what to choose. E.g., your Yes option would have answer value of 3 and display text of 'Yes.' This is the simplest approach since then all you need to do for a totaling formula would be:
{{q1}} + {{q2}} + {{q3}} + {{q4}}
A2: 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))
A3: Add a hidden field for each question, with the hidden field containing just the IF() formula for its associated question.
e.g. Hidden field named q1Score would have a Dynamic Value formula of:
IF({{q1}} = 'Y', 3, if({{q1}} = 'N', 1, 0))
Q: I am assigning the NOW() / UTCNOW() formula function to a field, but the time part is getting lost!
A: If you are assigning NOW() to a text field, or indirectly assigning to a text value (e.g. as in your 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 your use of the NOW() value in a FORMAT-DATE() function, specifying the time value as part of the desired text output.
FORMAT-DATE(NOW(), 'yyyy/dd/MM HH:mm:ss')
Q: How can I set a date/timestamp to be stored for the exact moment a button is pressed?
A: 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.
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.
Q: How can I create a counter/clicker in my Form which increases/decreases a number each time the user presses a button?
A: A combination of Action field type features allow you to create counter functionality. Use the “Always Trigger On Button Press” option found on Action fields in the Form designer.
By default, buttons only trigger any dependent formulae once, on the first time a user taps; the above option will instead 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 title "Increase" and the other with title "Decrease." We also assume you have a Number field with 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:
- First 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 have been tapped by the user. So the next part of the formula is now checking which button got pressed.
- If the button titled "Increase" got 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.
Q: How do I get each selection on a multiple-selection Choices field to appear on a new line?
A: By default, a multiple-selection Choices field will create a pipe-separate 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.