How to pulldata from other columns in a choice list?

7442
7
10-23-2017 07:37 AM
BledarBirbo
New Contributor

Hi.

In our survey we need to populate the next 2 fields automatically based on one field ( select_one asset ) dropdown.

If the choices sheet, the asset list has 2 extra columns 'asset_id' and 'asset_type' populated with other data. 

Anyone knows what is the correct formula to be place on each of the next fields for pulling the extra column information  based on the first choice automatically ?

Thanks.

Reply
0 Kudos
7 Replies
JamesTedrick
Esri Esteemed Contributor

Hi Bledar,

As your title indicates, this can be done using the pulldata() function.  I'd suggesting looking at https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-da...  and Formulas—Survey123 for ArcGIS | ArcGIS to get started with this function.

Reply
0 Kudos
BledarBirbo
New Contributor

Hi James.

I cannot find any examples on how to use the pulldata() function for getting columns values extracted from the lists in the same form. sheet (choices)

All the current samples show how to pulldata from external CSV files but that is not our requirement.

Reply
0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Bledar,

Unfortunately, you can't use pulldata in the manner you describe- it's not designed to work with additional values in a choice list.  You would need to save it as a CSV in addition to the choice list for pulldata to work.

Reply
0 Kudos
TasmanianIrrigation
Occasional Contributor

I have done this successfully using 'external selects'.  Instead of using the 'Choices' tab in your spreadsheet, create a copy of that tab called 'external_choices'.  Then in your survey, instead of using 'select_one' question type, use the 'select_one_external' question type.  This uses selection lists form your new 'external_choices' instead of from the usual 'Choices'.*

The purpose of the external selects is for performance reasons.  One of the ways it increases performance is that it saves this 'external_choices' to a separate 'itemsets.csv' file in the survey's media folder automatically for you.  Of course, once it is in the survey's media folder, it is available for pulldata()  🙂

For more information on external selects, see:

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformcascadingselects.htm

*The caveat of this method is in the note of the page linked to above.  That is, external selects are designed for use with cascading selects, and therefore the itemsets.csv file will only include lists which are specified with a filter in the survey.  Ie, this method will only work if your select is a cascading select.

Reply
0 Kudos
MikeBruening1
Regular Contributor

It would be better to use the pulldata() function using a csv file to set this up. Here is something that we use in order to Pull all of our project detail information while using the Survey123 form within the field.

You can see from the field form we have 5 fields that are populated by one question being answered.

As you can see the makeup of the pulldata() uses the name of the csv, the column header from the csv that your data you are attempting to pull, then the Question from the form that it is looking for data entry to be made, and then it is pulling the answer to the question and using it to cross-reference the csv file.

Here is the csv file structure that is used to pull data from the csv file into the question above.

In your instance it would replace the following within this function - pulldata('ProjectInfo','ProjectName','ProjectNumber',${ProjectNumber})

'ProjectInfo' would be equal to your "Asset.csv" file that contains all the answers for question "asset" additional data for the 'asset_id' and 'asset_type' columns.

'ProjectName' would be equal to the "asset_id" answer from the csv file; in this case 'asset_id'.

'ProjectNumber' would be equal to the "asset" question" name from your form; in this case let's use "asset"

${ProjectNumber} would be equal to the "asset" question answer that would be found within the csv file.

S0, your CSV file would look like this and contain all possible assets with their corresponding asset_id and asset_type values and you need to save into the Media folder of your main survey folder.

IT took me a few tries, but once, you get it...it opens up a lot of doors for pre-filling of survey forms

Good Luck,

Mike

JanelleLeza
New Contributor II

You have the most helpful explanation i have seen for pulldata() from a CSV however my survey still isn't auto filling. Would you be able to help me try to figure it out? I have posed the question a few times on my profile or I am happy to open a dialog in messages with any additional information you may need. 

Reply
0 Kudos
SunilGhimire1
New Contributor

Hey Janelle,

Were you able to pull data from other columns to the Choice list inside the same survey? I am also having the same issue. If you have any proven workaround for that it would be so much helpful. Thank you.

Reply
0 Kudos