Querying AGOL feature service by date range in ArcGIS Pro

[deleted-user-ka73FBRGJUOO]

I have a feature service that is being populated by our field workers using Survey123.

I have created a map series in ArcGIS Pro 2.2.0 that references said feature service, with the goal of creating a multi-page report, using dynamic text and images.

Each week I want to export a PDF of the prior week's entries.  The map series, naturally, is cumulative...with a page for every single point created since the very first one.

Is there a way to apply a definition query to the feature service so that it only shows the most recent week's worth of points?  I've tried a few different approaches but have hit a brick wall.  The feature service has both the standard CreationDate and EditDate fields, as well as a user-inputted date field.

[JoshuaBixby]

Wait until ArcGIS Pro 2.3 and try again.

#BUG-000111737 Applying Select By Attributes on date fields of hosted feature services fails in ArcGIS Pro.

• Status:  Implemented
• Version Fixed:  2.3

[JoshuaBixby]

I just got an e-mail from Esri this morning saying the status of this bug has changed to "Not in Current Product Plan."  This is a new one for me, not the actual status, but having a bug that is already fixed somehow changing to not going to be fixed.  Here is Esri's explanation:

This is a known limitation. To avoid this, we would recommend a best practice of using the BETWEEN SQL operator to perform the query using SQL rather than using ‘=’ in the query builder. This will ensure accuracy and reliability. For more infomration, please contact Support Services. Use the example below adding a second before and after the time you are querying for, ensuring that the first timestamp is precedes the second one. last_edited_date BETWEEN TIMESTAMP '2018-09-24 20:10:53' AND TIMESTAMP '2018-09-24 20:10:55'

[KoryKramer]

Hi Joshua.  You're right that the incorrect status led to confusion - sorry, but it looks like there was simply a mix up in marking the status the first time.  It was incorrectly marked as In Product Plan.

The latest update and explanation is correct.

[TimothyMichael]

Kory, 

Is the syntax different if querying a feature service coming from ArcGIS Enterprise?  I tried the recommended BETWEEN syntax in ArcGIS Pro and get a message that there was an error with the expression.  

I was able to get a simpler query to work (DATERECEIVED BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 12:59:59') querying against the REST endpoint directly, but that doesn't work in Pro either.  

I have standardized queries disabled for ArcGIS Server.

Thanks,

Tim

[KoryKramer]

I was seeing what you're seeing when working with a hosted feature service from ArcGIS Online, setting a definition query in Pro, when the ArcGIS Online organization wasn't using the standardized SQL only security setting.  With that setting checked on for the organization, the query works.  I logged a bug [BUG-000117318: Querying on a date field of a hosted feature service fails when the ArcGIS Online organization security setting to ‘Allow only standard SQL queries’ is unchecked.]  From your results, looks like it isn't specific to ArcGIS Online.

Can you enable standardized SQL queries on the server and try in Pro again?  If you're able to say, is there a business need for standardized queries to be disabled?

[TimothyMichael]

Kory, 

Thank you for the reply.  I reset the standardized queries setting to true and still get the same error in ArcGIS Pro.  I tried both versions of the query I posted in the last post, with and without 'TIMESTAMP.'

The reason why I originally disabled standardized queries was to test taking certain features offline with Collector.  For the same project, I have a need to have users take features offline that meet date-based criteria (i.e. some action needs to be taken 'today').  On the ArcGIS Enterprise / Server side I first applied a definition query to the layer (TESTRESULT <> 'New' AND SCHEDULEDDATE LIKE CONCAT(CONVERT(date,GETDATE()), '%') in ArcGIS Pro and then published the service.  This seemed to work OK for the service, but Collector would display an error when trying to take the data offline - I think the error referenced the Sync Service.  As a test I disabled the standardized queries setting and re-tested the sync in Collector, which worked, somewhat - the features were available in the offline map but changes made to those features would not synchronize back to the service.

Let me know if there's anything else I can try.  Thanks!

--Tim

[TimothyMichael]

Kory, 

My mistake - I forgot to restart the ArcGIS Server service after updating the standardized queries setting.  I restarted it and now the query I posted in my original screenshot is working correctly.  Now I need to figure out what to do about the offline piece...

Thanks,

Tim

[AndrewHargreaves2]

Is it possible to use the #select layer by attribute‌ in #arcgis pro 2.3‌ to select records created today? I'm trying 

CreationDate = timestamp NOW()

But getting an error.

[JoshuaBixby]

It is always helpful to post the entire error message contents.