Our Storm Water personnel maintain several feature classes in an enterprise geodatabase, which is currently at version 10.6. Each of these feature classes is related to two tables: one for inspections and one for events. Each feature class has a field which represents the number of days since the feature was visited, either by inspection or event. They symbolize their layers by this field, using graduated colors, to show how long it's been since the feature was visited. For example, red features haven't been visited in a long time while green ones have been visited recently. Storm Water wanted this field updated automatically every night.
I wrote a Python script which performs the update. I recently extrapolated it to be very general, to apply to any geodatabase layer and any number of related tables. I call the method once per feature class. I apologize if the documentation is not in a format to your liking, but I followed some examples I found on-line. Since it is just a script, you're free to modify it to your whim:
<SPAN class="comment token"># Script written by Roger Dunn, Information Technology Division, City Manager's Office, City of Orem, Utah</SPAN>
<SPAN class="comment token"># February 2019</SPAN>
<SPAN class="comment token"># Requires ArcGIS Desktop Standard or Advanced.</SPAN>
<SPAN class="comment token"># Written with ArcPy for Desktop 10.6.1</SPAN>
<SPAN class="comment token"># Target geodatabase is a 10.6 geodatabase affectionately called Knight, but really called OREMEGDB</SPAN>
<SPAN class="comment token"># Use ArcPy</SPAN>
<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="comment token"># DateTime functions needed</SPAN>
<SPAN class="keyword token">import</SPAN> datetime
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable</SPAN><SPAN class="punctuation token">(</SPAN>\
arcSDEConnectionFileName \
<SPAN class="punctuation token">,</SPAN> masterFeatureClassName \
<SPAN class="punctuation token">,</SPAN> masterKeyFieldName \
<SPAN class="punctuation token">,</SPAN> masterDayRangeFieldName \
<SPAN class="punctuation token">,</SPAN> detailEventTableNames \
<SPAN class="punctuation token">,</SPAN> detailEventForeignKeyFieldNames \
<SPAN class="punctuation token">,</SPAN> detailEventDateFieldNames \
<SPAN class="punctuation token">,</SPAN> valueIfNoHistoryFound
<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="string token">'''
Updates an ArcGIS geodatabase feature class field which represents the number of days
since something happened in a detail event table (which may or may not be related via
a relationship class in ArcGIS). This method doesn't return a value.
:param str arcSDEConnectionFileName: The name of an .sde connection file. If this
script is running in ArcGIS Desktop, then it suffices to use 'Database Connections\'
followed by the name of the file. If this is automated, then the .sde file should
be in the same directory as this script.
:param str masterFeatureClassName: The name of the feature class to be modified. The
connection information in arcSDEConnectionFileName should have the user name and
password (saved) of a user with permissions to modify masterFeatureClassName. Note
that masterFeatureClassName does not include the name of the parent feature dataset.
:param str masterKeyFieldName: The name of the primary key field in the master feature
class.
:param str masterDayRangeFieldName: The name of the field in the master feature class
which will hold the date range from today's date back to the most recent event in any
of the detailEventTableNames.
:param str[] detailEventTableNames: The names of tables related to the master feature
class.
:param str[] detailEventForeignKeyFieldNames: For each table listed in detailEventTableNames,
include the name of the field in that table that corresponds to the master feature class'
primary key field. Therefore, this list is as long as detailEventTableNames.
:param str[] detailEventDateFieldNames: For each table listed in detailEventTableNames,
include the name of the field in that table that contains the event date. Therefore, this
list is as long as detailEventTableNames.
:param var valueIfNoHistoryFound: The default value to insert in the master feature class'
DayRangeField if no history can be found in any of the detailEventTableNames for that
particular feature.
...
'''</SPAN>
<SPAN class="comment token"># Change the environment workspace, and allow previous temporary datasets be overridden</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> arcSDEConnectionFileName
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>overwriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
<SPAN class="comment token"># Store "today" (don't let it change during the script's iterations</SPAN>
scriptToday <SPAN class="operator token">=</SPAN> datetime<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Create a dictionary from the detail tables where the key is the unique ID of the master record</SPAN>
<SPAN class="comment token"># and the value is the most recent date for a detail record.</SPAN>
<SPAN class="comment token"># Initialize the dictionary</SPAN>
valueDict <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">{</SPAN><SPAN class="punctuation token">}</SPAN>
<SPAN class="keyword token">for</SPAN> detailTableIndex <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>detailEventTableNames<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
detailFields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>\
detailEventForeignKeyFieldNames<SPAN class="punctuation token">[</SPAN>detailTableIndex<SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> detailEventDateFieldNames<SPAN class="punctuation token">[</SPAN>detailTableIndex<SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>detailEventTableNames<SPAN class="punctuation token">[</SPAN>detailTableIndex<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> detailFields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> readRows<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> readRow <SPAN class="keyword token">in</SPAN> readRows<SPAN class="punctuation token">:</SPAN>
masterID <SPAN class="operator token">=</SPAN> readRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
dateVal <SPAN class="operator token">=</SPAN> readRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="operator token">not</SPAN> masterID <SPAN class="keyword token">is</SPAN> None <SPAN class="operator token">and</SPAN> <SPAN class="operator token">not</SPAN> dateVal <SPAN class="keyword token">is</SPAN> None<SPAN class="punctuation token">:</SPAN>
dateOnly <SPAN class="operator token">=</SPAN> dateVal<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">if</SPAN> <SPAN class="operator token">not</SPAN> masterID <SPAN class="keyword token">in</SPAN> valueDict<SPAN class="punctuation token">:</SPAN>
valueDict<SPAN class="punctuation token">[</SPAN>masterID<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dateOnly
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">if</SPAN> dateOnly <SPAN class="operator token">></SPAN> valueDict<SPAN class="punctuation token">[</SPAN>masterID<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">:</SPAN>
valueDict<SPAN class="punctuation token">[</SPAN>masterID<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> dateOnly
<SPAN class="keyword token">del</SPAN> dateOnly
<SPAN class="keyword token">del</SPAN> masterID
<SPAN class="keyword token">del</SPAN> dateVal
<SPAN class="keyword token">del</SPAN> readRow
<SPAN class="keyword token">del</SPAN> detailFields
<SPAN class="keyword token">del</SPAN> detailTableIndex
<SPAN class="comment token">#print valueDict</SPAN>
<SPAN class="comment token"># With valueDict now populated, it's time to modify the masterFeatureClass' DayRangeField with</SPAN>
<SPAN class="comment token"># what we've found</SPAN>
masterFields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>masterKeyFieldName<SPAN class="punctuation token">,</SPAN> masterDayRangeFieldName<SPAN class="punctuation token">]</SPAN>
edit <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>Editor<SPAN class="punctuation token">(</SPAN>arcSDEConnectionFileName<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Edit session is started without an undo/redo stack for versioned data (for second argument,</SPAN>
<SPAN class="comment token"># use False for unversioned data)</SPAN>
edit<SPAN class="punctuation token">.</SPAN>startEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>masterFeatureClassName<SPAN class="punctuation token">,</SPAN> masterFields<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> masterRows<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> masterRow <SPAN class="keyword token">in</SPAN> masterRows<SPAN class="punctuation token">:</SPAN>
masterID <SPAN class="operator token">=</SPAN> masterRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">if</SPAN> masterID <SPAN class="keyword token">in</SPAN> valueDict<SPAN class="punctuation token">:</SPAN>
daysSince <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>scriptToday <SPAN class="operator token">-</SPAN> valueDict<SPAN class="punctuation token">[</SPAN>masterID<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>days
<SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
daysSince <SPAN class="operator token">=</SPAN> valueIfNoHistoryFound
<SPAN class="comment token"># If the calculated value for the date range doesn't equal what's already in the</SPAN>
<SPAN class="comment token"># field, modify it. We only want delta rows where there's really a change.</SPAN>
<SPAN class="keyword token">if</SPAN> masterRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">!=</SPAN> daysSince<SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Start an edit operation</SPAN>
edit<SPAN class="punctuation token">.</SPAN>startOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
masterRow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> daysSince
masterRows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>masterRow<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token">#print '{0} {1} updated. Field {2} = {3}'.format(masterKeyField, masterID, masterEditField, daysSince)</SPAN>
<SPAN class="comment token"># Stop the edit operation.</SPAN>
edit<SPAN class="punctuation token">.</SPAN>stopOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN>
edit<SPAN class="punctuation token">.</SPAN>abortOperation<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">del</SPAN> daysSince
<SPAN class="keyword token">del</SPAN> masterID
<SPAN class="keyword token">del</SPAN> masterRow
<SPAN class="keyword token">finally</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Stop the edit session and save the changes</SPAN>
edit<SPAN class="punctuation token">.</SPAN>stopEditing<SPAN class="punctuation token">(</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">del</SPAN> edit
<SPAN class="keyword token">del</SPAN> valueDict
<SPAN class="keyword token">del</SPAN> scriptToday
<SPAN class="keyword token">del</SPAN> masterFields
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>I call the function like this, but I'm not going to give out the real database, feature class, and table names:
<SPAN class="keyword token">from</SPAN> UpdateLayersFromTables <SPAN class="keyword token">import</SPAN> UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable
<SPAN class="comment token"># Update Feature Class 1</SPAN>
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable<SPAN class="punctuation token">(</SPAN> \
<SPAN class="string token">'MyConnection.sde'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MySQLDB.SchemaOwner.FeatureClass1'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'UniqueID'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DaysSince'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'MySQLDB.SchemaOwner.Class1Inspections'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MySQLDB.SchemaOwner.Class1Events'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FeatClass1UniqueID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'FeatClass1UID'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'InspectionDate'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'EventDate'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10000</SPAN> \
<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Update Feature Class 2</SPAN>
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable<SPAN class="punctuation token">(</SPAN> \
<SPAN class="string token">'MyConnection.sde'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MySQLDB.SchemaOwner.FeatureClass2'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'UID'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'DaysSince'</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'MySQLDB.SchemaOwner.Class2Inspections'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'MySQLDB.SchemaOwner.Class2Events'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FeatClass2UniqueID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'FeatClass2UID'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'InspectionDate'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'EventDate'</SPAN><SPAN class="punctuation token">]</SPAN> \
<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">10000</SPAN> \
<SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>"Roj"
