Community

Select & display most recent record

8537
11
04-06-2020 09:05 AM
CaseyTeske2
by
New Contributor II
‎04-06-2020 09:05 AM

I have a feature layer data set that captures records from a Survey. There are hundreds of fire station locations that are static. There are many records for a single station location (ex: each fire station submits an update every day or so-- the updates may not be regular. One station may report daily, another may report weekly).

We are trying to create a web-app or dashboard that uses the data ONLY for the most recent record submitted by each location (bonus if it will automatically re-query & update according to a specified schedule). Using 'filter by today' will not work because an update for a station may have happened a week ago, and I still need to show the information for that record along with a station that updated this morning. I'm looking for help on a solution to show: 'for each individual station, select the most recent record from MAX DATE and use fields from that record for that station in the web-app (or dashboard)'....

For example: from the screen capture, I'd like to display only specific attributes from the 4/2/2020 12:00 AM record in the dashboard/web-app. When that station reports their information again, I'd like the web-app/dashboard to automatically reflect that update...

Any Ideas for a newbie to this side of things???!!! I've been looking into ARCADE, but not sure exactly how to make it work correctly. TIA

#wildfire 

 Chris Ferner

Preview file
45 KB
11 Replies
TechServices
by
New Contributor II
‎02-23-2021 08:53 AM

There are a couple of comments at the following thread that may be helpful to you. They were talking about using related tables and how to set them up for updated records.

https://community.esri.com/t5/arcgis-survey123-questions/mapping-with-survey123-within-a-polygon-or-...

 

 

0 Kudos
by Anonymous User
Not applicable
‎03-24-2021 01:42 PM

Hey Everyone,

I think I have figured out a workaround for this issue! Hopefully this method can help you out too.

My Method:

1a) Created a view in SDE for my related table, where the SQL expression specifies only the most recent related record should be returned if there are matching global ID's in the related table (ie. if there are more than one related records for a single feature). My expression input into "Create Database View" tool:

SELECT OBJECTID, GlobalID_rel, PaintType, created_date, DatePainted
FROM database.user.CrosswalksAndStopBars_RelatedTable AS t
WHERE created_date =
(SELECT MAX(created_date)
FROM database.user.CrosswalksAndStopBars_RelatedTable
WHERE GlobalID_rel = t.GlobalID_rel)
 
1b) The "SELECT MAX" portion of the code is what returns only the newest related record in the view.
 
2a) I then used SQL Server Management Studio to help me build another view expression to join the SDE feature class with the newly created related table view. I copied the expression generated from SQL server into the "Create Database View" tool:
 
SELECT        USER.CROSSWALKSANDSTOPBARS.GlobalID, USER.CROSSWALKSANDSTOPBARS.OBJECTID, USER.CROSSWALKSANDSTOPBARS.CrosswalkType, USER.CROSSWALKSANDSTOPBARS.Notes,
                         USER.CROSSWALKSANDSTOPBARS.SHAPE, USER.CROSSWALKSANDSTOPBARS.GDB_GEOMATTR_DATA, USER.CrosswalksAndStopBars_RelatedTable_View.OBJECTID AS ObjectID_Rel,
                         USER.CrosswalksAndStopBars_RelatedTable_View.GlobalID_rel, USER.CrosswalksAndStopBars_RelatedTable_View.PaintType, USER.CrosswalksAndStopBars_RelatedTable_View.created_date,
                         USER.CrosswalksAndStopBars_RelatedTable_View.DatePainted
FROM            USER.CROSSWALKSANDSTOPBARS LEFT OUTER JOIN
                         USER.CrosswalksAndStopBars_RelatedTable_View ON USER.CROSSWALKSANDSTOPBARS.GlobalID = USER.CrosswalksAndStopBars_RelatedTable_View.GlobalID_rel
 
2b) The output view joins the feature with the newest related record for that feature, using the global ID in the feature class and the related global ID field in the related table view.
 
3) I created a map service with Mapping capability that shares the view created in step 2. I was then able to input the REST endpoint of this service into my AGOL web maps for my applications, displaying only the newest related record for each feature.
 
Note: A LEFT OUTER JOIN was used in step 2a so that my view I was sharing with AGOL would show both features that had no related records yet created and features that had 1 or more related records (displaying only the newest related record of course).
 
Hope this helps!
Brett