nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Automation of Field Update with Most Recent Date from Related Table

DerekWood

Hello all. For my work there are several types of assets that we maintain ownership/maintenance of and perform scheduled inspections on a repeat cycle. Each type of asset is maintained in their own feature class and then all of the inspection data is maintained in a related table using the FacilityID of each asset as the Origin/Foreign key for the 1:M relationship class.

We are currently restructuring our .gdb and would like to implement a new filed for each asset's feature class that contains the date of the most recent inspection. What would be the most straightforward way to incorporate an automated population/update of this field with the most recent date from the related data?

Find more posts tagged with

Analysis

Content Management

Geodatabase

Accepted answers

rzufelt

I do this with pipeline inspection data. New inspections are added to the inspection table. Every night, a python script sorts the inspection table by date, 1:1 join the sorted inspection table to the pipelines, then calculate the date/inspection fields in the pipeline FC.

Since is a 1:1 join, only the "first" match is joined, and since the table is sorted by date, the newest row is the one that it joined and thus copied over to the FC table. Now I have the last/latest inspection data as an attribute to the pipeline FC, and only need to use a relate if I want to see the history, not just the last inspection.

All comments

rzufelt

Good to hear it worked.

Wasn't really sure how involved answer you wanted, so gave you the basic gist of my initial process and how it works.

I have since streamlined it to eliminate the need for the join, and to use updateCursor as it runs much, much faster than calculate field. Even on large dataset(s), this method takes seconds.

SewerTrackingTable_Sort = "in_memory\\SewerTrackingTable_Sort"
# Process: Sort
arcpy.Sort_management(reltable, SewerTrackingTable_Sort, "Jetted DESCENDING", "UR")

lutDict = {}

with arcpy.da.SearchCursor(SewerTrackingTable_Sort,["Jetted", "user_", "WorkType", "WorkOrder","WorkNotes","ProjectType","recnum"],"", "") as cursor:
  for row in cursor:
          recnum = row[6]
          if not lutDict.has_key(recnum):
              lutDict[recnum] = [row[0], row[1], row[2], row[3], row[4], row[5]]
      
with arcpy.da.UpdateCursor(ssGravityMain, ["Jetted", "user_", "WorkType", "WorkOrder","WorkNotes","ProjectType","recnum"]) as uCursor: 
    for Row in uCursor:
        if lutDict.has_key(Row[6]):
            Row[0] = lutDict[Row[6]][0]
            Row[1] = lutDict[Row[6]][1]
            Row[2] = lutDict[Row[6]][2]
            Row[3] = lutDict[Row[6]][3]
            Row[4] = lutDict[Row[6]][4]
            Row[5] = lutDict[Row[6]][5]
            uCursor.updateRow(Row)
    del Row, uCursor

Line 3 - sorts the related table by date field (Jettted) to memory dataset descending so the latest dates are at the top of the table.

Lines 7-11 establish search cursor on sorted related table and populate a dictionary.

Line 10, If the recnum is not already in the dictionary, add it with the tuple. The result is that only the "first" row for a particular feature is loaded into the dictionary as when it see's another one, it is already in the dictionary, so skips it. since it is sorted by date, this will be the "latest" record.

Then, I run an update cursor on the pipeline dataset. Go through each row, if the recnum is in the dictionary, update the fields, if not, move on to the next row.

Might give you some more food for thought.

DerekWood

Hey Rhett, thanks for this approach! I was able to use the needed tools on one of the needed feature classes via the geoprocessing tools pane and copy the python command from the history so I could format it into a single script to update the attribute field across all of our feature classes. Until I spend the time to familiarize myself with other approaches to automate the process this is an excellent solution!

rzufelt

jcarlson

It wouldn't necessarily need to be manual.

If you are working with a published service, you could make use of the Python API and use the calculate method on a FeatureLayer. This can even include a "where" filter, so you could, say, run the script every minute, looking at features with a "last edited" timestamp within the past few minutes (a little overlap to ensure nothing is missed).

Even without filtering the data first, this particular process runs very quickly, even on large datasets. Having a script fire your calculation at frequent intervals probably wouldn't impact whatever machine runs the script much.

If it's not a published service, you could still do something like this through arcpy, or even just SQL directly.

DerekWood

Thanks for the reply Josh.

I am working in an enterprise geodatabase with the live data, but I am prepping everything within a file geodatabase before a migration of the final modifications into our enterprise.gdb on a SQL server. I spoke with my GIS Administrator about utilizing this feature with the Attribute Rules approach, but he prefers to do his work in ArcMap and thus said that the tables wouldn't be copacetic in both ArcMap and AGP after implementing Attribute Rules.

As for the python script, that would need to be manually ran each time an update on the filed is desired, yes? Would the only workaround to automate this procedure be the approach with Attribute Rules that you suggested?

jcarlson

This is one of those "greatest hits" questions that comes up pretty often.

Generally speaking, if you're working in a geodatabase, Attribute Rules are what you want. Outside of that, a simple Python script to calculate the field running at frequent intervals works, too.

This is for a file geodatabase?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.