Multiple Field Key to Single Field Key for Relates using Python and ArcPy

MultiField_to_SingleKey.py

#Name: Multiple Field Key to Single Field Key for Relates using Python and ArcPy
#Author: Bryan McIntosh
#Description: For updating a single LINK_ID field in both the parent and child table
#             based on a natural 4 column composite index relationship.
import arcpy
## VARIABLES ##
edit_ws = r'C:\SpatialTimes.gdb' #Edit workspace
xs_fc = r'C:\SpatialTimes.gdb\CrossSection' #parent FC
xs_tbl = r'C:\SpatialTimes.gdb\CrossSection_RatingCurves' #related table
xs_att = ['Link_ID','F1','F2','F3','F4','OID@']
## FUNCTIONS ##
def fnUpdateXSTBL(qry, linkID):
    with arcpy.da.UpdateCursor(xs_tbl,xs_att, qry) as cursor2:
            for row in cursor2:
                row[0] = linkID
                cursor2.updateRow(row)
## MAIN CODE ##
edit = arcpy.da.Editor(edit_ws) #Set the edit workspace
edit.startEditing(False, False) #Note: change second param to true if using SDE WS
with arcpy.da.UpdateCursor(xs_fc, xs_att) as cursor:
    for row in cursor:
        row[0] = row[5] #Makes the LinkID equal to the current ObjectID of parent
        cursor.updateRow(row) #Update the parent row
        #Set the query for the multi-field natural key (fields are F1, F2, F3, F4)
        xs_tbl_qry = xs_att[1] + " = '{0}' AND ".format(row[1]) + xs_att[2] + " = '{0}' AND ".format(row[2]) + xs_att[3] + " = '{0}' AND ".format(row[3]) + xs_att[4] + " = {0}".format(row[4])
        fnUpdateXSTBL(xs_tbl_qry, row[5]) # Send the query and parent ObjectID to update the related table
edit.stopEditing(True)

My table does not have everything that my feature class does (I am really trying to find which entries are in the table and which entries are not). Is there a way to check if cursor2 is empty before trying to update the row? Right now it returns an error about an Invalid SQL Statement...

Thanks!

Hey @elbreidenbach, I haven't looked at this in a few years, but at first glance you could check if there are related records before using cursor2. Many approaches - select statement and count, query layer, or a searchCursor (easy since uses the same syntax).

I haven't test this but try adding a new first line inside the fnUpdateXSTBL function. Use the python any() function to evaluate if there is a selection using the same logic as the updateCursor (would return true if results). If so, cursor2 to the races.

if any(arcpy.da.SearchCursor(xs_tbl,xs_att, qry)):

Hi @TheBryanMac,

Thanks for your help! The any with a SearchCursor works for me. I appreciate your super quick response. On to the next challenge.