ArcPy: 999999 ERROR when using AddJoin_management

Question

I want to execute three joins in total. The first two joins work, but when I'm trying to join both resulting datasets I get the beloved 999999 ERROR.

# sr tables for joins
sr1Table = sr1Path + ".dbf"
sr2Table = sr2Path + ".dbf"
# create nw feature layer for join
nw1Layer = featureFolder + "nw1_layer"
nw2Layer = featureFolder + "nw2_layer"
arcpy.MakeFeatureLayer_management(nw1Path, nw1Layer)
arcpy.MakeFeatureLayer_management(nw2Path, nw2Layer)

# execute join on ID, sr1_layer will be joined to the nw1_layer
arcpy.SetProgressorLabel("Joining NW1 with SR1 ...")
arcpy.AddJoin_management(nw1Layer, "ID", sr1Table, "ID")
# execute join on ID, sr2_layer will be joined to the nw2_layer
arcpy.SetProgressorLabel("Joining NW2 with SR2 ...")
arcpy.AddJoin_management(nw2Layer, "ID", sr2Table, "ID")
# execute join on ID, nw2_layer will be joined to the nw1_layer
arcpy.SetProgressorLabel("Joining NW1 with NW2 ...")
arcpy.AddJoin_management(nw1Layer, "ID", nw2Layer, "ID")

Is this because I again have to create Feature Classes of nw1Layer + nw2Layer with CopyFeatures_management, then again use MakeFeatureLayer_management on the new Feature Classes and use these for the 3rd join? I try to avoid this, because I work with huge Shape Files and creating those Feature Classes would cost many hours of time.

Answer 1

Have you checked how the "ID" fields are named in the two layers (nw1, nw2)? When joining them with the table, they may be prepended with the input table name. So, in nw1Layer, the "ID" fields may look like "sr1.ID" and "nw1.ID" (depending on the actual table names). As a result, the final AddJoin_management may not be matching the simple "ID" with the actual joined layer's field names.

You can check this by adding a line like this after each of the first joins:

print [f.name for f in arcpy.ListFields(layer)]

For more information, see the documentation about qualified field names: http://pro.arcgis.com/en/pro-app/tool-reference/environment-settings/qualified-field-names.htm). This includes information about how to modify this behaviour using environment settings.

Answer 2

The solution was to again use CopyFeatures_management on the resulting feature layers to create new feature classes and MakeFeatureLayer_management to create new feature layers and use these for the final join. I also used the in_memory workspace instead to keep the performance at an acceptable level.