Our support site has moved, please go to https://support.formsonfire.com
Follow

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 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))