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 our Recipes.
Scenario:
I have a multiple choice Choices field with options 'Apple', 'Banana', 'Orange', '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 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, 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 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 totalling formula is:
{{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 it's 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.
e.g.
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.
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.
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 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.
Lets 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:
- 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 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.