My python script in ArcMap has an initial dialog box with two inputs: REG_CODE (values are in a list and include 'AKR', 'IMR', 'NER', etc) and Selection_type which offers 'NEW_SELECTION', 'REMOVE_FROM_SELECTION', etc. My code runs just fine unless I pick the value 'NER'. Then I get this error: Traceback (most recent call last): File "C:\Temp\SelectRegionalCREnterpriseData.py", line 99, in selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField) File "C:\Temp\SelectRegionalCREnterpriseData.py", line 96, in selectRelatedRecords arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause) File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute). I can verify that my layers and tables have a valid field called REG_CODE, that 'NER' does exist as an attribute value, and that there are no associated NULL values in the CR_ID field. The script does two things... 1) goes through each layer, and selects features where REG_CODE = (say, NER) 2) Finds all records in CR_Catalog table where REG_CODE = and then selects the related records in the CR_Link table. import arcpy , os , string import arcpy . mapping as MAP #Needed CR layers as well as Catalog and Link tables should be loaded into current mxd mxd = MAP . MapDocument ( "CURRENT" ) df = MAP . ListDataFrames ( mxd )[ 0 ] #set Reg_Code value for regional data into a sql expression fld = arcpy . AddFieldDelimiters ( arcpy . env . workspace , "REG_CODE" ) sqlExp = fld + " = " + " '{0}' " . format ( arcpy . GetParameterAsText ( 0 )) arcpy . AddMessage ( sqlExp ) #set selection type selectType = arcpy . GetParameterAsText ( 1 ) #Make lists of all feature layers and another list of all tables in dataframe layers = MAP . ListLayers ( mxd , "" , df ) tables = MAP . ListTableViews ( mxd , "" , df ) #Set destinationTable (CR_Link) to empty string. This gets populated only if Link table is loaded into mxd. # else, the select layer by attribute tool doesn't run destinationTable = "" originTable = "" #used in generating list of related CR_IDs for match Catalog to Link table. Related by CR_ID field. primaryKeyField = "CR_ID" foreignKeyField = "CR_ID" #These will be used to get records in Link that are related to Catalog where #Reg_Code = for table in tables : descTable = arcpy . Describe ( table ) tableNameList = ( descTable . name ). split ( "." ) tableName = tableNameList [- 1 ] if tableName == "CR_Catalog" : originTable = table elif tableName == "CR_Link" : destinationTable = table else : pass #Main code to select all records in Catalog with matching Reg_code and then getting related records in Link table if not str ( destinationTable ) == "" and not str ( originTable ) == "" : def buildWhereClauseFromList ( originTable , primaryKeyField , valueList ): # Add DBMS-specific field delimiters fieldDelimited = arcpy . AddFieldDelimiters ( arcpy . Describe ( originTable ). path , primaryKeyField ) # Determine field type fieldType = arcpy . ListFields ( originTable , primaryKeyField )[ 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 def selectRelatedRecords ( originTable , destinationTable , primaryKeyField , foreignKeyField ): ## Defines the record selection from the record selection of the OriginTable ## and applys it to the DestinationTable 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 ( originTable , primaryKeyField , sqlExp )]) # Establishes the where clause used to select records from DestinationTable whereClause = buildWhereClauseFromList ( destinationTable , foreignKeyField , sourceIDs ) # Process: Select Layer By Attribute arcpy . SelectLayerByAttribute_management ( destinationTable , selectType , whereClause ) # Process: Select related records between OriginTable and DestinationTable selectRelatedRecords ( originTable , destinationTable , primaryKeyField , foreignKeyField ) del tables , tableName , tableNameList , primaryKeyField , foreignKeyField del originTable , destinationTable #If Link isn't in mxd, send a message else : arcpy . AddMessage ( "CR_Link and CR_Catalog tables must be added to dataframe in order to create selection in CR_Link" ) for lyr in layers : fieldList = arcpy . ListFields ( lyr . dataSource , "REG_CODE" ) if len ( fieldList ) == 1 : arcpy . SelectLayerByAttribute_management ( lyr , selectType , sqlExp ) else : pass del lyr , layers , del sqlExp , fld , mxd , df , fieldList

Thank you. I you a link yesterday through email to download some data. I appreciate this greatly. We are using SQL Server 2012.

Thanks!! It's sql server 2012. Matt Stutts, GIS Specialist Cultural Resources GIS National Park Service Washington, DC 202-354-2149 www.nps.gov/crgis www.facebook.com/crgis

This may be workspace specific then, when we get your data we'll load it into an enterprise database (what DBMS is yours?) and see what happens.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

MSDC

My python script in ArcMap has an initial dialog box with two inputs: REG_CODE (values are in a list and include 'AKR', 'IMR', 'NER', etc) and Selection_type which offers 'NEW_SELECTION', 'REMOVE_FROM_SELECTION', etc.

My code runs just fine unless I pick the value 'NER'. Then I get this error:

Traceback (most recent call last): File "C:\Temp\SelectRegionalCREnterpriseData.py", line 99, in selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField) File "C:\Temp\SelectRegionalCREnterpriseData.py", line 96, in selectRelatedRecords arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause) File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

I can verify that my layers and tables have a valid field called REG_CODE, that 'NER' does exist as an attribute value, and that there are no associated NULL values in the CR_ID field.

The script does two things... 1) goes through each layer, and selects features where REG_CODE = (say, NER) 2) Finds all records in CR_Catalog table where REG_CODE = and then selects the related records in the CR_Link table.

<SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> os</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> string
</SPAN><SPAN class="kwd" style="color: #00008b;">import</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">mapping </SPAN><SPAN class="kwd" style="color: #00008b;">as</SPAN><SPAN class="pln" style="color: #000000;"> MAP</SPAN>

 

<SPAN class="com" style="color: #808080;">#Needed CR layers as well as Catalog and Link tables should be loaded into current mxd</SPAN><SPAN class="pln" style="color: #000000;">
mxd </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> MAP</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">MapDocument</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">"CURRENT"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
df </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> MAP</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">ListDataFrames</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">mxd</SPAN><SPAN class="pun" style="color: #000000;">)[</SPAN><SPAN class="lit" style="color: #800000;">0</SPAN><SPAN class="pun" style="color: #000000;">]</SPAN>

 

<SPAN class="com" style="color: #808080;">#set Reg_Code value for regional data into a sql expression</SPAN><SPAN class="pln" style="color: #000000;">
fld </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">AddFieldDelimiters</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">env</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">workspace</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"REG_CODE"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
sqlExp </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> fld </SPAN><SPAN class="pun" style="color: #000000;">+</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">" = "</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">+</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">" '{0}' "</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">format</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">GetParameterAsText</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="lit" style="color: #800000;">0</SPAN><SPAN class="pun" style="color: #000000;">))</SPAN><SPAN class="pln" style="color: #000000;">
arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">AddMessage</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">sqlExp</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
</SPAN><SPAN class="com" style="color: #808080;">#set selection type</SPAN><SPAN class="pln" style="color: #000000;">
selectType </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">GetParameterAsText</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="lit" style="color: #800000;">1</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN>

 

<SPAN class="com" style="color: #808080;">#Make lists of all feature layers and another list of all tables in dataframe</SPAN><SPAN class="pln" style="color: #000000;">
layers </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> MAP</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">ListLayers</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">mxd</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> df</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
tables </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> MAP</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">ListTableViews</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">mxd</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> df</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN>

 

<SPAN class="com" style="color: #808080;">#Set destinationTable (CR_Link) to empty string. This gets populated only if Link table is loaded into mxd.</SPAN><SPAN class="pln" style="color: #000000;">
</SPAN><SPAN class="com" style="color: #808080;"># else, the select layer by attribute tool doesn't run</SPAN><SPAN class="pln" style="color: #000000;">
destinationTable </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN><SPAN class="pln" style="color: #000000;">
originTable </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN>

 

<SPAN class="com" style="color: #808080;">#used in generating list of related CR_IDs for match Catalog to Link table. Related by CR_ID field.</SPAN><SPAN class="pln" style="color: #000000;">
primaryKeyField </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"CR_ID"</SPAN><SPAN class="pln" style="color: #000000;">
foreignKeyField </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"CR_ID"</SPAN>

 #These will be used to get records in Link that are related to Catalog where #Reg_Code = <input> 

<SPAN class="kwd" style="color: #00008b;">for</SPAN><SPAN class="pln" style="color: #000000;"> table </SPAN><SPAN class="kwd" style="color: #00008b;">in</SPAN><SPAN class="pln" style="color: #000000;"> tables</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; descTable </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">Describe</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">table</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; tableNameList </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">descTable</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">name</SPAN><SPAN class="pun" style="color: #000000;">).</SPAN><SPAN class="pln" style="color: #000000;">split</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">"."</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; tableName </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> tableNameList</SPAN><SPAN class="pun" style="color: #000000;">[-</SPAN><SPAN class="lit" style="color: #800000;">1</SPAN><SPAN class="pun" style="color: #000000;">]</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">if</SPAN><SPAN class="pln" style="color: #000000;"> tableName </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"CR_Catalog"</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; originTable </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> table
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">elif</SPAN><SPAN class="pln" style="color: #000000;"> tableName </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"CR_Link"</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; destinationTable </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> table
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">else</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">pass</SPAN>

 #Main code to select all records in Catalog with matching Reg_code and then getting related records in Link table 

<SPAN class="kwd" style="color: #00008b;">if</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="kwd" style="color: #00008b;">not</SPAN><SPAN class="pln" style="color: #000000;"> str</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">destinationTable</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="kwd" style="color: #00008b;">and</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="kwd" style="color: #00008b;">not</SPAN><SPAN class="pln" style="color: #000000;"> str</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">""</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">def</SPAN><SPAN class="pln" style="color: #000000;"> buildWhereClauseFromList</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> valueList</SPAN><SPAN class="pun" style="color: #000000;">):</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Add DBMS-specific field delimiters</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; fieldDelimited </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">AddFieldDelimiters</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">Describe</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">).</SPAN><SPAN class="pln" style="color: #000000;">path</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Determine field type</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; fieldType </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">ListFields</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">)[</SPAN><SPAN class="lit" style="color: #800000;">0</SPAN><SPAN class="pun" style="color: #000000;">].</SPAN><SPAN class="pln" style="color: #000000;">type

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Add single-quotes for string field values</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">if</SPAN><SPAN class="pln" style="color: #000000;"> str</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">fieldType</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">'String'</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; valueList </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="str" style="color: #800000;">"'%s'"</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">%</SPAN><SPAN class="pln" style="color: #000000;"> value </SPAN><SPAN class="kwd" style="color: #00008b;">for</SPAN><SPAN class="pln" style="color: #000000;"> value </SPAN><SPAN class="kwd" style="color: #00008b;">in</SPAN><SPAN class="pln" style="color: #000000;"> valueList</SPAN><SPAN class="pun" style="color: #000000;">]</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Format WHERE clause in the form of an IN statement</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; whereClause </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"%s IN(%s)"</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">%</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">fieldDelimited</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">', '</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">join</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">map</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">str</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> valueList</SPAN><SPAN class="pun" style="color: #000000;">)))</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">return</SPAN><SPAN class="pln" style="color: #000000;"> whereClause</SPAN>

   

<SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="kwd" style="color: #00008b;">def</SPAN><SPAN class="pln" style="color: #000000;"> selectRelatedRecords</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> destinationTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> foreignKeyField</SPAN><SPAN class="pun" style="color: #000000;">):</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;">## Defines the record selection from the record selection of the OriginTable</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;">## and applys it to the DestinationTable using a SQL WHERE clause built</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;">## in the previous defintion"""</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Set the SearchCursor to look through the selection of the OriginTable</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; sourceIDs </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> set</SPAN><SPAN class="pun" style="color: #000000;">([</SPAN><SPAN class="pln" style="color: #000000;">row</SPAN><SPAN class="pun" style="color: #000000;">[</SPAN><SPAN class="lit" style="color: #800000;">0</SPAN><SPAN class="pun" style="color: #000000;">]</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="kwd" style="color: #00008b;">for</SPAN><SPAN class="pln" style="color: #000000;"> row </SPAN><SPAN class="kwd" style="color: #00008b;">in</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">da</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">SearchCursor</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> sqlExp</SPAN><SPAN class="pun" style="color: #000000;">)])</SPAN>

    

<SPAN class="com" style="color: #808080;"># Establishes the where clause used to select records from DestinationTable</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; whereClause </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> buildWhereClauseFromList</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">destinationTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> foreignKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> sourceIDs</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Process: Select Layer By Attribute</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">SelectLayerByAttribute_management</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">destinationTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> selectType</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> whereClause</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="com" style="color: #808080;"># Process: Select related records between OriginTable and DestinationTable</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; selectRelatedRecords</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> destinationTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> foreignKeyField</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">

&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">del</SPAN><SPAN class="pln" style="color: #000000;"> tables</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> tableName</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> tableNameList</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> primaryKeyField</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> foreignKeyField
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">del</SPAN><SPAN class="pln" style="color: #000000;"> originTable</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> destinationTable</SPAN>

 

<SPAN class="com" style="color: #808080;">#If Link isn't in mxd, send a message</SPAN><SPAN class="pln" style="color: #000000;">
</SPAN><SPAN class="kwd" style="color: #00008b;">else</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">AddMessage</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="str" style="color: #800000;">"CR_Link and CR_Catalog tables must be added to dataframe in order to create selection in CR_Link"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">


</SPAN><SPAN class="kwd" style="color: #00008b;">for</SPAN><SPAN class="pln" style="color: #000000;"> lyr </SPAN><SPAN class="kwd" style="color: #00008b;">in</SPAN><SPAN class="pln" style="color: #000000;"> layers</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; fieldList </SPAN><SPAN class="pun" style="color: #000000;">=</SPAN><SPAN class="pln" style="color: #000000;"> arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">ListFields</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">lyr</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="pln" style="color: #000000;">dataSource</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="str" style="color: #800000;">"REG_CODE"</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">if</SPAN><SPAN class="pln" style="color: #000000;"> len</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">fieldList</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="pun" style="color: #000000;">==</SPAN><SPAN class="pln" style="color: #000000;"> </SPAN><SPAN class="lit" style="color: #800000;">1</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp; arcpy</SPAN><SPAN class="pun" style="color: #000000;">.</SPAN><SPAN class="typ" style="color: #2b91af;">SelectLayerByAttribute_management</SPAN><SPAN class="pun" style="color: #000000;">(</SPAN><SPAN class="pln" style="color: #000000;">lyr</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> selectType</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> sqlExp</SPAN><SPAN class="pun" style="color: #000000;">)</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">else</SPAN><SPAN class="pun" style="color: #000000;">:</SPAN><SPAN class="pln" style="color: #000000;">
&nbsp;&nbsp; </SPAN><SPAN class="kwd" style="color: #00008b;">pass</SPAN><SPAN class="pln" style="color: #000000;">


</SPAN><SPAN class="kwd" style="color: #00008b;">del</SPAN><SPAN class="pln" style="color: #000000;"> lyr</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> layers</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> 
</SPAN><SPAN class="kwd" style="color: #00008b;">del</SPAN><SPAN class="pln" style="color: #000000;"> sqlExp</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> fld</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> mxd</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> df</SPAN><SPAN class="pun" style="color: #000000;">,</SPAN><SPAN class="pln" style="color: #000000;"> fieldList </SPAN>

Vind meer berichten getagd met

Accepted answers

All comments

MSDC

Although I was able to modify that script to fit my needs and get it to run.... I still get errors when the 'keyview' table has records selected where REG_CODE = 'NER'. I don't get it. There aren't null values in there. This is insane.

DanPatterson_Retired

Thanks Bruce...zips and iThingy don't get along, I will check later. But in short all set operations can be used then I presume which would be nice and hopefully cut out all that syntax error stuff.

MSDC

Tried it. I'd have to first do a full selection on the first table (CR_Catalog) instead of using arcpy.da.SearchCursor. I see how it can get me around a potential sql formatting problem but I'm not convinced that's what's going on here. Still, it might be an option.

bharold

The code makes Python sets from field values and does comparisons between sets.

No joins required, no SQL. If you can even only download the zipfile the .py is there.

MSDC

Sorry... typo. CR_ID IN ('{guid}', '{guid}', etc)

DanPatterson_Retired

can't open on iThingy...got a snippet from there demonstrating the principles Bruce?

DanPatterson_Retired

did you copy that or type it since there is an unclosed ] after the first guid ( IN ('{guid X }'

MSDC

That gives me: CR_ID IN ('{guid]}', '{guid}', etc).

Basically, a list of string values that come from the field CR_ID from the CR_Catalog table. I get these returned no matter the REG_CODE value I choose... even 'NER'.

bharold

Please take a look at this as an alternate solution:

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

Regards

DarrenWiens2

What is the result of 'print whereClause'?

DanPatterson_Retired

can you format your code using Python code blocks in the Syntax Highligher section, >>> It is hard to follow the indentation

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.