Survey123 Pulldata from Layer Help

[SaadShahzad]

Hi all,

I'm trying to create a note in my survey that queries previous submission records in the feature layer to notify the survey-taker of an existing inspection record within the last 24 hours, I am trying to use this pulldata function in the calculate field to achieve it, but it does not seem to work, and permanently shows "Forklift has no inspections in the last 24 hours" - despite an existing inspection within the last 24 hours.

forklift_number is an integer field in my survey that holds the unique ID of the forklift

inspection_date is a dateTime field in my survey that holds the inspection date and time for a forklift

if(
  pulldata("@layer", "https://gis.blank.com/server/rest/services/Hosted/service_0d41872c64f34120b9881d67843aaes2/FeatureServer/0", "count", "forklift_number = '${forklift_number}' AND inspection_date >= timestamp(now() - 86400000)") > 0,
  "Forklift was last inspected in the last 24 hours.",
  "Forklift has no inspections in the last 24 hours."
)

Any help troubleshooting will be greatly appreciated. 

[DougBrowning]

Common issue that pulldata does not work inside an if statement.  Break it out to its own field then run the if on that field.

[SaadShahzad]

Hmm, okay so I separated it and I broke it down to get to the bottom of the issue, but still can't seem to even get this simplified pulldata (without the check for date and time) to work: 

pulldata("@layer", "https://gis.blank.com/server/rest/services/Hosted/service_0d41822d64f34120b9884d67848aaef2/FeatureServer/0", "count", "forklift_number = '${forklift_number}' " )

Something tells me I don't know how to use the "count" clause properly. I don't get any errors, but I don't get any values returned either. Shouldn't it return an integer value back with the number of records that exist with the forklift number I plugged into the survey? 

Example: I have two records in the feature layer with the Forklift Number: "123" associated to them, shouldn't the pulldata request return a count of 2?

[DougBrowning]

Looks like you are missing the getValue  part?

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_BB97985... 

[SaadShahzad]

Hi Doug,

Thanks for your help so far. I changed some stuff up and took another approach utilizing some of the information I found on the forum and docs.

What I'm trying to achieve now (before I attempt adding a "last 24 hours" clause) is to query the count of Forklift Inspections associated with a Forklift Number in my survey (which is an integer field). This pulldata request seems to be returning the entire count of the records in my layer, so it doesn't seem like my 'where' clause is working correctly. Any tips?

pulldata("@layer", "getValue", "attributes.forkliftInspections",  concat("https://gis.blank.com/server/rest/services/Hosted/service_0b41872c64f34120b9884d67848aaef2/FeatureServer/0", '?outStatistics=[{"statisticType": "count","onStatisticField": "forklift_number","outStatisticFieldName": "forkliftInspections"}]', "&where=forklift_number=", ${forklift_number}, "&t=",now()))

Thanks.

[DougBrowning]

Not sure as I have not done it but it seems to be missing a quote around the value.
maybe  
"&where=forklift_number='", ${forklift_number}, "'&t=",

[SaadShahzad]

No luck, unfortunately. Even adding a hardcoded value in place of the survey field (forklift_number) doesn't seem to return the results I'm looking for.

[DougBrowning · Accepted solution]

In the samples I do not see it using the word where?  It is a comma then the query.

pulldata("@layer", "getValue", "attributes.NAME", "https://services5.arcgis.com/jMCHJcLe13FaKCFB/arcgis/rest/services/US_Counties/FeatureServer/1?order... DESC&resultOffset=9", "STATE_NAME = 'California'")

[SaadShahzad]

Hi Doug, so that was part of the issue and then there were a few other things I had to change. But it works great now! Thanks again.

Here's the working expression for anyone that might be trying to achieve the same:

pulldata("@layer", "getValue", "attributes.forkliftInspections", concat("https://gis.blank.com/server/rest/services/Hosted/service_0b41572c64f34120b9584d67848aaef2/FeatureServer/0", '?outStatistics=[{"statisticType": "count","onStatisticField": "forklift_number","outStatisticFieldName": "forkliftInspections"}]'), concat("forklift_number=", ${forklift_number}) , "&t=", now())

Now to figure out the last 24 hours clause to so that it only returns the count of forklift inspections in the last 24 hours for a specific forklift number.....