How to select and tag the most recent record based on date and ID?

[NoahWasserman]

I am trying to create a model in Model Builder (ArcGIS Pro) that will calculate a "1" in a field for the most recent record based on site ID.  I have a site id field (DBID), an inspection date field (i_site_date), and the tag field (TAG).  There are multiple reports for each site (though some sites have none).  How do I sort by DBID, select the most recent (max?) date and calculate a "1" in the TAG field for just that most recent record for each site?

[Bud]

1. What kind of geodatabase and what version of Pro?
2. Could you use Select By Attributes with a subquery as the SQL expression? And Calculate Field?
3. Can you attach sample spreadsheet data or a mobile geodatabase to this post? And a screenshot of the attribute table, including a field populated with the values you want?
4. What do you want to happen if there are ties?
5. Summary Statistics (Analysis)
6. Summary Statistics — Tie fields to a primary field
7. Selecting the most recent records based on unique values in another field
8. Select maximum values in Select By Attributes (greatest n per group)
9. Related SQL in Join — Control what related record gets used
10. Support correlated subqueries in file geodatabase SQL expressions

[NoahWasserman]

Hi Bud, thanks for your response. I'll look into those links you posted.  My feature class (site points) and related table (inspection reports) are stored in ArcGIS.com. We use Field Maps App to submit inspections.  I'm most comfortable in Arcade so I was looking for a solution that would translate the following script I use when calculating fields in ArcGIS.com/content/data view for the feature class:

var ordered = OrderBy(FeatureSetByRelationshipName($feature, "InspectionReports"), "i_site_date DES")
var site = $feature.DBID
var related = Filter(ordered, "DBID = @site")
var relatedfeatures = Filter(related,"Report_type LIKE '%Inspection'")
var last = First(relatedfeatures)

var result;
if(last == null) {
result = Text("")
} else {
result = last.i_site_date
}
return result

I'm trying this solution right now: using the Sort tool in Model builder to pull down the inspections, sorting them by DBID and Date (descending), then joining that table back to the Site layer based on DBID, with the assumption that the first record for each DBID will be the most recent and join.  If you have a way to query an ArcGIS.com related table in Model Builder (ArcPro 3.1.3) and calculate the most recent date that's what I'm looking for!

[MariaRoe86]

Hi, 

I'm trying to do the exact same thing as you. Have you made any progress on finding an straightforward solution using Field Calculator? 

[NoahWasserman]

Hey MariaRoe86,

ESRI tech support could not find a solution that allowed me to reference the related table in Model builder Calculate Field tool.  So, I ended up setting up a Model with the first steps being 1) selecting all of the records from the AGOL related table that are not maintenance (we have a couple of inspection types), 2) using Sort tool to export from AGOL with the table sorted by ID then by Date (descending) to a local geo-database, 3) join that table back to the point layer - only the first records (newest) join one-to-one, then 4) calculate the inspection date over to the point layer.  Then I have a couple of other Calculate Field Arcade expressions run on the point layer but these all run smoothly because they only reference that layer, not the related table (Most recent inspection date was the only data I needed).  Seems to be working.  I run the model as a scheduled geoprocess on my desktop ArcPRO, takes <1min to run in the background regardless of what ArcPRO project I have open, and it seems to be working to keep everything updated.  The Sort export table overwrites itself each time so I'm not accumulating a lot of data and so there's not an issue joining it back to the point layer each time in the model. But you could just as easily add another export step with a time/date stamp in the name if you wanted a record of all related table updates.  In the attached model image I added in a Select by Attribute to deselect after my export, just so that selection wouldn't mess up the next run.

 

Let me know if you find any other solutions.