Latest Contributions by mattstutts

I'm having a bit of trouble finding a more suitable way to handle a series of selections/queries based on a many-to-many relationship. The code below works, for the most part, but it's in the "def selectRelatedRecords" section where I know I'm probably unnecessarily repeating steps and could use some input. The basic idea:  I have a table (linkTable) where the user selects a field and a value from that table that becomes sqlExp. I then get the value of the field "CR_ID" from the same linkTable where sqlExp is true. I then want to cycle through all the feature layers in the current mxd and get the related records in those layers (again, CR_ID is the same as the now selected records in linkTable.) But what I end up doing is reselecting the same records over and over in the linkTable each time I try to apply a SelectLayerByAtrribute to the available feature layers b/c I'm looping for lyr in layers: I'm really looking for some tweaks so I can maybe put the selected CR_IDs into a list, maybe, and then apply the selectlayerbyattribtue to the available layers. import arcpy, os, string import arcpy.mapping as MAP #Needed CR layers as well as Link table should be loaded into current mxd mxd = MAP.MapDocument("CURRENT") df = MAP.ListDataFrames(mxd)[0] #Get Link tables from input linkTable = arcpy.GetParameterAsText(0) #Make lists of all feature layers in dataframe layers = MAP.ListLayers(mxd, "", df) #set Reg_Code value for regional data into a sql expression fldName = arcpy.AddFieldDelimiters(arcpy.env.workspace, arcpy.GetParameterAsText(1)) sqlExp = fldName + " = " + " '{0}' ".format(arcpy.GetParameterAsText(2)) arcpy.AddMessage(sqlExp) #set selection type selectType = arcpy.GetParameterAsText(3) #used in generating list of related CR_IDs for match Link table to CR Features. linkKeyField = "CR_ID" featureKeyField = "CR_ID" #Main code to select all records in CR Feature Classes with matching Program ID in Link table def buildWhereClauseFromList(linkTable, linkKeyField, valueList):     # Add DBMS-specific field delimiters     fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(linkTable).path, linkKeyField)     arcpy.AddMessage("fieldDelimited value: {}".format(fieldDelimited))     # Determine field type     fieldType = arcpy.ListFields(linkTable, linkKeyField)[0].type     # Add single-quotes for string field values     if str(fieldType) == 'String':         valueList = ["'%s'" % value for value in valueList]     # Format WHERE clause in the form of an IN statement     whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))     return whereClause     arcpy.AddMessage("whereClause: {}".format(whereClause)) if len(layers) > 0:     for lyr in layers:         def selectRelatedRecords(linkTable, lyr, linkKeyField, featureKeyField):         #    Defines the record selection from the record selection of the linkTable         #      and applys it to the featureTable using a SQL WHERE clause built         #      in the previous defintion"""             # Set the SearchCursor to look through the selection of the OriginTable             sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(linkTable, linkKeyField, sqlExp)])             # Establishes the where clause used to select records from DestinationTable             whereClause = buildWhereClauseFromList(lyr, featureKeyField, sourceIDs)             arcpy.AddMessage("whereClause inside selectRelatedRecords: {} \n".format(whereClause))             arcpy.AddMessage("lyr: {0}, selectType: {1}".format(lyr, selectType))             # Process: Select Layer By Attribute             arcpy.SelectLayerByAttribute_management(lyr, selectType, whereClause)         # Process: Select related records between OriginTable and DestinationTable         arcpy.AddMessage("Selecting records in CR Feature Layers related to records in Link table...")         selectRelatedRecords(linkTable, lyr, linkKeyField, featureKeyField) ... View more