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.
| code | label | a | b |
|---|
| BCF | Blue catfish | 0.000001553 | 3.278 |
| BCR | Black crappie | 0.000015959 | 2.968 |
| BLG | Bluegill | 0.000003592 | 3.304 |
| BRB | Brown bullhead | 0.000063387 | 2.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:
| type | name | label | appearance | readonly | default | calculation |
|---|
| text | station | Station Location | | | | |
| date | date | Date Sampled | | | today() | |
| begin repeat | fish_repeat | Fish Data | | | | |
| select_one species | species | Choose Species | minimal | | | |
| decimal | length | Individual length | | | | |
| decimal | weight | Measured weight | | | | |
| note | a | Value for a: | | | | pulldata('FishCalc', 'a', 'code', ${species}) |
| note | b | Value for b: | | | | pulldata('FishCalc', 'b', 'code', ${species}) |
| note | stdWeight | Standard Weight: | | | | round(${a} * pow(${length}, ${b}),3) |
| decimal | showkn | Weight / Standard Weight | | yes | | ${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!
