Populate field by the field of a one-to-many related table in python

[deleted-user-t3dSviijg-m9]

Trying to figure this out but am having trouble understanding what I fundamentally need to do....

I have a one-to-many relationship class. The one is fire hydrant. The many is fire hydrant inspections.

They are related by a unique FacilityID.

Both tables have a column called "CurrentPosition", and both of those columns have the same domain.

I am trying to figure out the correct procedure to make a python script that will parse through the inspections table, find the most recent inspection (by the field InspectionDate), take the value found in that record for CurrentPosition, and write it to the CurrentPosition field of the HydrantTable.

I am planning on finalizing this and having it run as a nightly task so any inspections performed, the current position is updated on the hydrant table that night.

My first thought was to make a temporary join, after making a definition query that only allows inspections performed between a set period of time. And then using the calculate field management to update the CurrentPosition, and then release the join. But my definition query on the related table was screwy and kept failing. 

Does anyone have any suggestions or documentation I can look up? Anyone doing anything similar?

Thanks very much in advance!

[MitchHolley1]

Once you have all the current dates in the Hydrants table, you can run something like this to check if any 'InspectionDate' values in the Hydrants table are less than those in the Inspection table. 

import arcpy

hydrants = r''
inspTable = r''

#set dictionary key:value pairs to FacilityID and the latest InspectionDate
dateDict = {}
with arcpy.da.SearchCursor(inspTable, ['FacilityID','InspectionDate']) as cursor:
    for row in cursor:
        key = row[0]
        value = row[1]
        if key not in dateDict:
            dateDict[key]=value
        elif value > dateDict[key]:
            dateDict[key]=value
del cursor

#search Hydrants for FacilityIDs in dateDict and compare dates
with arcpy.da.UpdateCursor(hydrants, ['FacilityID','InspectionDate']) as cursor:
    for row in cursor:
        if row[0] in dateDict:
            if row[1] < dateDict[row[0]]:
                row[1] = dateDict[row[0]]
                cursor.updateRow(row)
del cursor
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[deleted-user-t3dSviijg-m9]

This will be helpful to use as a backup check, to see if the script I will be running is successfully writing to the Hydrant tables. 

I was thinking about

1. setting a definition query on the inspections to only show inspections <=Today's Date and >=Todays date minus seven days. 

That would remove any historical inspections that we performed on any hydrants, as well as any inspections performed this year that have already been accounted for. 

2. Joining the now def. queried table to the hydrants table, by FACILITYID.

3. Using the field calculate management tool to calculate the InspectionDate on hydrants, dependent upon the value found in the joined InspectionDate from the inspection table. 

Then deleting the temp. join and removing the def. query. 

In theory this works. We'll see what new python tricks I learn along the way!

Thanks for you help. If you see something glaringly wrong in my thought process, please let me know.

[MitchHolley1]

Why don't you just post your code....

[deleted-user-t3dSviijg-m9]

import arcpy

fcPath = r"S:\GIS\DB Connection Files\DEV\DEV04.sde\WaterDistribution\wSystemValve"
relatedTblPath = r"S:\GIS\DB Connection Files\DEV\DEV04.sde\VALVEACTIVITY"

print "system valves and valve activity paths set"

relTblFields = ["FACILITYID", "INSPDATE", "CurrentPosition"]
fcFields = ["FACILITYID", "CurrentPosition"]

print "field dict's created"
dict_RelTbl = {} #Look up dictionary

#Loop through related table and store only the max date for each unique ID.
with arcpy.da.SearchCursor(relatedTblPath, relTblFields) as cursor:
    for row in cursor:
        joinID = row[0]
        if joinID not in dict_RelTbl.keys():
            dict_RelTbl[joinID] = row
        # if the key is in the dictionary already (one to many relationship), compare the dates and keep the most recent date
        else:
            curDate = dict_RelTbl[joinID][1]
            if row[1] > curDate:
                dict_RelTbl[joinID] = row

with arcpy.da.UpdateCursor(fcPath, fcFields) as cursor:
    for row in cursor:
        joinID = row[0]
        relatedRecord = dict_RelTbl[joinID]
        currentPosition = relatedRecord[2]
        row[1] = currentPosition
        cursor.updateRow(row)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[deleted-user-t3dSviijg-m9]

But I am running into this issue....

Traceback (most recent call last):
  File "S:/GIS/ValveScriptTesting.py", line 23, in <module>
    if row[1] > curDate:
TypeError: can't compare datetime.datetime to NoneType
‍‍‍‍

[MitchHolley1 · Accepted solution]

It seems like there is a Null InspectionDate in row[1]. 

A SQL query can get around that: 

dateQry="""INSPDATE IS NOT NULL"""‍

#Loop through related table and store only the max date for each unique ID.
with arcpy.da.SearchCursor(relatedTblPath, relTblFields, dateQry) as cursor:
    for row in cursor:
        joinID = row[0]
        if joinID not in dict_RelTbl.keys():
            dict_RelTbl[joinID] = row
        # if the key is in the dictionary already (one to many relationship), compare the dates and keep the most recent date
        else:
            curDate = dict_RelTbl[joinID][1]
            if row[1] > curDate:
                dict_RelTbl[joinID] = row‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[deleted-user-t3dSviijg-m9]

I was just about to hop on here an let you know that I think I found that issue, that there are a few records that are blank. 

My work around which, anything with a date would take precedence over anything without a date. 

   else:
        curDate = dict_RelTbl[joinID][1]
        compareDate = row[1]
        if curDate is None:
            dict_RelTbl[joinID] = row
        else if compareDate is not None and compareDate  > curDate:
            dict_RelTbl[joinID] = row‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I'm going to give your solution a go as well. 

Thanks for your help thus far!