Use existing data in your survey - the pulldata( ) function

86237
86
10-27-2016 02:48 PM
JamesTedrick
Esri Esteemed Contributor
20 86 86.2K

With the 1.7 release of Survey123 for ArcGIS, we introduced a function that makes it easy to access data that is external to the survey- pulldata(). Using this function, can have a set of reference information distributed with the survey, making it easy for your respondents to enter information that doesn't change very often or is used as a baseline data point in your survey.

Preparing your data

The pulldata() function relies on your external data being stored in a CSV file and uploaded as part of your survey. This means that you can easily generate the data file from a wide variety of tools, including Microsoft Excel and ArcGIS Desktop. Place the CSV file in the media folder, the same place where all external files (video, audio) go.

Using pulldata() in your form

The full function and its parameters are pulldata(<csvfile>, <returnColumn>, <lookupColumn>, <lookupValue>).

  • csvfile is the name of the external data file without the .csv ending.  As mentioned above, it is stored in the media folder of the survey
  • returnColumn is the column in the CSV file with the value you want to be entered into the form.
  • lookupColumn is the column in the CSV file with the value you have from the form to find the correct row
  • lookupValue is the value in form that exists in the CSV file to find the correct row.

Example: Fish Size calculation

A common task in wildlife management is to conduct a survey of plant and animal specimens. Management of fish, in particular, factors in the size and weight of the fish to determine maturity and capture potential. The 'standard weight' of a fish can be roughly modeled against its length using a density function. The function is given as: 

Weight = a*Length^b.

We can compare this weight against the actual weight to provide a quick evaluation of fish health; under 80% of standard weight may indicate the fish is under stress.

The values of a and b vary between fish species. Rather than embed a set value for all fish in the survey itself, we can use an external data to store the values of a and b for each fish species, and then read the values using pulldata.

To implement this, we first have a table that stores the a and b values, along with an identifier for the fish.

codelabelab
BCFBlue catfish0.0000015533.278
BCRBlack crappie0.0000159592.968
BLGBluegill0.0000035923.304
BRBBrown bullhead0.0000633872.711

FishCalc.csv - table of standard weight parameters 


In our form, we then use the pulldata function to retrieve the values. We could use the pulldata function directly in the calculation, but for clarity, I'll use separate note questions to retrieve the values and then use them in a third question that calculates the weight:

typenamelabelappearancereadonlydefaultcalculation
textstationStation Location
datedateDate Sampledtoday()
begin repeatfish_repeatFish Data
select_one speciesspeciesChoose Speciesminimal
decimallengthIndividual length
decimalweightMeasured weight
noteaValue for a:pulldata('FishCalc', 'a', 'code', ${species})
notebValue for b:pulldata('FishCalc', 'b', 'code', ${species})
notestdWeightStandard Weight:round(${a} * pow(${length}, ${b}),3)
decimalshowknWeight / Standard Weightyes${weight} div ${stdWeight}
end repeat

FishEntry.xls - sample XLS form that uses pulldata to calculate standard weight for fish

The full form template can be downloaded to try. Thank you to the Georgia Department of Natural Resources, who provided the original values used in this form.


What type of data is a good fit for pulldata()?

The data used by pulldata() is published at the same time as the form. Because of this, the data used in pulldata functions shouldn't change too frequently. In the above example of estimating fish weight, this information would rarely change, if ever. The data can be updated by republishing the form with new data; but the field workers then need to update the forms on their devices. This suggests that the data that is used should be updated less than the typical cycle that the field workers would update the form. If you have your field workers check in every morning, for example, data that is updated once a day or less would be appropriate.

More uses for pulldata()

The example in this blog used the CSV file to store variables in a function; other similar uses include being able to estimate crop yield based on crop or seed type and acreage or total allowable development based on land use zoning and approximate lot dimensions. Another type of survey that benefits greatly from pulldata is a periodic inspection, such as bridge, hydrant or household address verification.  The CSV file can contain the data from the last time the inspection and then filled into the format a baseline for the updated inspection.  Some operations, such as post-disaster recovery, may include multiple rounds of surveys- a preliminary damage assessment, a more thorough building assessment after the immediate disaster, and post-disaster followups.  The data from previous surveys can accompany subsequent rounds of surveys as reference and a consistency check for the surveyors.  In addition to calculations, the pulldata function can also be used in a constraint to validate the value matches (or is within a range of) the existing value.

Learning more

The Survey123 for ArcGIS help has a section describing the use of pulldata.

We've also posted a tutorial video on CSV file and pulldata use in the Survey123 for ArcGIS playlist...  

If you have any additional questions, please ask in the Survey123 for ArcGIS community in GeoNet!

Tags (1)
86 Comments
ZhifangWang
Esri Regular Contributor

Hi John,

Thanks for the information, which is helpful for designing the workflow. I'll also log it in our backlog too.

Thanks,

Zhifang

JenAmes
New Contributor III

I have created 2 surveys over the last couple months and tried to use the PullData function to populate a geopoint question, and to fill in a notes field based on data in a csv in the media folder. In Survey123 Connect, I do not receive any errors during the publishing process, but once I try to access that survey online, I receive an error that renders the survey useless.

I can close the error message, but the survey does not populate the way it should and will not let me submit anything. It functions fine in the Survey123 app, but I'm trying to move everyone away from using the app. Web based surveys are much easier for the frequency with which the surveys are updated, rather than trying to make sure everyone who uses the app updates the survey every time I republish the survey. Is it known why the PullData function doesn't work for newly published web forms? Older surveys that use the PullData function don't seem to have a problem, only newly published surveys.

by Anonymous User
Not applicable

Hi Jen,

Can you please provide a copy of the survey form xlsx file and also the csv files used for pulldata() in media folder. We will need to take a closer look at the exact pulldata() expression you are using.

Phil.

JenAmes
New Contributor III

Phil,

Attached are links to Dropbox files for the survey form and the media folder csv.

Survey Questions

Media Folder CSV

Semi-unrelated, but is there a way to directly upload a file to my comment? I feel like I've seen it done before, but I can't figure out how to do it, hence the Dropbox links.

by Anonymous User
Not applicable

Thanks Jen,

I will take a look into this further next week.

As for adding attachments to comments in the posts, you need to open the reply in a new window, not from the Inbox view, and then also open the advanced editor from the top right corner of the reply window.

Phil.

by Anonymous User
Not applicable

Hi Jen,

The first download link you provided to survey Questions does not work, it just links back to GeoNet?

The Media Folder CSV link did work and I was able to download from Dropbox.

Phil.

JenAmes
New Contributor III

That's weird, I'm sorry. Here is the real link. Hopefully.

Parks Equipment Survey

by Anonymous User
Not applicable

Hi Jen,

Thanks for sharing the files. I was able to find the problem. There are a few issues with the csv file you are trying to use for pulldata and it contains a few things not supported by the web app as well as invalid CSV format. See screenshot below.

The CSV file has a # in the name of one of the columns (Serial#), this is not supported in the web app and should be avoided for any field name or column names. You will need to remove this from CSV and the pulldata calculation in xslx file.

There is an extra column in the CSV that has no data, this can be seen by the additional comma at the end of each row. This needs to be removed as a CSV should not have a comma at end of each row.

There is also an extra line at the end of the CSV that is blank. If you open the CSV in a text editor (as below) such as NotePad++, you will see the raw data.

How did you create the CSV, some software will add these extra things that are not needed in a CSV?

Once the CSV is fixed it should look like this and will work in the web app:

Web app using same survey with fixed CSV file:

Hope this helps.

Regards,

Phil.

JenAmes
New Contributor III

Phil,

Thank you so much, I would have never figured out the extra rows and columns thing. I just did a save as csv from an Excel doc, so I must have deleted data but not the column/row. I was also unaware of that the # won't work. I've made the changes and published the survey and we're good to go now! Thank you!

StevenDel_Castillo
New Contributor III

Hi Philip,

Has there been an update to the enhancement? Perhaps support for xlsx instead of csv?

Thanks,

Steven

by Anonymous User
Not applicable

Is there a way to do a select multiple that is populated by a pulldata?  To add on, how could you then iterate through the results to pull additional data.  For instance, once you have selected Site A, the building list populates with the five buildings at the site.  Then it pulls in the building code for each of the buildings selected automatically with a pulldata() call?

EduardoC_
New Contributor II

I configured the pulldata csv in my survey and I made the survey public so everyone can access it with a link. But I need the data in the CSV to be private, given that it is confidential.

When I open the developer tools and I open the link of my survey, I see that the CSV file is transmitted from the portal to the browser, and then, everyone can have access to this information.

What can I do in order to be able to use the pull data capability while maintaining the CSV file private?

Thanks.

JamesTedrick
Esri Esteemed Contributor

Hi Matthew,

Pulldata only returns 1 value.  It can be used to populate a multiple choice question, but only with the 1 value.  An alternative would be to have a repeat section that uses pulldata to retrieve a value; you could then use the join() function to calculate a list of values into the multiple choice.

JamesTedrick
Esri Esteemed Contributor

Hi Eduardo,

From your description, you have a fundamental issue in sharing - if the form needs to be shared with everyone, than the CSV file will also need to be shared to everyone; otherwise the pulldata function cannot work.

JulienLacroix
New Contributor

Hi,

I'm wondering if it is possible to populate a drop down list in my survey with field names (or simply data) of a database table. I see two possibilities using the pulldata function:

  • Export a csv of the table and call the pulldata function on it;
  • Call a JS file with pulldata() that query my database.

However, in both cases I have to call this function as many times as there are attributes or data to display... Is there a way to do it in a 'easy' way?

Thanks.

EDIT: it seems that calling a JS file that query the database is actually not possible with pulldata function. I'm then wondering if AppStudio for  would not be a solution to do that?

AllanLambert
New Contributor III

Hello,

Very helpful blog and posts. I'm about to send a survey to around 100 citizens who will not have the Survey app or an AGOL acct.  I've observed that a lot of the formatting (Settings: Style, background and box colors, xlsform appearance w4, w2) does not work when viewed via the url link to the survey.  Is pulldata also on the list of what won't work using the url?  pulldata("@geopoint",${location},"reversegeocode.address.Match_addr")  Is there a post or blog you can point me to that discusses this? Thank you.  Allan

ZhifangWang
Esri Regular Contributor

Hi Allan Lambert‌,

It is a known limitation that the theme-grid cannot work in the Survey123 web app for now (mentioned in Appearance—ArcGIS Survey123 | Documentation and https://community.esri.com/groups/survey123/blog/2019/11/20/survey123-tricks-of-the-trade-groups-gri... ). There is an enhancement request raised by other users via Esri Support Service (ENH-000130822) for this, contacting the support service could help prioritize this enhancement.

pulldata reversegeocode should work normally in the Survey123 web app. If you encounter an issue, discuss in https://community.esri.com/groups/survey123/blog/2018/07/06/understanding-reverse-geocoding-in-surve... or raise a ticket to Esri Support Service is welcomed.

In addition, Quick reference—ArcGIS Survey123 | Documentation  may help on understanding known differences between the Survey123 field app and the Survey123 web app.

BrittanyBurson
Occasional Contributor III

Hi @ZhifangWang, apologies but it doesn't seem possible for me to reply directly to your comment on 05-17-2019 about the pulldata .csv file being unsecured.

You said, "Currently, the entire .csv file used by pulldata is shared together with the form item, so it means anyone can access the survey (form item) can download/get the .csv file technically, either through the web app or the field app."

Is this still the case at present version? Thank you

ZhifangWang
Esri Regular Contributor

Hi @BrittanyBurson ,

If you use the pulldata syntax from a .csv file, yes, it is still the case that the user can access the entire data of the .csv file technically.

But there could be a chance to enhance the security concern by using the new Dynamic choice lists approach: setting users can only query their own records in the feature layer. This feature is still in beta, any feedback is welcomed to the forum in Survey123 Beta Community.

DataOfficer
Occasional Contributor III

Hi @ZhifangWang ,

Can you give a bit more detail about how using the dynamic choice lists approach can add security? For the query to work, would it not be necessary to make the feature service available to everyone using the form, therefore exposing the complete dataset as is done with the csv approach? We are in a situation where we would like to restrict access to an external csv list (or similar) based on Group membership.

As a possible workaround, could it be possible to do the following to link to multiple views (restricting access only to the lists that those users are entitled to):

  1. Create a single survey form which would be shared to multiple user groups that we would want to restrict the choice list to
  2. Have a field which recognises the user login and populates a hidden text field via a standard pulldata function with the service URL for their particular view of the feature service (presumably though this would require every username to be listed against a group membership which would require some management)
  3. Use the service URL from step 2 in the select_one question to access the appropriate feature service

Using the Search_Fire_State example, 

  1. Create multiple views of the Wildfire layer, relevant to each user group
  2. User A logs in and the hidden text field (${hiddenFieldNamepulls in the search parameters associated to their group membership from an external csv (e.g. "Fire?url=https://services3.arcgis.com/T4QMspbfLg3qTGWY/ArcGIS/rest/services/Public_Wildfire_Perimeters_View/F...", "startswith", "UNITID", ${us_state} )
  3. The select_one Fire question (irwinid) apperance field contains something like
    1. autocomplete search(${hiddenFieldName)
    2. However, I am not sure it is possible to pull in a text string from another field to the appearance field???

Many thanks.

by Anonymous User
Not applicable

Hi there,

I am wanting to use the PullData function in my Survey. I have a select_one question which the auditor picks the company, i then have four select_one questions which is related to each of the company employees and the auditor can select the name of employee from the company they have originally selected. I am wanting to populate the contact details of the employee but currently PullData only supports a 1-1 relationship. Is there a work around so that it can pull from the four select_one questions if a certain company is selected from the first question? 

Please let me know if there is a simpler way to the formatting of my questions. 

 

JaimeSmith2412_0-1617121227010.png

Thanks!

MichaelBruening
Occasional Contributor

@Anonymous User In the past I have been able to solve this by utilizing a concat() within the pulldata request. For talking points;

you have two questions that are being used within the selection of the parameters – Question A and Question B that are both string-based answers.

Question A’s answer would be utilized within a “choice_filter” to limit the answers of “Question B” by creating a new column within your choices tab named “QuestionA_Filter”. Within the question’s “choice_filter” there would be the need to insert “QuestionA_Filter=${QuestionA}”.

These two question’s answers can be combined to create a unique answer combination of “Answer A” and “Answer B”. These answers are derived from two separate choices lists.

By using an underscore within the concat() you will produce “AnswerA_AnswerB” as a unique answer to both questions.

There will be a need to generate all possible answer combinations within the CSV file that is being used within the pulldata() for your “key lookup field” and we will call this “QuestionA_PlusQuestionB”.

What this does is give you one answer that will be passed into a CSV file that has the “Email”, “PhoneNumber”, “JobTitle”, or other values that can be utilized within the pulldata().

The calculation of the concat() would look like this below to pull the email of person selected from a company -

pulldata('StaffDetails','Email',’ QuestionA_PlusQuestionB ‘,(concat(${QuestionA},'_',${QuestionB})))

The calculation of the concat() would like this below to pull the “PhoneNumber” of the person selected from a company –

pulldata('StaffDetails','PhoneNumber',’ QuestionA_PlusQuestionB ‘,(concat(${QuestionA},'_',${QuestionB})))

I hope this helps you out.

by Anonymous User
Not applicable

Thanks @MichaelBruening. This is extremely helpful and will use going forward!

novakc
by
New Contributor II

I am going to use Survey 123 for a scavenger hunt: Phase 2 Prototype here: https://experience.arcgis.com/experience/1f8a1aec22a64e9eaf57023b6a5655f6

People will register via my website: https://tlcgis.org/history/Events/Live-Well-Leon-Scavenger-Hunt/Leon-County-Registration I can export the registration data as a .CSV file.  I was thinking about using the pull data function for the team names to semi automate this.  Is there a way to have the media file reference a url (not have the data just a url to the .CSV file ) so I it would be easier to update?

ZhifangWang
Esri Regular Contributor

@novakc ,

Have you checked the linked CSV capability? This probably can help your workflow.

novakc
by
New Contributor II

No I haven't.  I will now, thank you so much!  I'll let you know if it works. 

novakc
by
New Contributor II

@ZhifangWang 

Hi.

I tried the linked .CSV capability and I tried Excels Power Query.  Neither seem to allow for an auto update

External Choices: 

.CSV on my web server

  1. Place a .CSV file that will communicate with the data on the server in the media file of Survey 123--Populates!
  2. Have the file in the media folder auto update from the data on the web:
    1. The spreadsheet is Querying the web file (power query)--works when I manually hit refresh
    2. ​It seems to lose the connection when Survey 123 is updated so it won't auto update. (My content management system can kick out a .CSV to my server)
    3.  Possibly the problem: https://answers.microsoft.com/en-us/msoffice/forum/all/excel-queries-and-connections-data-does-not-u... 

Survey 123 Linked Content from AGOL hosted . CSV

  1. Upload .CSV file to AGOL and used linked content via Survey 123 Connect
  2. Make update via AGOL (upload updated .CSV)
  3. Go back to Survey 123 Connect and refresh the linked content. so this also won't auto update.​

I appreciate you pointing me in this direction.  I'm trying to automate the registration and it's looking like the best I can do is  semi automate it (still an improvement over last year).

EmilyCaruccio
New Contributor III

I am struggling to create a geopoint with the pulldata function from two separate columns from a csv *linked content) in ArcGIS Online. I've tried a few different formulas and am coming up short. Would you be able to assist?

My form

CSV 

Within the calculation field in Survey123 connect, I put pulldata('fileforsurvey123', 'Y', 'X' 'LicenseID', ${facility_name}) but that does not work. Do I use some sort of concatenation for the latitude and longitude? I appreciate any assistance.

EmilyCaruccio
New Contributor III

Hi @Jcordovafredes, thanks for bringing that to my attention. I updated for form link.

You are correct with your understanding. I want to use the coordinates from the external csv to create my geopoint for use in a web map. The coordinates are in two separate columns within the external csv, named X and Y.

codergrl
New Contributor III
EmilyCaruccio
New Contributor III

@codergrl I have not! 

codergrl
New Contributor III

@EmilyCaruccio I also found this which may be helpful https://community.esri.com/t5/arcgis-survey123-questions/survey123-calculating-geopoint-from-pull-da...

Based on that, you may want to make the geopoint readonly so the GPS doesn't overwrite it ... or use the ONCE function around the pulldata

EmilyCaruccio
New Contributor III

@codergrl  & @Jcordovafredes  Through the link that @codergrl  shared, I found the article: https://community.esri.com/t5/arcgis-survey123-
blog/survey123-tricks-of-the-trade-working-with-uk/ba...
 which writes out exactly what I'm looking to do. Thanks so much for your help!

Within the geopoint question, I added concat(number(${Y}),' ',number(${X})) to the calculation column

HollyWithrow
New Contributor

I would like to add that you have to add your look-up value in Choices worksheet in the Survey123 document.  Although using a select_one question type might be obvious to this - it wasn't for me and it took me a couple of hours to figure this piece out.

Also, whatever you list in the choices sheet as your lookup value that ties back to your .csv, is how the data will be stored in your hosted feature layer.

i.e.  If in your choices sheet, you use a number to reference a site name (in the label), it will store the number in the site name field in your hosted feature layer when they submit the survey, instead of the site name label (which is probably what you want).

example:

list_name      name         label

name                1             Hidden Valley Resort

When they select Hidden Valley Resort in the drop down - it publishes the number 1 as the Site Name.

EhsanKafai
Esri Contributor

Hi @JamesTedrick ,

Thank you for this fantastic post.

I was just wondering if there is any way to pull data from two columns instead of just one.

Regards,

Ehsan 

 

CourtneyMarneweck
New Contributor

@JamesTedrick can pulldata() now be used to create a select_one dropdown list?

About the Author
I work on the Survey123 team. Please note that I do not use the private messages feature in Esri Community; if you need to reach me, feel free to e-mail jtedrick@esri.com .