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)
