Survey123 Tricks of the Trade: pulldata("@layer")
The pulldata("@layer") function was introduced with Survey123 version 3.16 (October 2022). It allows you to query an ArcGIS layer and retrieve data from it. In this blog, I will explore common scenarios for this function and describe design best practices and limitations.
A bit of context first
Querying ArcGIS layers using XLSForm expressions is not a new concept. Back in the day, I described how to do it using custom JavaScript functions. Using custom JS functions gives you great flexibility, but you need to know how to write JavaScript, and most importantly, you can't execute a custom JS function if you share a survey publicly.
The new pulldata("@layer") function simplifies the syntax for querying ArcGIS layers, and it can be used in public surveys too!
Point-in-polygon calculations using a map
The animation below shows a typical scenario for pulldata("@layer"). A calculation takes the location set in the map, and triggers a point-in-polygon query to retrieve a value from the intersecting polygon; a parcel's APN (Assessor Parcel Number) in this case.
I can think of a gazillion cases where point-in-polygon queries will be handy... just think of the polygon layers you may have: parcels, soils, fire perimeters, sales territories, evacuation zones, city boundaries...
Point-in-polygon calculations using an address location
Point-in-polygon queries do not strictly require a map in your form: They require a location and a polygon layer. In the example below, the respondent enters an address, and a point-in-polygon query is triggered to automatically calculate the City Code that applies to that location. In this case, the location for your point-in-polygon query does not come from a map, but an address. The City Code comes from the feature layer targeted in your point-in-polygon query.
Point-in-polygon calculations will help you make your forms geographically smarter. You can choose to display the geo-calculated values in the form, or keep them hidden.
Point-in-polygon queries for data validation
You can use the output of pulldata("@layer") to calculate a value, like in the examples above, or to feed a data validation rule. In the next animation, a point-in-polygon query checks if a reported incident falls within a City of Redlands public park. If it does, the report is accepted. If it does not, the user will not be able to submit the form.
Again, we are essentially doing a point-in-polygon query using pulldata("@layer"). In this case, the output of the query is used to check the integrity of the data.
Calculate values using an attribute query
In the example below the respondent enters a customer number and the form automatically populates contact information about that customer. That is all done through pulldata("@layer"): The customer number is used to find a record in the customer layer. If found, the name and email attributes are populated.
Customers, assets, buildings, parts... You can query any ArcGIS layer or table as long as, of course, the user completing the form has access to the layer or table.
Count records in a table or layer
Another common use case is that in which you want a survey to stop accepting responses once a certain number of records are submitted. This is also something pulldata("@layer") can do for you. Check out the next screenshot: Through a query to the signup table, we can tell how many people already signed up. A constraint in the form uses this value to allow (or not allow) another person to submit the survey.
Now that we have reviewed some common scenarios, we are going to get hands on...
Getting started with pulldata("@layer")
If you have read this far, you will be eager to start. The pulldata("@layer") syntax is described in this help topic. Have a good read of this topic as it describes the basics very well.
Point-in-polygon query to retrieve a single attribute: pulldata("@layer", "getValueAt")
This three-minute video shows, step-by-step, how to construct a simple point-in-polygon query using the World Administrative Divisions layer from ArcGIS Online. If you want to follow along, make sure you have Survey123 Connect version 3.16 or newer.
For your reference, you will find the getValueAt.xlsx file attached at the bottom of this post.
The getValueAt_Constraint.xlsx file shows how you can use the calculated value from the point-in-polygon query to ensure the location chosen by the user falls within a polygon.
Point-in-polygon query to retrieve multiple attributes: pulldata("@layer", "getRecordAt")
Another three-minute step-by-step video building on the previous one. In this case, the getRecordAt operation is used to do a point-in-polygon query and get an entire record. Using the pulldata("@json") function, the record is parsed locally to get multiple attributes efficiently.
Find the getRecordAt.xlsx file in the attachments section below for reference.
Refining point-in-polygon queries using extra parameters
On top of the well defined parameters you can pass to the getRecord and getRecordAt operations, you can also pass extra parameters to the feature layer url to perform some more advanced queries. For example:
- distance: This parameter allows you to buffer the input geometry (geopoint) to find polygons within a given distance. By default, the distance units come in meters, but you can also specify other units. For example, the following expression will apply a buffer of 1600 feet to the provided location (geopoint) and find any intersecting polygons in the critical habitats layer.
pulldata("@layer", "getRecordAt", "https://services.arcgis.com/QVENGdaPbd4LUkLV/ArcGIS/rest/services/USFWS_Critical_Habitat/FeatureServ...?distance=1600&units=esriSRUnit_Foot", ${location})
I highlighted in blue the key part where the distance and units are specified. These extra parameters are added to the URL. You need to add a question mark (?) to start adding extra parameters and then separate them with an ampersand symbol (&).
- gdbVersion: This parameter is useful if you want to query a versioned multi-user geodatabase feature layer.
- orderByFields: If you expect your query to return more than one value, you can use this parameter to sort the results. For example, say you use getRecord to get all tickets submitted by a user. You can use orderByFields to sort all tickets by date in descending order. This will make your getRecord operation to give you back the latest ticket submitted.
- outStatistics and groupByFieldsForStatistics: Ideal when you want to retrieve stats from your layer, rather than a unique record or value. This is how, for example, you will want to get a count of records in a layer.
Check the Query (Feature layer) help topic to learn more about all the extra parameters you can use. The Request Parameters section in this help topic describes the parameters that you cannot use.
Advanced techniques with pulldata("@layer")
Survey123 Connect includes a sample called Query a Feature Layer. Have a look at it, as it illustrates some advanced techniques you can leverage to gather statistics and construct dynamic queries.
A few extra tips and things you should know
- Public surveys: Unlike custom JS functions, you can use pulldata("@layer") with public surveys.
- No offline support: The pulldata("@layer") function only works while online. This makes it particularly useful for web surveys, although if your device is connected, it will also work in the Survey123 field app.
- Support starts with version 3.16: If you go into Survey123 Connect and you can't even get the samples to work, check your Connect version. You need 3.16 or newer for this function to work in Connect. All surveys published with version 3.16 or newer will work in the web app. For the field app, ensure users have the latest version of Survey123 installed on their devices. Otherwise, your pulldata("@layer") function will be ignored.
- You do not need to add a layer to a map before you query it: I think this one should be obvious already, but I will add it just in case. You do not need to add a layer to your survey map before you can query it. In fact, you do not need a map in your survey to do a query. All you need to do is to pass the URL of your layer to the function.
- About tokens: When using pulldata("@layer") you do not need to worry about tokens. Since the token of the signed in user is passed automatically to the function.
- concat() is your friend: When composing a SQL WHERE filter, use the concat() function. For example:
- Good: concat("MANHOLE_ID='", ${question1},"'")
- Bad: MANHOLE_ID='${question1}'
- SQL tips: Where statements can sometimes be tricky.
- Quote if you work with a text field: concat("MANHOLE_ID='", ${question1},"'")
- Do not quote if numeric: concat("MANHOLE_ID=", ${question1})
- What is the SQL to query records in the last 15 days? Check the solution here.
- Layer sharing: Make sure the layers you query are appropriately shared. For example, you cannot expect a private layer to be available in a public survey. Do not dream of Survey123 magically letting you query a layer the signed in user does not have access to. If Survey123 cannot access a layer you reference in pulldata("@layer"), the output of the function will be empty. The user will not see an error or warning.
- Layer capabilities: The layers you query need to be queryable. This may seem obvious, but it may catch you out. If the data you are querying is sensitive, consider the use of feature layer views to restrict access to fields and rows as appropriate.
- pulldata("@layer") is not for populating a list: The purpose of pulldata("@layer") is to help you query a layer and get back either one record, or one value (attribute) from a record. If you want to query a layer to populate a list, use the search appearance instead as described in this blog post by @BrettS or this other blog post on geolists.
- If you need a bit more guidance, you can check this video with a few demos and step by step instructions.
More info in video
A one hour session covering pulldata("@layer") was recorded some time ago. More info here.