topic Select & display most recent record in ArcGIS Survey123 Questions

Select & display most recent record

CaseyTeske2 — Mon, 06 Apr 2020 16:05:26 GMT

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

Re: Select & display most recent record

XanderBakker — Wed, 08 Apr 2020 15:55:55 GMT

Hi Casey Teske ,

I'm afraid that Arcade will not allow you to select and show only the last (most recent record) in ArcGIS Dashboard. You can create a pop-up that shows the most recent related record and there have been some related discussion recently:

There is beta for ArcGIS Dashboard coming soon that will have extended support for configuration using Arcade expressions. Maybe what you are looking for will be in the next beta release of ArcGIS Dashboard.

Some other ideas that come to mind:

If you have your data in an Enterprise geodatabase, you could configure an attribute rule that is triggered on each insert
You could use a Notebook in ArcGIS Online and schedule it to extract the data you need and create a new hosted feature layer with only the latest record. This option does not have a trigger on the insert of a feature or record that will result in the update of the information you want to show in Dashboard. You will have to set the frequency to match your needs as closely as possible.

Re: Select & display most recent record

CaseyTeske2 — Wed, 08 Apr 2020 16:04:40 GMT

Thanks Xander Bakker... It's what I suspected. I'll keep playing in search of a work-around -- I can't be the only one with an issue like this

Re: Select & display most recent record

AndrewSouthern — Fri, 17 Apr 2020 20:19:23 GMT

You aren't, I am in the exact same boat. I have a Survey123 for long term care facilities and covid-19 related infections are updated daily or semi-daily. I would like to display only the most recent report for each facility and cannot accomplish this by just filtering within a certain time period as the frequency of these reports is not consistent. I've been searching through threads and have yet to find a good way to accomplish this goal.

Re: Select & display most recent record

CaseyTeske2 — Thu, 21 May 2020 22:28:24 GMT

Andrew did you ever get a solution? I am still banging my head on this one...

Re: Select & display most recent record

JustinConnerWR — Thu, 16 Jul 2020 21:17:41 GMT

Any luck?

Re: Select & display most recent record

CaseyTeske2 — Thu, 16 Jul 2020 21:51:55 GMT

Justin Conner ... No, no luck-- we did a complicated "dummy" workaround with some filters on our feature layer, but .... it is not ideal! If you have solved it, I'm all ears

Re: Select & display most recent record

Anonymous User — Tue, 18 Aug 2020 18:14:36 GMT

I'll just add to the record that you are not the only one looking for this functionality. I'm trying to do the same thing as you except for with park inspections.

Re: Select & display most recent record

CarlosSigal — Tue, 17 Nov 2020 18:30:46 GMT

Hi Andrew

If you use Survey123, you can do this, creating the fields that you want to know last values in the main layer, and calculating this fields with the original fields you want to copy last value. Example:

survey (sheet name)

type name label

begin group

text name Name

end group

begin group

begin repeat

decimal temp Temperature

end repeat

end group

begin group

note lasttemp Last temperature ${temp} (in calculation column)

end group

Re: Select & display most recent record

ChrisSpagnoli — Mon, 08 Feb 2021 22:18:34 GMT

Same issue here. Been trying to figure out a workaround as well. I'm also dealing with data in a related table.

Re: Select & display most recent record

TechServices — Tue, 23 Feb 2021 16:53:55 GMT

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-admin-unit/td-p/836112

Re: Select & display most recent record

Anonymous User — Wed, 24 Mar 2021 20:42:04 GMT

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