Using Formulas in Survey123

38169
43
08-23-2015 06:03 PM
HannahFerrier
Occasional Contributor III
15 43 38.2K

Formulas supported in Survey123 include the following operators:

SymbolFunctionExample

+

Addition

2 + 2

-

Subtraction

2 - 2

*

Multiplication

2 * 2

div

Division

2 div 2

=

Equal

.=10

!=

Not equal

.!=10

<

Less than

.<10

<=

Less than or equal to

.<=10

>

Greater than

.>10

>=

Greater than or equal to

.>=10

or

or

.=10 or .=20

and

and

.>10 and .<20

Formulas can be used in Survey123 when building your forms in the following columns:

Constraint

Adding a constraint to a survey question will restrict the accepted inputs for a response. This could include a specific range of numbers, combinations of letters and numbers or general pattern matching. In your spreadsheet, the constraint expression is entered into the constraint field and helpful text is entered into the constraint_message field. In the constraint expressions, the input for the question is always represented by a full stop.

For example, the following formula can be used to restrict the input of an integer field to positive numbers only:

.>=0

Regular expressions can also be used for pattern matching to constrain valid responses. This regular expression can constrain the input of a string field to a well-known email address format:

regex(., '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}')

See here for more information on regular expressions...

See here for more information on using constraints...

Relevant

A question, or a sets of questions, can be hidden and revealed based on previous answers using relevant expressions. These expressions are entered into the relevant column and the answers to previous questions are always referred to as follows: ${field_name}. You can apply a relevant expression to a single question, or group questions together and set the relevant expression for the entire group. For example, this expression will reveal a question if the answer to the previous question is true:

${previous_question} = 'true'

This example will hide questions if the answer to the previous question was greater than 100:

${previous_question} <= 100

This example combines multiple operators and questions:

${previous_question} + ${other_previous_question} <= 100

When using mathematical operators, be aware that sometimes you may need to cast values into numbers. In the previous examples we assumed that previous_question and other_previous_question where integers or decimals... but what if the question types were strings? Then you cast them as follows:

int(${previous_question}) + int(${other_previous_question}) <= 100

See here for more information about using relevant clauses...

Calculations

Calculations are performed in the calculation field of a question. Calculations are often associated with calculate type of questions, but can also be applied to integer, text etc type of questions. The outcome of the calculation can be used to populate relevant or constraint expressions by referring to the field name of the calculate question. They can be used to hold values that do not need to be displayed on the form, but are included in the feature service.

For example, you can create a question of type calculate and name it calc, then insert the following expression int its calculation column:

${question_1} + ${question_2} + ${question_3}

And then use the result to set the relevance for the next question:

${calc} <= 100

See here for more information on calculations...

Tags (1)
43 Comments
KenMuir1
New Contributor

Is there a way to assign text answers with values? And then in turn calculate them at the end?

ThierryGregorius
New Contributor

I had the same question but managed to figure it out in the end.. after a bit of fiddling it turned out be rather simple.

Let's say you have a multiple-choice question ('select_one' type) with 5 possible answers, and you want to score the answer on a scale of 1 to 5.

In the choices worksheet, first create a list with the possible text responses, let's call the list_name 'Q1_choices'. In the label column you'll write the text for each possible choice (so that's 5 lines of text in total, with the Q1_choices name repeated in the list_name column for every line). All you have to do now is to write 1,2,3,4 or 5 in the 'name' column as appropriate (one per line) which will be the score for the response.

In the survey sheet, you'll have as type 'select_one Q1_choices'. For the name column let's use 'Q1_response'. For label just type the text of your question.

Now to work out and show the score add another line in the survey sheet (before the 'end group' line). For the type column use 'integer', let's call the name column 'Q1_score', and in the label column you can write something like 'Your score is:'. Finally, and crucially, in the calculation column insert '${Q1_response}' (without quotation marks).

When you test the questionnaire in survey123 you should now see the score displayed. As you click through the different responses in the form you should see the score changing between 1 and 5 in real time.

This approach can be repeated for multiple questions, so you could have lists called Q2_choices, Q3_choices, etc all with 1,2,3,4 or 5 in the name column. The duplication of identical 'name' options between list_names doesn't seem to matter, the responses are all uniquely tied to their particular question.

Good luck, hope this makes sense!

RobertMarros
Occasional Contributor II

I had a question regarding using the regex commands. I have a survey where we are using a work order id to start the survey but we want to control what the user types in by using a constraint as well as have a default value set on the same field so it is always pre-populated with the letter "T" at the beginning. So, I have the default value set to the letter "T" and have the following formula written in the constraint :

regex(.,'[0-9]{10}')

The goal here was to allow the user to type in whatever numbers necessary after the letter "T" but they would be limited to 10 characters long including this letter "T". Every little alteration with these regex commands seems to publish fine with no errors but then doesn't work the way I need it to when testing it on the survey itself. Is there an issue with the default value being pre-populated already? I need the result to look like something like this "T101819654" but produce a message when the string is less or more than 10 characters long.

I also ran across the types that ESRI supports on the xls forms themselves and I see this:

re{ n, m}Matches at least n and at most m occurrences of preceding expression.

So, if I need to use the "re" where do I insert it to make my equation post successfully? Do I need anything additional to my equation above to make it work? It seems so simple and even had the same type of regex command working in an online tester but then realized that ESRI uses some slightly different syntax and now I am stuck. Any help with fixing my expression would be great!

Thanks, Jason Sphar

zkovacs
Occasional Contributor III

Hi Jason,

I think the regular expression you need would look like this:

regex(.,'T+[0-9]{9,9}')

{m,n} matches the preceding element at least m and not more than n times, so it should be {9,9} in your case as you need exactly 9 numbers after the letter 'T'. Mind you, in practice the form only seems to use the value for m and completely ignores n. It throws an error if you have less than 9 numbers after T, but lets you enter more than 9 as well. (Same issue with any combination, tried {3,4} and so on... )

It's the same with the example in the article for email addresses. Based on the expression regex(., '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}') , it should check that you have a proper email address ending @domain.xx, @domain.xxx or @domain.xxxx. However, it accepts anything after @ if you have at least 4 characters, like @mmmm which should throw an error. (Using Survey connect 1.5.35)

If this is not a bug, then the documentation needs some updating...

Zoltan

zkovacs
Occasional Contributor III

I had another think about it and you may be better off with using an input mask for that field instead of a regular expression. With that you can control the number of characters, whether they are letters or numbers etc. Have a look at "Input masks" in here. I hope it helps.

Zoltan

PabloRios
New Contributor III

Hi,

I have a select_one (cascade select) question that i use to hide or not subsequent question and it works fine but i have an issue because i have another question that i use to filter the same subsequent question like previous question that i described. I need to hide some questions using one or two conditions. it is possible? 

JamesTedrick
Esri Esteemed Contributor

Hi Pablo,

It is possible to use and/or/not in the relevant statement to generate more complex functions.  Given what you describe above, let's do a scenario around disaster preparation. Sometimes extra assistance is needed to evacuate; you want a question asking if extra assistance is needed when the household contains more than 8 people or there are people with significant mobility limitations.  The relevant statement for the third question would be:

${numPeople} > 8 or ${mobilityLimitations} = 'yes'

TerryBeutel
New Contributor II

Is it possible to "calculate" if an image question has been filled? ie has the photo been collected in the survey. I want to do this so I can use that as a condition for displaying a subsequent question.

IsmaelChivite
Esri Notable Contributor

Hi Terry.  Information in this thread may be useful:https://community.esri.com/message/662807-re-make-a-new-question-dependent-on-an-image-capture 

Essentially, the expression string-length(${imageQuestion})>0 evaluates to true only if a photo has been selected or taken. You can use the  expression in the relevant field.

SeanEdwards
New Contributor II

Great info Hannah. I just wanted to let you know the links for constraint, relevant, and calculations do not work. Thanks!

#survey123

JamesTedrick
Esri Esteemed Contributor

Hi Sean,

I've updated the links- we revised our documentation system a couple of months ago.

AndrewHargreaves2
Occasional Contributor III

@Ismael Chivite, @James Tedrick

How do I go about populating a value based on a previous select_one answer? Here's my set up:

  • Q1: Infrastructure type (select one) to equal 'residential meter'
  • Q2: Enclosure (select one) populate with the answer 'meter box'

Thanks

JamesTedrick
Esri Esteemed Contributor

Hi Andrew,

Calculations with select_one questions is coming with the 2.7 release of Survey123, which will be released in the near future.  You can test out your functions by grabbing an beta version in the Esri Early Adopter Community .

GISAdministrator22
New Contributor II

I have created surveys for street sign inspections and have collected about 7k sign poles with 13000 related signs (with groups and repeats). I am now working on another survey to collect the reflectivity of those signs.  The current form auto-populates most of the fields based upon the sign inspections collected previously and through the use of external choices and "pull data".  Is it possible to calculate values from those choices using a range?  What I am hoping to accomplish is populate a field with a condition based upon the reflectivity reading.  For example, a "white" "regulatory" sign would be considered in "fair" condition if the reflectivity reading fell between the range of 250 -500 and a "brown" "guide" sign would be in fair condition if the reading was between 25 - 40.   So the condition is determined by the results of the following fields 1) SignType 2) SignColor 3) Reflectivity (integer).  I am just trying to figure out how to incorporate ranges for different sign types and color into the equation to determine condition based upon the entered integer (reflectivity).  Hope that makes sense!  Any thoughts?

JamesTedrick
Esri Esteemed Contributor

Hi,

This should be possible.  I would probably find it easiest to set up a table of sign types and conditions with minimum values per condition level, import those with pulldata, and then use a series of nested if statements to find the appropriate level

table structure:

signtypemin_poormin_fairmin_good
white_regulatory10250501
brown_guide52541

The if statement would be something like:

if(${reflectivity_measurement} < ${min_fair}, 'poor', if(${reflectivity_measurement} < ${min_good}), 'fair', good))

GISAdministrator22
New Contributor II

Thanks for the quick response!  So...  here is my external table (SignReflect2.csv):

colormin_poormax_poormin_fairmax_fairmin_goodmax_good
Blue01920394060
Brown02425444555
FY0149150374375450
FYG0249250474475650
Green06970114115125
Red0495099100160
White0249250499500

750

How would I nest the following expression using SignColor and Reflectivity(integer) entries from Survey123 form? 

if(${Reflectivity} < ${min_fair}, 'poor'), if(${Reflectivity} < ${min_good}, 'fair'), if(${Reflectivity} > ${min_good}, 'good') 

When using the following pulldata parameters:

pulldata ('SignReflect2','min_fair','color',{SignColor})

pulldata ('SignReflect2','min_good','color',{SignColor})

or am I not approaching this right?  I appreciate any feedback you can provide as I may be over thinking this.

JamesTedrick
Esri Esteemed Contributor

Hi,

That looks pretty close to correct, it looks like it's a matter of getting the nesting correct.  A nested set of if statements will look correct if all (or nearly all, if more complex functions/evaluations are present) of the closing parenthesis are all bunched together - think of each if statement being an individual matryoshka doll:

if(${Reflectivity} < ${min_fair}, 'poor', if(${Reflectivity} < ${min_good}, 'fair', 'good'))

You also don't need the last evaluation since there is no other category beyond 'good'. Sentence-wise this can read as:

"If the reflectivity is less than the min_fair value, mark as poor; otherwise, look to see if reflectivity is less than the min_good value; if it is less than, mark as fair; otherwise, mark as good."

GISAdministrator22
New Contributor II

Thanks again.  One last question.  The min_fair, min_poor, etc is determined by the color.  The color answer from a previous question should be used to determine the min and max values for condition in the table above.  see below for description of statements:

if color is Blue and Reflectivity is less the min_fair then condition is poor
if color is Blue and Reflectivity is less then min_good and greater than max_poor then condition is fair
if color is Blue and Reflectivity is greater then min_good then condition is good

if color is Brown and Reflectivity is less the min_fair then condition is poor
if color is Brown and Reflectivity is less then min_good and greater than max_poor then condition is fair
if color is Brown and Reflectivity is greater then min_good then condition is good

etc.

Is it possible to use the pulldata function for multiple fields (min_poor, max_poor, min_fair, max_fair, etc.) using the external table above and then use the above "if" statement based upon the color (which is determined by a previous question in the form)?  I have not been able to get this to work but am sure I am missing something in the syntax.

JamesTedrick
Esri Esteemed Contributor

Yes, this should be possible.  The loading of values based on the color would be the job of the pulldata function.  One caveat is that values read from pulldata are treated as text by default, so you would need to convert to a number.  This can be done by setting the bind::type of the pulldata calculate questions to int or decimal.  Here's a quick mock up (blog comments can't add attachments):

GISAdministrator22
New Contributor II

Worked like a charm!!  Thanks a bunch!

EmilyCarman
New Contributor

I followed the same steps on a survey that I am working on, but I tried to do it with the select_multiple question type. My final calculation would only show up if a single answer was selected, not if I picked more than one. Do you know of any workarounds for this issue?

AliciaRitzenthaler
New Contributor III

Can or/and statements be used in constraint statements too? 
I have the survey questions 'Select the most abundant', 'Select the 2nd most abundant', and 'Select the 3rd most abundant'. I'd like to constrain the response to the 2nd and 3rd most abundant questions so that users cannot choose a response they selected for the previous question. 

.!=${mostabundant} seems to prevent the 2nd most abundant response from being the same as the most abundant answer.

.!=${mostabundant} or .!=${2ndabundant} however does not achieve the desired response; the 3rd most abundant can be the same as either the most or 2nd most abundant answer. 

RobertMarros
Occasional Contributor II

Is there a way to do this without using the name field? What if I need the name to populate the result in the backend? Can another field be added that can hold a value for an answer and then be able to add it up at the end? 

Thanks

ClaireProctor
Occasional Contributor

If you're using a select_one question, then in most cases (the most common exception being when using a cascading select) the label of the choice will be used in the results.

by Anonymous User
Not applicable

Hi Santokh,

Which pulldata() calculation is not working as there are 2 shown. For the first one I do not see the PR column in your CSV file screenshot, which should be the return column?

pulldata(<csvfile>, <returnColumn>, <lookupColumn>, <lookupValue>)

Are you able to provide a copy of your xlsx file and csv file so we can take a closer look.

Phil.

SantokhRandhawa
New Contributor II

Hi Phil,

I got it working.

Thanks for help.

Santokh

SantokhRandhawa
New Contributor II

Hi,

Is it possible to secure the feature layer for Survey123, so that data uploaded to ArcGIS cannot be changed? Any help will be appreciated.

Thanks,

Santokh

JamesTedrick
Esri Esteemed Contributor

Hi Santokh,

When you share a Survey123 form, you have the option to share it for viewing; this creates a read-only endpoint that can be used in applications.  See Share survey results—Survey123 for ArcGIS | ArcGIS for more information.  Additionally, you can restrict querying on the feature layer or feature layer view being used for submission (if the form is not being used to edit data) - see Manage hosted feature layers—ArcGIS  Help | ArcGIS for more information.

AndrewHargreaves2
Occasional Contributor III

Hi James,

The 'Manage Hosted feature layers' link was useful, especially when talking about 'Further control of edits for layers'. I'm wondering if making a small group as described in that section is my only answer to this question, posted in the ArcGIS ‌ group and maybe similar to what Santokh Randhawa was asking....

SantokhRandhawa
New Contributor II

Hi James, Thanks for reply. I would like to explain my question in a better way. When you are viewing your submitted data from Survey123 in ArcGIS , you can double tap on any field and you can change its value. Is there way to lock that edit option?

Santokh

JamesTedrick
Esri Esteemed Contributor

Hi Santokh,

Which product are you viewing the survey data in?  The table view of the Survey123's Data tab does not allow editing.  That being said, other tools (like ArcGIS 's Map Viewer table view) can let you edit the data; you should 'Disable editing' in the layer's properties if this is what you are referring to.

SantokhRandhawa
New Contributor II

Hi James,

I have inserted the image of data table. I did disable editing the layer in the settings. But still I was able to edit all data fields by double clicking on it.

Santokh

JamesTedrick
Esri Esteemed Contributor

Hi Santokh,

This is an issue with regard to ArcGIS ‌'s data view.  As the item owner, it may be expected that you would have edit access to the data- I would ask the ArcGIS  team for clarity on this.

SantokhRandhawa
New Contributor II

Hi James,

I thought about that and then checked it using the login of one of the group members but I was still able to edit the submitted data in ArcGIS  data view.

Another question I have is that is there a way to lock editing of sent surveys from Survey123 app. As of now I can click on "sent surveys" on the app and reopen it, edit and send again which will edit the  previously submitted data. Is there a way to lock it?

Thanks,

Santokh

JamesTedrick
Esri Esteemed Contributor

In terms of 'locking' from the Survey123 app, you can disable the Sent folder 

SantokhRandhawa
New Contributor II

Thanks James. That was helpful. How about my other question on blocking editing of ArcGIS online data? did you find something on that.

Thanks again for your help.

Santokh

MichaelFazio2
New Contributor II

Hi, I have a similar situation, but I have three conditions to choose from. Is it possible to have more than two conditions in an "if" statement? For more information please see my question: How to calculate a field based on multiple conditions?

by Anonymous User
Not applicable

Hi Michael,

Yes, you can have more than two conditions in an if statement if you use nested if statements, after each condition add a new if statement. An example can be seen here:

https://community.esri.com/thread/226367-collate-3-text-fields-into-1-field#comment-822052 

Hope this helps.

Phil.

by Anonymous User
Not applicable

Hello,

I am wanting to achieve a formula that will count how many questions have been answered no matter what the answer is and divide this by how many question there are. 

Basically i want to calculate the top row (GDPercentageComplete) with how many of the questions from GD1_1 to GD6_6 (except the comment/text fields) were answered (it does not matter what they were answered with) and divide this by the amount of question as i would like this as a percentage. 

JaimeSmith2412_0-1620040991725.png

Any help would be greatly appreciated.

 

Thanks,

 

Jaime

by Anonymous User
Not applicable

Hi @Anonymous User,

Yes this is possible using hidden questions to ensure each select one has a 1 or a 0 value and then add them all up, or you can do it in one calculation if yo uput all the if() in one calc and add them in that one calc.

If you add a hidden question after each select one question that has a if() calculation, to say if it is null, assign it 0, otherwise assign it 1. That will give that hidden question value of 1 or 0.

You can then add those questions up in a sum to know how many have been answered and then convert it to a percentage.

Or if you prefer not to add so many hidden questions, you can do it all in one question and just add them all together and then sum and convert to percent. Will be a bit more complex and harder to compile, but easier in the form with just one question with the calc.

Regards,

Phil.

 

 

by Anonymous User
Not applicable

Hi @Anonymous User 

 

Thank you for your input this has worked a treat. I have now published this onto AGOL and I'm getting NaN in the Percentage Complete field. Is there something I can do to fix this?

 

Thanks,

 

Jaime Smith

by Anonymous User
Not applicable

Hi @Anonymous User,

Are you using the survey in the web app or field app, where do you see the NaN?

It sounds as if there is a null value tried to be summed or used in mathmatical expression. Need to ensure every field that is used in the expression has a 0 or a real value, and not a null value.

If you can share your xlsx file I can take a closer look and get back to you.

Regards,

Phil.

by Anonymous User
Not applicable

Hi @Anonymous User,

I had a look at the survey you sent me. Are you referring to the expression in Percentage Complete field which is:

${Decimal2} div 4 *100 +"%"

This expression appears to be mathmatical and text concatination in one. This is not expected to work, either in field app or web app. If it does work in field app that is by chance more so then by design. You need to seperate this expression into two different expressions (use hidden and/or null field types), the first being the mathmatical expression to get the result, and then the second to concatenate that result with the % sign (string). Use the concat() expression to do this, not just a + sign. This will work in both field app and the web app. Then only show the note to the user, not the hidden fields.

Also note that your survey uses grid theme, however this is currently not supported in the web app.

Regards,

Phil.