topic Re: Select Related Records in Python Questions

Select Related Records

ErnestoCarreras3 — Thu, 06 Feb 2014 16:06:26 GMT

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

Select Related Records

ErnestoCarreras3 — Thu, 06 Feb 2014 16:18:38 GMT

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

Re: Select Related Records

Anonymous User — Sat, 11 Dec 2021 23:43:02 GMT

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.

Re: Select Related Records

ErnestoCarreras3 — Sat, 11 Dec 2021 23:43:05 GMT

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!

Re: Select Related Records

CarinaHoyer — Thu, 29 Dec 2016 16:10:50 GMT

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!

Re: Select Related Records

Anonymous User — Sat, 11 Dec 2021 23:43:08 GMT

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)  ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Select Related Records

CarinaHoyer — Thu, 29 Dec 2016 16:20:52 GMT

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!

Re: Select Related Records

CarinaHoyer — Thu, 29 Dec 2016 19:05:43 GMT

Would it be a problem with using *argv

Re: Select Related Records

BruceHarold — Tue, 03 Jan 2017 15:37:17 GMT

Hello

Here is a sample scripted GP tool that handles selections between objects sharing keys, it has no dependency on relates:

http://www.arcgis.com/home/item.html?id=e638afe0695a4ad38388cb8d9b350446

Regards

Re: Select Related Records

ANNAPRIOR1 — Thu, 23 Nov 2017 12:36:57 GMT

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?

Re: Select Related Records

DougMcCarty — Fri, 09 Feb 2018 19:06:10 GMT

Bruce, this is a great tool. I was attempting to modify a bit so that selections could be created in the dialog via Query Builder and passed to the tool prior to the main execution, but running into some issues. SQL Expressions are not being honored, and all records in the data set are selecting. Any tips?

Re: Select Related Records

SarahHartholt1 — Fri, 04 Oct 2019 19:42:27 GMT

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