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

39209
67
10-27-2016 02:48 PM
Esri Esteemed Contributor
13 67 39.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)
67 Comments
New Contributor III

Does the < lookupValue> (like ${species} in this example), a  must to be set to "Select one" question type? . I do have something similar but it does not work if the question type is set to text.  

Esri Esteemed Contributor

Hi Florent,

It will work when set to type text, but the lookup is sensitive to the precise value in the table - a free text field offers many possibilities of mistyping.  This is why I'm showing a select_one field - constraining the choices helps guarantee the lookup.

Occasional Contributor II

is there a way to incorporate the functionality of a lookup table with the pulldata function, based on two values entered...not just one?  I would need two lookupColumns and two lookupValues.

In my example, the data entered in two fields (#large wells -and- #small wells) will determine which MPN value to return to the form. The lookup table data is found here, https://www.idexx.com/pdf/en_us/water/qt97mpntable.pdf 

please advise, thx!

Regular Contributor

I wonder if you would be able to configure the file you are using for the pulldata function to contain all possible concatenation answers the two question answers and use an underscore to separate the two answers within your concatenation.

Meaning, if your two questions that are setup as select_one question formats, then you could possibly pass a concatenation of these two question through your pulldata function by applying a calculation.

An example would be if you have the following answers to ?1 - GIS, GPS, AutoCAD and answers to ?2 - Rocks or Rolls, then your answer combos would be - GIS_Rocks, GPS_Rocks, AutoCAD_Rocks, GIS_Rolls, GPS_Rolls, or AutoCAD_Rolls. It would be these answers that are passed through your pulldata function to supply your answer.

If you cannot pass a concatenation through a calculation, then you might be able to use a hidden field to hold the answer of the concatenation to your pulldata function.

Just thinking outside the box and I hope this works for you.

Mike

Occasional Contributor II

concat in a hidden field worked like a charm, thanks Mike!

Esri Esteemed Contributor

If I'm following along, another way to do that is the coalesce function - it provides the first non-empty result in two questions.

New Contributor III

Does the pulldata() function against external csv files only work in the field app?

Esri Esteemed Contributor

Hi Alek,

pulldata for accessing CSV values does work in the new web form.

Esri Esteemed Contributor

Hi Linda,

I'm not seeing any erroneous behavior with the sample.  Are you copying & pasting into a new sheet or loading the provided sheet into Survey123 Connect?

Esri Esteemed Contributor

Hi Linda,

Just to check, you've loaded both the provided sheet and placed the accompanying csv file (that was in the media folder) into the new survey's media folder?

Occasional Contributor

Can I use pulldata() to pre-populate Select_One questions?  The data I'm pulling consists of integer values (0 to 4) that match the name of the Select_One, however, the Select_One label is very descriptive and I'd like to retain that for the form.  I know I can pre-populate the fields if I set the type to integer, but that would lose a lot of the user experience.

I'm also able to set the default value to an integer, but I haven't figured out how to use a pulldata() from a CSV value as the defaults.

Thanks, 

Adam

Occasional Contributor

Just found a post that informed me this requires search () functionality which is not yet supported.  Hopefully it will be soon.

Cheers,

Adam

New Contributor

Hi, 

I am trying to do replicate this for an aquatic species survey and I cannot get it to autopopulate.

I've double checked errors and redone and republished again and again and nothing. 

Here is a screenshot of my csv file

and here is how the survey is set up

The SpCN_Name column corresponds to the name for the species_observed field, not the label which is how it is set up in the example file.

Any help on this would be much appreciated! Is it because the species scientific name has spaces? because I would in theory need spaces for this field

Esri Esteemed Contributor

Hi Savannah,

It's a little hard to debug from just the screenshots (the choices sheet would have helped), but one thing I noticed is that your default value is "No Plant" - is that a valid value (not label)?  Generally values should not have spaces (as the equivalent SpCN_Name in your csv does - it's "NoPlant").  Having an invalid default can lead to select_one questions to not behave correctly.

New Contributor

Hi James, 

I don't think I can attach the full workbook here but this is what the choices tab looks like. You're right that default is a label not a name, so I've changed that to "NoPlant" but the autofill still won't work for scientific name.

Esri Esteemed Contributor

Everything does look correct, assuming that underscores are being used in the pulldata question (cell borders make it hard to see that in the image). The only other things I could think of is that the csv file is in the main folder of the survey, not the media folder.

New Contributor III

Hi,

I am using pulldata function to grab string/text from the CSV file. it worked great until I realise some of the text has been truncated. Is there a text limitation to the pulldata? At the moment, it appears to cut off at length around 76 or so.When it does this, it added a 'double quotation' at the start of the string. The bizzarre thing is that the second half of this string is returned in the following command in place of the values from the referred lookup column - therefore, it is not returning the correct values from the lookup column. I am attaching a screen shot of what is shown on Survey123 form, a screen shot of the accompany CSV look-up table and my XLS form.

Screenshot of the columns referred to in the CSV fileSurvey form created where the pulldata strings were split and shifted down the rowsThe pulldata setup in the XLSX form

I am not sure what happened here or where I go wrong. I am unable to see why my form is doing this 'double quotation' quirky thingy and breaking up the string into parts and shifting the display to the following section below.

I am using Survey123 Connect v2.4.60

Thank you

Siew Wei

Esri Frequent Contributor

Hi Siew,

As discussed at the Holistic Testing Day in Melbourne the other week, the way you are trying to populate question labels using pulldata in a calculate field and display them in a note as the question is not supported, it is not best practice, nor a recommended way to build a feature service (database). Field names in Survey123 relate to the column names of the database and in Survey123 the label field is used as the field alias, which will be honoured in AGOL, Survey123 website, plus any other ArcGIS environment. By populating the label names as answers in a note from another excel file and writing them as field values (calculations), you are creating additional fields in the feature service and storing the labels as attributes, meaning there is no link between the column name and label name (questions), ie no aliases are being created.

Whilst I understand you were trying to do this to save time and to avoid having to type all the label names into the xls form, it would be advisable to set up the xls form in the supported way and copy and paste all of your question names into the label column, not as values in a note from a calculate field.

Regards,

Phil.

New Contributor III

Hi Phil,

Thank you for getting back to me. Yes, I understood what you had explained at the Holistic testing day and can see the issues with what I had attempted to implement here. Put aside what I had used here as an example. The question I have here is on why my pulldata in this particular occasion has a 'double quotation' which then appear to split my pulled values (string) into sections, and then displaying the remaining part of the strings into the next section (displacing the values from the pulldata of the next section). 

So irregardless of the reason, I am seeing weird responses/actions from the pulldata function. I need to understand what happened here as going forward, I will be extracting strings from CSV in developing other forms (and yes, they will not be questions like I had used in the example here).

Thank you very much.

Kind regards

Siew Wei

New Contributor III

Hi Phil,

Just testing if I am to respond to this email, will the content of my email here be posted to GeoNet… or that I should respond directly from GeoNet.

Cheers

SW

Esri Frequent Contributor

Yes replying to the email will post it directly to GeoNet.

Can you provide a copy of your XLS Form and the CSV file the pulldata() references. I need to look into it more closely.

You can either upload the files to this post (using advanced editing) or you can email them to me.

Phil.

New Contributor III

Got it! I have emailed you the files.

Cheers

SW

Esri Frequent Contributor

Hi Siew,

 

The reason you are seeing the issue with pulldata() not reading the entire string and displaying quotation marks (“) is that your CSV file contains commas (,) in the label text, which by default in a comma separated value file (CSV) will indicate the start/end of a column. Excel will get around this by adding quotations to the start and end of each string when commas are present, however the pulldata() function reads the entire string, including the quotations and starts a new column when it sees a comma.

 

To avoid this behaviour you will need to remove the commas in each field and save your file in Excel again, if there are no commas Excel will remove all the quotations. Once you have done this each question will display correctly in the note.

 

Below highlights what your pulldata() function is selecting:

Regards,

Phil.

New Contributor III

Hi Phil,

Thank you for the pointers! Another info to add to my knowledge pool!

Kind regards

Siew Wei

New Contributor II

Hi All - 

I have a similar question and it seems like it should be a simple fix.  I am using pulldata to populate a read only text field.  I have something like this:

I want the pulldata to loop up the right column and return all matching values in from the left column.  I keep getting only one value from the left returned.  Thoughts?

New Contributor II

Is there a way to pull data and feature it in a select_one drop down list?

Thanks,

Victoria

Occasional Contributor III

Hi Victoria. No, this is not currently supported. But you can replicate the field names of the select_one drop down list in the csv and pull data from it based on a select_one selection.

Occasional Contributor III

Hi Linda. Pulldata is only supported with 1-1 relationships. So if there is more than one column with the same value, it will only return the first one found.

New Contributor II

Savannah has an extra 's' in her call column in the pulldata command "SpeciesSscientificname" instead of "SpeciesScientificname".

Regular Contributor

Will the following scenario work:

the end user will input the street address into survey123 and I can use the pulldata() function to grab the parcel number associated with that address (a .csv file that has an address column and a parcel number column).

whats the file size limit to these tables? (like 35k address with 17k parcels)

Esri Esteemed Contributor

Hi Joe,

The process you outline could work, though there are a couple of concerns that I can think of:

- I would suggest a guided approach of address selection rather than freeform to prevent typos (i.e., select the street, select the street number)

- In your address/parcel table, you clearly have more addresses than parcels.  While I wouldn't see a problem with multiple addresses associated with a parcel (you still get the unique parcel ID), is there a situation where 1 address refers to multiple parcels (like this subdivision in College Park, MD):

It may be that as in this case, there's a 'master parcel' for tax reporting purposes to look up, but it could also be the case in undeveloped areas that two independent properties have the same address.

New Contributor III

Hi again Phil,

Now that I know commas have to be avoided in the csv file for pulldata function to work.. I now struggle with how to manage database containing multiple commas between strings which I need to pull into my form. An example of how the string data tends to look like is:

If I am to delete the commas between the numbers, it would made the entry very confusing . Is there any other way to deal with such strings with multiple commas? 

Apart from commas, will using semi-colon cause problems? or 'slashes' or "=" ? Those are also present in the data that I need to pull into my Survey123 form.

Hoping there is a way to work around the restriction with using commas :S

Thanks in advance

SW

Esri Frequent Contributor

Hi Siew,

Yes could could try using different characters instead of commas, however remember that any different special character you use may have impact in calculations or when displayed in app or online, so ensure to test thoroughly.

Note the issue is with using a CSV file with data in columns that contain commas, not so much pulldata(). The definition of CSV is Comma Separated Values, so if you have commas in your data/values, then the CSV separates them as different columns. To avoid this programs such as Excel use quotations around the commas, however this is not always supported, such as pulldata().

Phil.

Esri Frequent Contributor

Hi Siew,

I have raised this as a enhancement request in our issue backlog, to enable the ability to support quotations in CSV files, and added your use case and comments above to the issue.

Regards,

Phil.

New Contributor III

That will be great Philip. Thank you

New Contributor II

Thanks for the helpful blog! I used a .csv in the media folder to set up a pulldata() function using Survey123 Connect, and it worked perfectly in the Survey123... However, when I visit the online web form it does not seem to work. Are there extra steps to enable that pulldata() functionality in the web form? 

New Contributor II

For anyone else who runs into this problem, something was wrong with our .csv file. It worked again after creating an entirely new csv and pasting the original data into it. We still don't know what the original problem was, since both look the same in a text editor, but creating a new one from scratch solved the problem. 

New Contributor III

How would I configure the barcode scan to scan a barcode and bring the associated information from the barcode from a csv file into the survey and match fields from csv to survey?

Occasional Contributor III

Hi Trisha,

Something like this should do the trick:

typenamelabelhintcalculation
barcodeScannedBarcodeScan BarcodeOptions include 1000-1008, 2000-2008, 3000-3008 and 4000-4008
textCsvReference1Csv Reference 1pulldata('BarcodeInfo', 'Reference1', 'BarcodeId', ${ScannedBarcode})
textCsvReference2Csv Reference 2pulldata('BarcodeInfo', 'Reference2', 'BarcodeId', ${ScannedBarcode})

Whereby the .csv file is called BarcodeInfo.csv, is stored in the media folder, and contains BarcodeId, Reference1 and Reference2 fields as below.

BarcodeIdReference1Reference2
1000Spencer StFlinders St
1001Spencer StFlinders Ln
1002Spencer StCollins St
1003Spencer StLittle Collins St
1004Spencer StBourke St
1005Spencer StLittle Bourke St
1006Spencer StLonsdale St
1007Spencer StLittle Lonsdale St
1008Spencer StLa Trobe St

Kind regards,

Mikie

New Contributor III

I am unable to see the attached zip file you referenced in email.  This is what i have, does this look correct?

survey123

survey123

csv filecsv file

Esri Esteemed Contributor

Hi Trisha,

One thing to note - many of the columns in your CSV file appear to have spaces in the column header - they should be replaced with underscores to match how you've specified the information in the XLS form.  I'd also check for commas in the text columns of the CSV file - they will result in column mismatches.

New Contributor III

I would like to add to the above. I had a 'Note' section that I was pulling data from. The notes were copied and pasted from an online submission form. I figured out that I needed to remove all the commas, but I still had some strange behavior. It turned out to be 'carriage returns' or 'new line' characters, not all of which were easily seen in the data. A search and replace took care of the problem. I would suggest checking all the columns in a CSV file for commas and other special characters. It doesn't take that long and could save a lot of head scratching down the road. 

New Contributor II

Hello all, forgive me for posting a redundant question but I have been searching for solutions for days now and still can't get my pulldata function to even show a sign of working (i'd take an error at this point because it means I am on the right track). I have uploaded the attached csv file (no commas in there; no spaces in the headers) to the media folder of the attached form. I want to be able to select a company from the "Firm Name" field and return the "Firm ID" from the csv. Under the calculation for the "Firm ID" i have: pulldata('Firm_Info','Firm_ID','Firm_Name',${firmName}).

I am sure I am doing something obviously wrong but I can't figure it out at this point. Any advice is appreciated.

Firm Reporting Form

Firm_Info.csv

Occasional Contributor III

It looks like your issue is that the "Firm_Name" column in your lookup table has all the names with the first letter capitalized while the values for your "choices" tab "name" in column are all lowercase.

New Contributor II

Thank you so much Brent! I knew it would be something small and obvious. I was paying more attention to the label than the name. I am embarrassed at how many hours I lost to this. Thank you!!

New Contributor II

Hi

I had an issue with the survey123 pulldata, my csv file contains an ID and his  latitude, longitude coordinates, it’s a very large file with 289,500 registers (points), I try to use the Lat, Lon information in a geopoint to locate the ID. I tested with 10  points and It works very well, but when I try  to use the file with the 289,500 point the survey123 application stop worki and closed. What is the limit of registers in the csv file and what other way I can use to solve this.

Thank you.

Esri Esteemed Contributor

Hi Rodrigo,

The pulldata() function is not currently designed to handle extremely large lists at this time.  We are looking at methods to make data lookup more efficient in the future.

Occasional Contributor

How secure is the external CSV file in the media folder? I would like to use pulldata() to pre-populate answers in a private survey from a CSV made from previous community survey events.  For example, a user navigates their browser to the web form survey URL.  They are prompted to log in with their ArcGIS named user credentials. The survey form then loads in the browser and uses property("username") to find correct row in the CSV to use for prepopulating answers.  However it is very important that the entire CSV showing all users' previous survey results is not available to an individual survey user in any way.   

Esri Regular Contributor

Hi John,

Thanks for raising the question.

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.

I will raise your concern to the team for discussion to see if we can come with an enhancement. Before that, I would like to check with your use case, do you want to restrict the logged in user only can access the records submitted by themselves in a previous survey? Or the logged in user can also access other records submitted in the previous survey by other users, or even the survey will be shared with the public but you still do not want to share the .csv data with anonymous users. The first scenario may make sense from the implementation perspective but the latter two will be hard or even impossible.

Thanks,

Zhifang

Occasional Contributor

Hi Zhifang -

My use case is to restrict the logged in user to only access the record submitted by themselves in their previous survey submission.

About the Author
I work on the Survey123 team.