Dynamic Date Filter

Document created by jskinner-esristaff Employee on May 5, 2016Last modified by jskinner-esristaff Employee on Jun 1, 2017
Version 3Show Document
  • View in full screen mode

Users have the need to apply a dynamic date filter to a feature layer within their web map .  For example, they may need to show all features that were edited on the current day.  Instead of having to manually update the filter in the web map each day, the below steps will walk you through how to have this date update automatically each day.

 

Note:  Before proceeding, you will want to make sure 'Allow only standard SQL queries' is unchecked within My Organization > Edit Settings > Security tab:

 

Screen1.PNG

 

Steps:

1.  Within your web map, apply a filter to your layer:

Screen1.PNG

2.  Save your web map and use a utility such as GeoJobe Admin Tools or AGOL Assistant to edit the JSON.  In the below example, the AGOL Assistant is used.  After signing into the AGOL Assistant using your AGOL credentials, click 'I want to' and choose 'View an Item's JSON':

Screen2.PNG

 

3.  Navigate to and select the web map you saved previously.  On the right scroll until you see Data and find the layer that has the filter (definitionExpression) applied:

Screen3.PNG

 

4.  Click the pencil at the top right to begin editing.  You will then want to update the definitionExpression using a SQL function (i.e. GETDATE()).  In the below example, by subtracting 1 and adding 1 to the GETDATE function will subtract/add one day.  Also, the CONVERT function is used to truncate the time from the date.  This is to ensure you are getting the entire day rather than a 24 hour period.  So, the expression will filter all features between yesterday and tomorrow (i.e today).

 

Screen2.PNG

 

NOTE:  The definition expression will be relevant to the geodatabase you are using.  The above will work for a hosted ArcGIS Online feature service.  However, if you are using a hosted service in the ArcGIS Datastore (which stores the data in a PostgreSQL database) or a feature service consuming data from Oracle, the definition expression would be:

 

"last_edited_date BETWEEN (CURRENT_DATE - 1) and (CURRENT_DATE + 1)"

 

For SQL Server, the definition expression would be:

 

"last_edited_date BETWEEN (CURRENT_TIMESTAMP - 1) and (CURRENT_TIMESTAMP + 1)"

 

5.  Save your changes by clicking the save option at the top right.

 

6.  Go back to ArcGIS Online and re-open your web map by going back to 'My Content' and selecting to open the web map in the ArcGIS Online Viewer.  If you click on the Filter option, the expression should be blank under both the View and Edit tabs:

Screen5.PNG

 

The filter will be dynamic, so as each day passes the filter updates with no user interaction.

Attachments

    Outcomes