This is great! Is there a way to create relationships between multiple features/tables? I have 1 feature class and 3 tables in total.
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?
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?
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
Would it be a problem with using *argv
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!
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:
<SPAN class="keyword token">import</SPAN> arcpy<SPAN class="punctuation token">,</SPAN> os<SPAN class="punctuation token">,</SPAN> sys arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>ovewriteOutput <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>qualifiedFieldNames <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">SelectRelatedRecords</SPAN><SPAN class="punctuation token">(</SPAN>in_ft<SPAN class="punctuation token">,</SPAN> in_field<SPAN class="punctuation token">,</SPAN> rel_table<SPAN class="punctuation token">,</SPAN> rel_field<SPAN class="punctuation token">,</SPAN> sql<SPAN class="operator token">=</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">''' 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) '''</SPAN> <SPAN class="comment token"># Check to make sure field types match, otherwise raise error and exit tool </SPAN> in_ft_fields <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Describe<SPAN class="punctuation token">(</SPAN>in_ft<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>fields ftype1 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>f<SPAN class="punctuation token">.</SPAN>type <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> in_ft_fields <SPAN class="keyword token">if</SPAN> f<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">==</SPAN> in_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> ftype2 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>f<SPAN class="punctuation token">.</SPAN>type <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Describe<SPAN class="punctuation token">(</SPAN>rel_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>fields <SPAN class="keyword token">if</SPAN> f<SPAN class="punctuation token">.</SPAN>name <SPAN class="operator token">==</SPAN> rel_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">if</SPAN> ftype1 <SPAN class="operator token">!=</SPAN> ftype2<SPAN class="punctuation token">:</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddError<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'\nField type for "{0}" in "{1}" does not match field type for "{2}"'</SPAN> \ <SPAN class="string token">' in "{3}"!\n'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>in_field<SPAN class="punctuation token">,</SPAN>in_ft<SPAN class="punctuation token">,</SPAN>rel_field<SPAN class="punctuation token">,</SPAN>rel_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddError<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Field type is "{0}", must be "{1}".\n'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>ftype1<SPAN class="punctuation token">,</SPAN>ftype2<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> sys<SPAN class="punctuation token">.</SPAN>exit<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># map doc </SPAN> mxd <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>MapDocument<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'CURRENT'</SPAN><SPAN class="punctuation token">)</SPAN> df <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>mapping<SPAN class="punctuation token">.</SPAN>ListDataFrames<SPAN class="punctuation token">(</SPAN>mxd<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># make feature layer if sql </SPAN> <SPAN class="keyword token">if</SPAN> sql<SPAN class="punctuation token">:</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>rel_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'NEW_SELECTION'</SPAN><SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN> rel_count <SPAN class="operator token">=</SPAN> int<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetCount_management<SPAN class="punctuation token">(</SPAN>rel_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'\n{0} selected records from from "{1}"\n'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>rel_count<SPAN class="punctuation token">,</SPAN> rel_table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># grab unique id's </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>rel_table<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>rel_field<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> rows<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">if</SPAN> ftype2 <SPAN class="operator token">==</SPAN> <SPAN class="string token">'String'</SPAN><SPAN class="punctuation token">:</SPAN> recs <SPAN class="operator token">=</SPAN> tuple<SPAN class="punctuation token">(</SPAN>r<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>encode<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'utf-8'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> r <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN> recs <SPAN class="operator token">=</SPAN> tuple<SPAN class="punctuation token">(</SPAN>r<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> r <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Select records in in_ft table </SPAN> par_path <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>path<SPAN class="punctuation token">.</SPAN>dirname<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>Describe<SPAN class="punctuation token">(</SPAN>in_ft<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>catalogPath<SPAN class="punctuation token">)</SPAN> where <SPAN class="operator token">=</SPAN> <SPAN class="string token">''' {0} in {1} '''</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>AddFieldDelimiters<SPAN class="punctuation token">(</SPAN>par_path<SPAN class="punctuation token">,</SPAN> in_field<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> recs<SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>in_ft<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'NEW_SELECTION'</SPAN><SPAN class="punctuation token">,</SPAN> where<SPAN class="punctuation token">)</SPAN> sel_count <SPAN class="operator token">=</SPAN> int<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetCount_management<SPAN class="punctuation token">(</SPAN>in_ft<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Selected {0} related records from "{1}"\n'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>sel_count<SPAN class="punctuation token">,</SPAN> in_ft<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="keyword token">if</SPAN> __name__ <SPAN class="operator token">==</SPAN> <SPAN class="string token">'__main__'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="comment token"># Get args </SPAN> argv <SPAN class="operator token">=</SPAN> tuple<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetParameterAsText<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetArgumentCount<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># Run it </SPAN> SelectRelatedRecords<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>argv<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></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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!
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.
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)
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
