Using the pulldata @layer functions

Question

So i have reached my end with this. I am trying to build a Survey for Overnight Parking restrictions in one of our towns. The ordinance does not allow for overnight parking. However, you can register a vehicle up to 6 times per calendar month. I'm trying to create the survey so that it will reject any entry after that sixth registration by using the @layer function to query existing license_plate records in the feature service on AGOL. I keep getting an error on my expressions and I cannot figure out why. Attached is my xls form if anyone wants to take a stab at it. I would certainly appreciate it.

Overnight Parking.xlsx

scaleError.png

ChristopherCounsell · Answer

Firstly I would recommend looking at these two pages: https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-pulldata-quot-layer/ba-p/1224415 https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_BB97985CAFB740EE8AEF99EFD6E238E3 There are sample surveys and formulas that help show what the formula should look like in different scenarios. Looking at your current expression I believe there are a few issues with how it's formatted, mixing literal string values with references to survey fields, among other things. Starting with the basis of the pulldata expressions you have: * pulldata("@layer", "getRecord", "", "") * pulldata("@layer", "getValue", "", "", "") The first step is to add choose if we are getting a single record or a value. We are looking for a value not individual records. So we'll use the second expression above and our first two parameters are done!: * pulldata("@layer", "getValue", The second step is to add the third parameter. We are looking for a count of results. So instead of using length(), let's get a count directly within the expression. * pulldata("@layer", "getValue", "attributes.TotalCount", The fourth parameter is to specify the URL of the service. You'll need to supply this one and ensure it's shared to your end users. Don't forget to include the layer number at the end. * pulldata("@layer", "getValue", "attributes.TotalCount","https://services5.arcgis.com/abBCDeDBCBDd/arcgis/rest/services/MyserviceName/FeatureServer/1" However you'll be including some url parameters here for statistics. So as part of the third parameter, add some more to the URL. As we're counting results, we just need to hit a field that'll be populated, so ObjectID or FID is fine. If you wanted to sum a field make sure you specify the right statistic field. * pulldata("@layer", "getValue", "attributes.TotalCount", "https://services5.arcgis.com/abBCDeDBCBDd/arcgis/rest/services/MyserviceName/FeatureServer/1?outStatistics=[{"statisticType": "count","onStatisticField": "ObjectID","outStatisticFieldName": "totalCount"}]", The 5th parameter is where you'll build the query for your feature service. If you want all results, it's just "1=1". You can build it within the pulldata() or in another Survey123 row then reference it, but the end result needs to be a SQL query string like "1=1". So concat() is your friend if you want to combine string values and refer to survey123 questions. e.g. * pulldata("@layer", "getValue", "attributes.TotalCount", "https://services5.arcgis.com/abBCDeDBCBDd/arcgis/rest/services/MyserviceName/FeatureServer/1?outStatistics=[{"statisticType": "count","onStatisticField": "ObjectID","outStatisticFieldName": "totalCount"}]", concat("license_plate='",${licence_plate},"'") If the user entered ABCDEF into the licence_plate question the query would read as: * license_plate = 'ABCDEF' Note that you need to include the ' in the query, so it's important to include it within the concatenated "string values" as above. The final step is to include the month criteria within the query. This one's a little tricky as you're probably using a dateTime field. It looks like you calculate this with Today() which only returns date, but it may still store time in UTC and you may need to accommodate this in the query. For Australians, that 11 hour UTC offset could give the wrong results or month. What I'd recommend doing is taking a look at your data and constructing the query in the rest directory. Go to the Service REST API > Query. In the parameters set: * Where: parking_date >= date '2024-12-01 00:00:00' * Out Fields: parking_date This will return requests where the parking_date value is equal to or greater than the first of december. Take a look at the results. Even though you've been calculating Today(), is there an hour set? The dateTime fields are stored by default in UTC. So a record of 'Today()' as 12/19/2024 may be stored as 12/18/2024 01:00:00 PM. To accommodate this we can update the query to offset for our current UTC offset of 11 hours: * Where: parking_date >= date '2024-12-01 00:00:00' - INTERVAL '11' HOUR This sets the date as the start of the month, but takes off the +11 hours of Australia to query correctly. Try building your expression here at the REST endpoint instead of inside Survey123. Once you have this working you need to include it in your pulldata() where clause. Also test your licence plate query. I don't believe you need to convert the values to upper case. Upper isn't supported in Survey123 but can be passed through into the SQL Query, if it's supported, but I don't think you'll need it. Here's an expression that gets today's date at yyyy-mm and then adds the extra text we need: concat("license_plate='",${licence_plate}, "'AND parking_date >= date", format-date(now(),'%Y-%m'), "-01 00:00:00' - INTERVAL '11' HOUR") Combine it all together and here is your pulldata() expression. I'd suggest using a note field instead of calculate so you can see results. * pulldata("@layer", "getValue", "attributes.TotalCount", concat("https://services5.arcgis.com/abBCDeDBCBDd/arcgis/rest/services/MyserviceName/FeatureServer/1", '?outStatistics=[{"statisticType": "count","onStatisticField": "ObjectID","outStatisticFieldName": "totalCount"}]'), concat("license_plate='",${licence_plate}, "'AND parking_date >= date '", format-date(now(),'%Y-%m'), "-01 00:00:00' - INTERVAL '11' HOUR","&t=",now())) You will need to adjust the interval for UTC accordingly, noting the above is for Australia at +11 UTC. Or completely revise it if you're using a date only field. Again, pay close attention to the positioning of ' in the query so that it is valid SQL within the concatenation of string values. The final bit to include &t=now() in the query is just to ensure the response isn't cached on the app. From here your pulldata should either return a null value or a count of values. If you have issues handling the null values try using coalesce(${count_parkings},0). This will return the count, or 0 if no count is there. Try and unpack the above step by step. Try it with a "1=1" query parameter first. I haven't tested this so there may be some syntax issues... let me know how you go or if you need any clarification.

dougbrowning · Answer

First thing I would try is to move pulldata to its own field all by itself.  You have it wrapped in a length() now and it really does not like to be nested.  This gets posted a ton so that could be it.  You can then use this field inside of length() instead.  Start there.