I am currently building a script for a script tool that will join data from a multitude of tables into a single table based on a common join field. Now, I have attempted to use arcpy.addJoin_management(…) to join data from a linkTable to a primaryTable, and received the following tool error message (ERROR 000990):
arcgisscripting.ExecuteError: Failed to execute. Parameters are not valid.
WARNING 000970: The join field METER_GRIDNO in the join table Customer_Accounts is
not indexed. To improve performance, we recommend that an index be created for the
join field in the join table.
ERROR 000990: All_Meters is already joined
Failed to execute (AddJoin).
Here is the code leading up to the point of failure:
# Create table views or feature layers in order to join data using
# the Add Join tool.
if primaryTableIsFC:
arcpy.MakeFeatureLayer_management(_primaryTable, primaryTableView)
else:
arcpy.MakeTableView_management(_primaryTable, primaryTableView)
arcpy.MakeTableView_management(_linkTable, linkTableView)
# Join link table coordinates and other data to primary table.
arcpy.AddJoin_management(primaryTableView, _primaryJoinField,
linkTableView, _linkJoinField, "KEEP_COMMON")
I have received this error message regardless of what tables or feature classes I use, and have tried inputting parameters of varying types into the tool to no effect. When I tried to "remove" the join indicated by the error message, I receive an error that the table file cannot be opened.
What is strange is that there is no problem executing this code segment in ArcMap via the 'Python Window' when I use full path names to the files.
From my own troubleshooting in ArcMap I have discerned that the Add Join tool will give this error when the only the join table basename for the feature class is supplied, but will work when the full path to the table is used. This is not the case outside of ArcMap however.
For a relatively simple tool, it is surprising to be having such difficulties executing it inside of an IDE (Eclipse Juno, to be precise). If someone out there knows enough about the intricacies of the arcgisscripting module from which this tool operates to provide some insight as to what might be going wrong, I'd greatly appreciate the help. Also, I'm in the process of learning python scripting using arcpy, so if there is something simple that I missed, please let me know.
Full Code Up to Error:
From module construct_analysis_fc:
# Coded input of parameters, for debug and testing purposes
outputPath = (r"G:\Projects\Water_Resources\WaterEfficiencyTargeting" +
os.path.sep + r"GIS_Data\WaterUseByBillingTier.gdb")
importDir = None
fileFilterList = None
outputName = "Customer_Accounts_Spatial"
primaryTable = "Customer_Accounts"
primaryJoinField = "METER_GRIDNO"
linkTablePath = (r"G:\Projects\Water_Resources\WaterEfficiencyTargeting" +
os.path.sep + "GIS_Data\WaterUseByBillingTier.gdb\All_Meters")
linkJoinField = "METER_GRIDNO"
linkXYFields = ["LONGITUDE", "LATITUDE"]
linkOtherFields = []
joinTables = []
fieldList = ["Customer_No", "Account_No", "Service_Address",
"Service_City", "Service_Zip","METER_GRIDNO",
"Account_Type", "Adjustment_Form", "Read_Date",
"Bill_Date", "Max_Tier", "Billing_Cycle_Days",
"Bill_Number", "Billed_Consumption","Billed_Charges",
"LONGITUDE", "LATITUDE", "Landscape_Area"
]
constructMethod = "XY_LINK"
# Prepare workspace and environment variables
if not scratchPath:
scratchPath = arcpy.env.workspace
scratch = workspace_setup.getScratchWorkspace(scratchPath)
arcpy.env.workspace = outputPath
arcpy.env.overwriteOutput = True
# Create geodatabase tables from imported table files.
if importDir:
import_xlsx_data.importXlsxData(importDir, outputPath, fileFilterList)
# Initialize data containers for processing
primaryTableInfo = [primaryTable, primaryJoinField]
linkTableInfo = [linkTablePath, linkJoinField, linkXYFields, linkOtherFields]
joinTablesInfo = fc_constructor.create_field_info_dict(joinTables, fieldList)
# Use methods of a `FeatureClassConstructor` class instance to build the
# desired output feature class.
fc_constructor_ = fc_constructor.FeatureClassConstructor(outputPath)
if constructMethod == fc_constructor.GEOCODE:
pass
elif constructMethod == fc_constructor.XY_LINK:
fc_constructor_.construct_xy_linked_fc(outputName, primaryTableInfo,
linkTableInfo, False, joinTablesInfo)
elif constructMethod == fc_constructor.COMPREHENSIVE:
pass
else:
arcpy.AddError(constructMethod + " is an invalid construction method.")
exit(1)
From module fc_constructor:
def construct_xy_linked_fc(self, outputName, primaryTableInfo, linkTableInfo,
writeUnmatched=False, joinTablesInfo=None):
"""Join coordinate data from reference table to primary table and join
data from additional tables based on a common join field. Return a
feature class containing all specified input data.
The `primaryTableInfo` argument specifies the table to which spatial
information and all other data be joined to via a join field. It is
supplied in the following form:
primaryTableInfo = [<"tableName">, <"joinField">]
The `linkTableInfo` argument specifies a table containing coordinate
and optionally other data to be joined to the primary table, allowing
a feature layer to be exported to a feature class as output. It is
supplied in the following form:
linkTableInfo = [<"tablePath">, <"joinField">, <["xField", "yField"]>,
<["fields"]>]
where <"tablePath"> is a complete path to the table, <"joinField">
is the field to join by, <("xField", "yField")> is a list of fields
specifying coordinate information, and <["fields"]> is a list of
fields to also be joined to the primary table. In the case where
the link table resides in the same workspace as the primary table,
<"tablePath"> can simply be the name of the link table.
`writeUnmatched` specifies weather to create a supplementary table
containing the link IDs of records in the primary table that have no
matching records in the link table.
`joinTablesInfo` specifies a dictionary of table name - field detail
tuple key-value pairs indicating what field data from other tables
should be joined to the primary table. It is supplied in the following
form:
joinTablesInfo = {<"tableName_01":[field_01, ...]>, ...}
where each `tableName` key is linked to a list of field names and types
to be joined to the primary table. All of the tables MUST have the same
join field as in the primary table in order to be joined.
"""
env.workspace = self.outputPath
#curEnv = env.workspace
_outputFC = outputName + u"_spatial"
_primaryTable = primaryTableInfo[0]
_primaryJoinField = primaryTableInfo[1]
_linkTablePath = linkTableInfo[0]
_linkJoinField = linkTableInfo[1]
_linkXYFields = linkTableInfo[2]
if len(linkTableInfo) == 4:
_linkFieldNames = linkTableInfo[3]
env.workspace = os.path.dirname(_linkTablePath)
_linkTable = os.path.basename(_linkTablePath)
_linkFields = ([fields for fields in arcpy.ListFields(_linkTable)
if fields.name in _linkFieldNames])
_linkFields.extend(_linkXYFields)
primaryTableView = _primaryTable + "_view"
linkTableView = _linkTable + "_view"
tempPrimaryXYLayer = _primaryTable + "_xy_lyr"
if writeUnmatched:
_unmatchedTable = _primaryTable + u"_Unmatched"
arcpy.CreateTable_management(self.outputPath, _unmatchedTable)
arcpy.AddField_management(_unmatchedTable, _primaryJoinField.name,
_primaryJoinField.type)
# Determine whether the input types are either tables or feature classes
primaryTableIsFC = False
if arcpy.Describe(_primaryTable).dataType == u"FeatureClass":
primaryTableIsFC = True
env.workspace = self.outputPath
#curEnv = env.workspace
# Create table views or feature layers in order to join data using
# the Add Join tool.
if primaryTableIsFC:
arcpy.MakeFeatureLayer_management(_primaryTable, primaryTableView)
else:
arcpy.MakeTableView_management(_primaryTable, primaryTableView)
arcpy.MakeTableView_management(_linkTable, linkTableView)
# Join link table coordinates and other data to primary table.
arcpy.AddJoin_management(primaryTableView, _primaryJoinField,
linkTableView, _linkJoinField, "KEEP_COMMON")
Additional information:
- ArcInfo License
- Version 10.0
- Service Pack 5
Thanks to those who can help!
Best Answer
Incredible...so having spent some time 'trying' different approaches to making this code work, I wrapped the code segment in question in a try / except block as such-
and the resulting tables were joined as expected. Apparently the error that
All_Meters is already joinedcame about after the result of a successful join from executing the tool, and in so doing caused the script to halt execution. But handling the exception was all that was needed to continue onwards. Not entirely sure why if the tool managed to join the tables that an error would come about, but this exception handing is the solution to this problem.I hope this helps others who encounter the same or similar problem executing tools that should work!!!