Community

Select Related Records

11068
11
02-06-2014 08:06 AM
ErnestoCarreras3
by
Occasional Contributor
‎02-06-2014 08:06 AM
Is there any way to select related records using python? similar to the related records button within ArcMap. I like to include this operation as a script in a GP Model.
Thanks in advanced.
EC
Tags (2)
11 Replies
ErnestoCarreras3
by
Occasional Contributor
‎02-06-2014 08:18 AM
Is there any way to select related records using python? similar to the related records button within ArcMap. I like to include this operation as a script in a GP Model.
Thanks in advanced.
EC
0 Kudos
by Anonymous User
Not applicable
‎02-06-2014 08:20 AM
I have used this script in the past:

import arcpy, os, sys
arcpy.env.ovewriteOutput = True
arcpy.env.qualifiedFieldNames = False
    
def SelectRelatedRecords(in_ft, in_field, rel_table, rel_field, sql=''):

    '''
        Selects all features from input layer based on selection from another table.  Acts like
          a "relationship class".  There must be a common field between the two tables.

        in_ft = input features
        in_field = field for the relate from input features
        rel_table = related table
        rel_field = related table field
        sql = SQL Query for the related table (optional)

    '''

    # Check to make sure field types match, otherwise raise error and exit tool
    in_ft_fields = arcpy.Describe(in_ft).fields
    ftype1 = [f.type for f in in_ft_fields if f.name == in_field][0]
    ftype2 = [f.type for f in arcpy.Describe(rel_table).fields if f.name == rel_field][0]
    if ftype1 != ftype2:
        arcpy.AddError('\nField type for "{0}" in "{1}" does not match field type for "{2}"' \
                       ' in "{3}"!\n'.format(in_field,in_ft,rel_field,rel_table))
        arcpy.AddError('Field type is "{0}", must be "{1}".\n'.format(ftype1,ftype2))
        sys.exit()
    
    # map doc
    mxd = arcpy.mapping.MapDocument('CURRENT')
    df = arcpy.mapping.ListDataFrames(mxd)[0]

    # determine version for cursor type 
    ver = float(arcpy.GetInstallInfo()['Version'])
    if ver > 10:
        dataAccess = True
    else:
        dataAccess = False

    # make feature layer if sql
    if sql:
        arcpy.SelectLayerByAttribute_management(rel_table, 'NEW_SELECTION', sql)
    rel_count = int(arcpy.GetCount_management(rel_table).getOutput(0))
    arcpy.AddMessage('\n{0} selected records from from "{1}"\n'.format(rel_count, rel_table))

    # grab unique id's
    if dataAccess:
        with arcpy.da.SearchCursor(rel_table, [rel_field]) as rows:
            if ftype2 == 'String':
                recs = tuple(r[0].encode('utf-8') for r in rows)
            else:
                recs = tuple(r[0] for r in rows)
    else:
        rows = arcpy.SearchCursor(rel_table)
        if ftype2 == 'String':
            recs = tuple(r.getValue(rel_field).encode('utf-8') for r in rows)
        else:
            recs = tuple(r.getValue(rel_field) for r in rows)
        del rows

    # Select records in in_ft table
    par_path = os.path.dirname(arcpy.Describe(in_ft).catalogPath)
    where = ''' {0} in {1} '''.format(arcpy.AddFieldDelimiters(par_path, in_field), recs)
    arcpy.SelectLayerByAttribute_management(in_ft, 'NEW_SELECTION', where)
    sel_count = int(arcpy.GetCount_management(in_ft).getOutput(0))
    arcpy.AddMessage('Selected {0} related records from "{1}"\n'.format(sel_count, in_ft))
    return

if __name__ == '__main__':

    # Get args
    argv = tuple(str(arcpy.GetParameterAsText(i)) for i in range(arcpy.GetArgumentCount()))

    # Run it
    SelectRelatedRecords(*argv)


This should do the trick.  You can either manually select records in your related table or pass an SQL query in (do not use SQL param if you have manually selected records).  I have a more complicated version where there is an option to export the selected records joined to the related table.  You can download the attached toolbox if you want to try this one out.
RelatedRecords.zip
SarahHartholt1
by
New Contributor II
‎10-04-2019 12:42 PM

This is great! Is there a way to create relationships between multiple features/tables? I have 1 feature class and 3 tables in total. 

0 Kudos
ErnestoCarreras3
by
Occasional Contributor
‎02-06-2014 08:21 AM
I have used this script in the past:

import arcpy, os, sys
arcpy.env.ovewriteOutput = True
arcpy.env.qualifiedFieldNames = False
    
def SelectRelatedRecords(in_ft, in_field, rel_table, rel_field, sql=''):

    '''
        Selects all features from input layer based on selection from another table.  Acts like
          a "relationship class".  There must be a common field between the two tables.

        in_ft = input features
        in_field = field for the relate from input features
        rel_table = related table
        rel_field = related table field
        sql = SQL Query for the related table (optional)

    '''

    # Check to make sure field types match, otherwise raise error and exit tool
    in_ft_fields = arcpy.Describe(in_ft).fields
    ftype1 = [f.type for f in in_ft_fields if f.name == in_field][0]
    ftype2 = [f.type for f in arcpy.Describe(rel_table).fields if f.name == rel_field][0]
    if ftype1 != ftype2:
        arcpy.AddError('\nField type for "{0}" in "{1}" does not match field type for "{2}"' \
                       ' in "{3}"!\n'.format(in_field,in_ft,rel_field,rel_table))
        arcpy.AddError('Field type is "{0}", must be "{1}".\n'.format(ftype1,ftype2))
        sys.exit()
    
    # map doc
    mxd = arcpy.mapping.MapDocument('CURRENT')
    df = arcpy.mapping.ListDataFrames(mxd)[0]

    # determine version for cursor type 
    ver = float(arcpy.GetInstallInfo()['Version'])
    if ver > 10:
        dataAccess = True
    else:
        dataAccess = False

    # make feature layer if sql
    if sql:
        arcpy.SelectLayerByAttribute_management(rel_table, 'NEW_SELECTION', sql)
    rel_count = int(arcpy.GetCount_management(rel_table).getOutput(0))
    arcpy.AddMessage('\n{0} selected records from from "{1}"\n'.format(rel_count, rel_table))

    # grab unique id's
    if dataAccess:
        with arcpy.da.SearchCursor(rel_table, [rel_field]) as rows:
            if ftype2 == 'String':
                recs = tuple(r[0].encode('utf-8') for r in rows)
            else:
                recs = tuple(r[0] for r in rows)
    else:
        rows = arcpy.SearchCursor(rel_table)
        if ftype2 == 'String':
            recs = tuple(r.getValue(rel_field).encode('utf-8') for r in rows)
        else:
            recs = tuple(r.getValue(rel_field) for r in rows)
        del rows

    # Select records in in_ft table
    par_path = os.path.dirname(arcpy.Describe(in_ft).catalogPath)
    where = ''' {0} in {1} '''.format(arcpy.AddFieldDelimiters(par_path, in_field), recs)
    arcpy.SelectLayerByAttribute_management(in_ft, 'NEW_SELECTION', where)
    sel_count = int(arcpy.GetCount_management(in_ft).getOutput(0))
    arcpy.AddMessage('Selected {0} related records from "{1}"\n'.format(sel_count, in_ft))
    return

if __name__ == '__main__':

    # Get args
    argv = tuple(str(arcpy.GetParameterAsText(i)) for i in range(arcpy.GetArgumentCount()))

    # Run it
    SelectRelatedRecords(*argv)


This should do the trick.  I have a more complicated version where there is an option to export the selected records joined to the related table.  You can download the attached toolbox if you want to try this one out.


Thanks for the quick reply!
0 Kudos
CarinaHoyer
by
New Contributor
‎12-29-2016 08:10 AM

Hello!
I just stumbled upon this script/toolbox and am trying to run the tool on ArcMap 10.4.1. I am getting a run error that reads ValueError: invalid literal for float(): 10.4.1. 

I am assuming this is because I am running it on a different version of Arc than the tool was created for but wanted to check if you had any input for me to change the script and debug!

Thank you!

0 Kudos
by Anonymous User
Not applicable
‎12-29-2016 08:14 AM

I have not ran this code in several years, but you can just take out the part that checks if it needs to use the old style cursors since you are using 10.4.1:

import arcpy, os, sys  
arcpy.env.ovewriteOutput = True  
arcpy.env.qualifiedFieldNames = False  
      
def SelectRelatedRecords(in_ft, in_field, rel_table, rel_field, sql=''):  
  
    '''  
        Selects all features from input layer based on selection from another table.  Acts like  
          a "relationship class".  There must be a common field between the two tables.  
  
        in_ft = input features  
        in_field = field for the relate from input features  
        rel_table = related table  
        rel_field = related table field  
        sql = SQL Query for the related table (optional)  
  
    '''  
  
    # Check to make sure field types match, otherwise raise error and exit tool  
    in_ft_fields = arcpy.Describe(in_ft).fields  
    ftype1 = [f.type for f in in_ft_fields if f.name == in_field][0]  
    ftype2 = [f.type for f in arcpy.Describe(rel_table).fields if f.name == rel_field][0]  
    if ftype1 != ftype2:  
        arcpy.AddError('\nField type for "{0}" in "{1}" does not match field type for "{2}"' \  
                       ' in "{3}"!\n'.format(in_field,in_ft,rel_field,rel_table))  
        arcpy.AddError('Field type is "{0}", must be "{1}".\n'.format(ftype1,ftype2))  
        sys.exit()  
      
    # map doc  
    mxd = arcpy.mapping.MapDocument('CURRENT')  
    df = arcpy.mapping.ListDataFrames(mxd)[0]  
  
    # make feature layer if sql  
    if sql:  
        arcpy.SelectLayerByAttribute_management(rel_table, 'NEW_SELECTION', sql)  
    rel_count = int(arcpy.GetCount_management(rel_table).getOutput(0))  
    arcpy.AddMessage('\n{0} selected records from from "{1}"\n'.format(rel_count, rel_table))  
  
    # grab unique id's  
    with arcpy.da.SearchCursor(rel_table, [rel_field]) as rows:  
        if ftype2 == 'String':  
            recs = tuple(r[0].encode('utf-8') for r in rows)  
        else:  
            recs = tuple(r[0] for r in rows)  
    
  
    # Select records in in_ft table  
    par_path = os.path.dirname(arcpy.Describe(in_ft).catalogPath)  
    where = ''' {0} in {1} '''.format(arcpy.AddFieldDelimiters(par_path, in_field), recs)  
    arcpy.SelectLayerByAttribute_management(in_ft, 'NEW_SELECTION', where)  
    sel_count = int(arcpy.GetCount_management(in_ft).getOutput(0))  
    arcpy.AddMessage('Selected {0} related records from "{1}"\n'.format(sel_count, in_ft))  
    return  
  
if __name__ == '__main__':  
  
    # Get args  
    argv = tuple(str(arcpy.GetParameterAsText(i)) for i in range(arcpy.GetArgumentCount()))  
  
    # Run it  
    SelectRelatedRecords(*argv)  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
CarinaHoyer
by
New Contributor
‎12-29-2016 08:20 AM

Thank you for the quick response!! That helps immensely.

I am also getting a TypeError: SelectRelatedRecords() takes at most 6 arguments, that comes up from the last run it part of the code. Is there a way I can take into account more arguments.

Thanks again! 

0 Kudos
ANNAPRIOR1
by
New Contributor II
‎11-23-2017 04:36 AM

I'm having the same problem except it will only take at most 5 arguments. Did you ever work out how to work around/resolve this?

0 Kudos
CarinaHoyer
by
New Contributor
‎12-29-2016 11:05 AM

Would it be a problem with using *argv

0 Kudos