Firstly I would recommend looking at these two pages:
https://community.esri.com/t5/arcgis-survey123-blog/survey123-tricks-of-the-trade-pulldata-quot-laye...
https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_BB97985...
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", "<URL>", "<WHERE clause>")
- pulldata("@layer", "getValue", "<JSON property>", "<URL>", "<WHERE clause>")
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.
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.
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.
If the user entered ABCDEF into the licence_plate question the query would read as:
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.
