Making Other Work Harder in Survey123 Results

3695
16
12-11-2017 01:03 PM
Regular Contributor
6 16 3,695

Survey123 is a very popular ArcGIS App for the Field and I'm always looking for new ways to work with it - to make sure it's delivering value for my users. Previously I've looked at applying a custom Other field and in this blog I investigate making survey results easier to analyse, or symbolise, when a survey includes the Other functionality.

If you're not aware, the Other functionality allows users to enter an Other value as an answer to a multiple choice question, if the answer they need is not in the list. This has been implemented for the question What is the property type?:


This functionality is awesome, but the Other value that a user inputs is assigned to a new field in the attribute table. This means it can be hard to analyse and query this question later on because the answers for one question are spread over two fields:

Blog 1 v2.png

Here's a method to collate these answers to one field, making it easier for end users to view and query the data. So instead of the answers looking like the above example, they will look like this:

Blog 3 v2.PNG

If you need some help setting up a Survey123 survey, check out the help  videos and  pages. This blog assumes you have a survey set up using the Survey123 Connect for ArcGIS desktop application with at least one  select_one type question.

The blog is split into 2 sections to cover different methods used to implement Other

  • If you are using the or_other method, look at Section 1.
  • If you are manually adding Other as a choice option, look at Section 2 
  • If you are working with choice names that include spaces, step 5 in Section 2 covers this.

Please note: the methods described below will only work in the Survey123 survey. If you edit the data in the ArcGIS Online map viewer or in Collector for ArcGIS, the changes won't automatically be reflected. 

Section 1

1)  Browse to the select_one question you have set up with or_other:

In the background, Survey123 creates a new field to hold the Other value entered by the end user. The name of this field is usually the name of the select_one field followed by ‘_other’. In the example above, the new field created for the is called PropertyType_other (you can also find this field name by looking in the Schema Preview in the Survey123 Connect for ArcGIS desktop application).

2)  Create a new text question under the select_one question (this is for testing, later this field will be of type hidden). Name and label it something like this:

3)  Now you need to create an if statement to determine what value is written to this new field. This if statement will be entered into the calculation field. The if statement for this survey will be:

if(${PropertyType}='other', ${PropertyType_other}, ${PropertyType})

It’s important that 'other' is all lowercase as this is what Survey123 will recognise in this case.

In simple terms: if the PropertyType question answer is ‘other’, then assign the PropertyType_other value to this field. Otherwise assign the PropertyType value to this field.

The survey will look like this:

4)  Save the Excel spreadsheet and preview the functionality in Survey123 Connect:

Or_other collate.gif

5)  When you’re happy with the survey, head back to Excel and change the type of question to hidden for PropertyTypeCollated:

This means that this question will be hidden from end users in the Survey123 app, but will be available for you later on. So you can display it in a pop up or use it as a field for analysis/querying.

**Hidden fields are not currently supported in the Survey123 website (http://survey123.arcgis.com) but has been added as an enhancement request. 

6)  Your survey is now ready to be published and used on a mobile device.

Section 2

1)  Browse to the select_one question you have set up with Other as a choice option:

Blog 19.png

2)  Create a new text question under the select_one question. This will be the text field to hold the Other - SurveyType answer. Name and label it something like this:

3)  Now you need to create an if statement to determine what value is written to this new field. This if statement will be entered into the calculation field. The if statement for this survey will be:

if(${SurveyType}='Other', ${SurveyTypeOther}, ${SurveyType})

This Other is looking at the name column in the choice list.
In simple terms: if the SurveyType question answer is ‘Other’, then assign the SurveyTypeOther value to this field. Otherwise assign the SurveyType value to this field.
The survey will look like this:
4)  Save the Excel spreadsheet and preview the functionality in Survey123 Connect:
Other collate.gif
5)  You may notice above that if you choose Health and Safety as the answer, the SurveyTypeCollated field has the text HealthandSafety. This is because Survey123 pulls the text from the name column and not the label column:
Blog 12.png

We can expand the current if statement to compensate for choices that include spaces/characters by nesting the if statements:

if(${SurveyType}='HealthandSafety', 'Health and Safety', if(${SurveyType}='Other', ${SurveyTypeOther}, ${SurveyType}))

In simple terms: if the SurveyType question answer is ‘HealthandSafety’, then assign ‘Health and Safety’ to this field, if the SurveyType question answer is ‘Other’, then assign the SurveyTypeOther value to this field. Otherwise assign the SurveyType value to this field.

More information about nested if statements can be found here but please note that Survey123 does not need the statements to begin with '='.

6)  Save the Excel spreadsheet and preview the functionality in Survey123 Connect.

7)  When you’re happy with the survey, head back to Excel and change the type of question to hidden for SurveyTypeCollated:

This means that this question will be hidden from end users in the Survey123 app but will be available for you later on. so you can display it in a pop up or use it as a field for analysis/querying.

**Hidden fields are not currently supported in the Survey123 website (http://survey123.arcgis.com) but has been added as an enhancement request. 

😎  Your survey is now ready to be published and used on a mobile device.

Collating answers to one field will make creating queries, filters, applying symbology and running analysis easier!

This post was originally shared on the Esri UK blog: GeoXchange — Esri UK. It's also a valuable source for posts on other ArcGIS topics by other Esri employees, check it out!

16 Comments
New Contributor III

Very nice!  I have a question that is perhaps related.  If I have 3 survey questions/fields and the combination of answers from those 3 fields means something that I want to capture in a separate field for use in pop-ups and queries ... how would I set up the calculation?  As a simple example, lets say that you run a grocery store and the cashiers do not have the PLU codes memorized.  So you set up a few questions to get to them to an answer ... the questions are 1) Type of food? (and "fruit" is a choice that leads to the next question), 2) Color of fruit? (and "yellow" is a choice), 3) Type of yellow fruit? (and "banana" and "lemon" are choices).  How could I then tell the survey that the answer of "banana" from the third question should populate a hidden PLU_Code field with "4011" while the answer of "lemon" should populate a hidden "PLU_Code" field with "4595"?  

Let's say I also have a fourth question that is triggered by the answer of "banana" in question #3.  What is the weight of the banana in ounces (integer)?  How could I tell the survey to take the answer to question 4 and put it in a "FoodWeight" field?  Thanks!!     

Regular Contributor

Hi Ashley, 

It sounds like the Pull Data function might be able to help you - this is where a csv of values is used to load the survey with answers. 

Here's more information: 

Formulas—Survey123 for ArcGIS | ArcGIS 

Survey123 for ArcGIS – XLSForm: Enabling CSV Data into your Survey - YouTube 

https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-da... 

For your example, your questions could be set like this:

Choices could be set like this:

The Pull Data .csv could be populated like this and saved in the media folder of your survey e.g. C:\Users\<username>\ArcGIS\My Survey Designs\<surveyname>\media :

When the user chooses Lemon, the code 4595 is filled to the hidden field called PLU_Code.

For the next section, a hidden field is created to hold the answers from the weight of Banana/Lemon etc. and a calculation is used populate it:

If you want to keep the field type to an integer, make sure to use the bind::esri:fieldType column. 

Hope that helps!

Carmel

New Contributor III

Thank you!  I have a follow-up question ... let's say your csv file is the same as your example, but also includes PLU codes for all fruits of all colors.  If the next fruit encountered is a strawberry then how do I take the ${RedFruit} from my survey, use the csv with the codes for all fruits, and have the PLU code for this second piece of fruit added to the same PLU_Code field that already contains lemon for the first piece of fruit in the example above?  In the end, I want a single PLU_Code field that captures the PLU code for any fruit that is encountered in my survey.  Thanks again!!     

Regular Contributor

Hi Ashley, 

That's a good questions and took a lot of brain power on the last day before the holidays!

I managed to do it incorporating Repeats into the survey (some caveats covered at the end):

The user runs through the set of questions as many times as they need to complete the survey:

Then I used Aggregate Functions to sum the values coming from the Yellow_PLU_Code & Red_PLU_Code fields:

For example, if I run through the repeat and select lemon and apple, the values wrote to the field are:

Caveats: this works well if the user only chooses one of each fruit colour. If they choose 2 of the same fruit colour, (lemon and banana), the codes are just concatenated with no punctuation:

Hopefully this is something you might be able to build upon yourself - another method is suggested by the user GIS Officer here: https://community.esri.com/message/675570-re-calculating-a-final-total-from-repeats-survey123?commen...  

Carmel

Occasional Contributor III

Carmel Connolly / Ismael Chivite‌ / James Tedrick instead of creating an "_other" field for every question is there a way to push these all into a single "notes" field?

Or alternatively is there a way to have a single "notes" field appear under each question if the value chosen is "other"?

Or is it possible to have a note populated with those questions answered "other" used a instruction for what must be entered into a single "notes" field?

Thanks

Esri Esteemed Contributor

Hi Andrew,

This should be possible - the notes field could be either permanently relevant or have a large number of 'or' clauses to show if any question has an 'other'; you would need to provide instruction to the person filling the form on how to document which response is which in the form.  In that case, you wouldn't use the "or_other" keyword in the question and would have a 'other' value specified in the choice lists

To consolidate a variety of 'other' answers, you would just have a note/field calculate based on the '_other' field responses using concat() - this could include a prefix to label each value - i.e., concat("1. ", ${q1_other}, "\n2. ", ${q2_other},...)

Occasional Contributor III

Thanks James Tedrick‌ - one more questions related to 'concat': is it possible to concatenate several values together with formatting in between. I'm looking to replicate the effect of CHAR(10) as described here to insert a carriage return.

Thanks

Esri Esteemed Contributor

Hi Andrew,

The carriage return was what I was trying to accomplish with the '\n' - that's the escape sequence for a carriage return.

New Contributor III

Hi There ,

I've been trying to make this set of instructions work with little success. I'm using Survey123 Connect v. 3.7.60.

I've attempted to use both sections 1 & 2 (separately) to replace the initial select_one question with the following "other" question. The flaw I'm finding is that even though a collated column is being created and supplying either the selected answer or the typed in "other" answer, there is no formula/logic to replace the original select_one question with the collated answer.

How do I go about doing this? From everything I've tried, the above doesn't actually work yet.

Regular Contributor

Hi Drew,

Thank you for the information, it might be the case that Survey123 works a little different now to 2 years ago! Can you email me your Survey123 spreadsheet and I'll have a look? cconnolly@esriuk.com

Carmel

New Contributor III

Hi Carmel,

Thank you for your willingness to help! However, about an hour after I submitted my question I came up with this work-around:

type                               name              label                  relevant            calculation            bind::esri:fieldType

select_one fixture_type  field_13          Fixture Type                                                                                     null

text                                field_13_other Specify Other    ${field_13}='Other                                                 null

hidden                           Fixture_Type    Collated                    if(${field_13}='Other', ${field_13_other}, ${field_13})

Ultimately, I created two questions for the surveyor to fill out that are "null" in the end-product, while the answer that actually shows up on the spreadsheet when exported (but is hidden for the surveyor) is the collated question that I titled "Fixture_Type".

Again, thank you very much for responding so quickly and for being willing to help!

-Drew

Regular Contributor

Excellent! Thanks for sharing your solution for others

Carmel

New Contributor

Is it possible to only upload the collated fields? Since hidden fields are not visible to the website, the space-saving potential is lost. 

How about I create a separate hosted feature service that is written on by the select collated fields of my survey? Is that possible?

Occasional Contributor II

Hidden fields are visible.  The null in bind esri type is what causes them not to upload.  If you want them just remove that null.

Hope that helps

New Contributor

Is there a way to have this work with a multiple_select question? I have a Multi_select question ask for names with a specify_other question. If I select only "other" on the multi_select and then type a name into the specify_other question, it works fine. However, because of the calculation formula, if I select a name with other in the multi_select and then type something into the specify_other question, the hidden question with show "other" and the name I selected rather than the name I typed and name I selected.

Esri Esteemed Contributor

Hi Ricky,

Coalescing a multiple choice with an 'other' question is a bit more difficult, as the choices selected are stored in a comma-separated list; you would essentially need to find 'other' and replace it with the value in the other question.  This will be possible using JavaScript in the future.

About the Author
GIS Consultant at Esri UK