Select to view content in your preferred language

Calculate Field on Joined Table error in arcpy

264
2
Jump to solution
a week ago
Labels (1)
LoriEmersonKDOT
Regular Contributor

I successfully created python steps in Notebook, but when I converted to python script that I run from my toolbox it fails.

After joining a table to a feature class, the calculate field fails saying that I have an invalid field (ERROR 000530).

import arcpy
param1 = r"\\mynetworkpath\ResearchAndMaterialsCenter.gdb"

arcpy.management.MakeTableView(
    in_table=param1 + r"\PavedShoulder_GenerateNearTable_SameRouteDirection",
    out_view="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    where_clause="NEAR_DIST <> 0",
    workspace=None,
    field_info="OBJECTID OBJECTID VISIBLE NONE;IN_FID IN_FID VISIBLE NONE;NEAR_FID NEAR_FID VISIBLE NONE;NEAR_DIST NEAR_DIST VISIBLE NONE;NEAR_RANK NEAR_RANK VISIBLE NONE;FROM_X FROM_X VISIBLE NONE;FROM_Y FROM_Y VISIBLE NONE;NEAR_X NEAR_X VISIBLE NONE;NEAR_Y NEAR_Y VISIBLE NONE"
)
arcpy.AddMessage("Make filtered Table View completed successfully")

arcpy.management.AddJoin(
    in_layer_or_view=param1 + r"\PavedShoulder_Dissolve_DanglePoints",
    in_field="OBJECTID",
    join_table="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    join_field="IN_FID",
    join_type="KEEP_ALL",
    index_join_fields="NO_INDEX_JOIN_FIELDS",
    rebuild_index="NO_REBUILD_INDEX",
    join_operation=""
)
arcpy.AddMessage("Add Join completed successfully")
    
# list all fields after join
print ("Fields after join:")
for field in arcpy.ListFields(param1 + r"\PavedShoulder_Dissolve_DanglePoints"):
    print (field.name)

arcpy.management.CalculateField(
    in_table="PavedShoulder_Dissolve_DanglePoints",
    field="PavedShoulder_Dissolve_DanglePoints.MyNearX",
    expression="!PavedShoulder__GenerateNearTable_03130017700_SameRouteDirection.NEAR_X!",
    expression_type="PYTHON3",
    code_block="",
    field_type="TEXT",
    enforce_domains="NO_ENFORCE_DOMAINS"
)
arcpy.AddMessage("Calculate Field MyNearX completed successfully")

 The output for printing the field names in my joined table is this :

Fields after join:
OBJECTID
Shape
RouteID
RouteDirection
SUM_Length_ft
ORIG_FID
DANGLE_LEN
POINT_X
POINT_Y
POINT_Z
MyNearX
MyNearY
PavedShoulder_Dissolve_DanglePoints_MyNearX  

 What is wrong with the joined fields that is causing the Calculate Field to give me an error?

0 Kudos
1 Solution

Accepted Solutions
LoriEmersonKDOT
Regular Contributor

When transitioning from testing my code in my current map, to using a script tool, I realized that I needed to make my feature class a layer using the MakeFeatureLayer tool first before joining to a table and calculating the field.  

Now, when I print a list of fields of my joined layer and table, the list is what I expected and the calculate field works.

Fields after join:
PavedShoulder_Dissolve_DanglePoints.OBJECTID
PavedShoulder_Dissolve_DanglePoints.Shape
PavedShoulder_Dissolve_DanglePoints.RouteID
PavedShoulder_Dissolve_DanglePoints.RouteDirection
PavedShoulder_Dissolve_DanglePoints.SUM_Length_ft
PavedShoulder_Dissolve_DanglePoints.ORIG_FID
PavedShoulder_Dissolve_DanglePoints.DANGLE_LEN
PavedShoulder_Dissolve_DanglePoints.POINT_X
PavedShoulder_Dissolve_DanglePoints.POINT_Y
PavedShoulder_Dissolve_DanglePoints.POINT_Z
PavedShoulder_Dissolve_DanglePoints.MyNearX
PavedShoulder_Dissolve_DanglePoints.MyNearY
PavedShoulder_Dissolve_DanglePoints.PavedShoulder_Dissolve_DanglePoints_MyNearX
PavedShoulder_GenerateNearTable_SameRouteDirection.OBJECTID
PavedShoulder_GenerateNearTable_SameRouteDirection.IN_FID
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_FID
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_DIST
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_RANK
PavedShoulder_GenerateNearTable_SameRouteDirection.FROM_X
PavedShoulder_GenerateNearTable_SameRouteDirection.FROM_Y
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_X
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_Y
import arcpy
param1 = r"\\mynetworkpath\ResearchAndMaterialsCenter.gdb"

arcpy.management.MakeFeatureLayer(
    in_features="PavedShoulder_Dissolve_DanglePoints",
    out_layer="PavedShoulder_Dissolve_DanglePoitns_View",
    where_clause="",
    workspace=None,
    field_info=None
)

arcpy.management.MakeTableView(
    in_table="PavedShoulder_GenerateNearTable_SameRouteDirection",
    out_view="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    where_clause="NEAR_DIST <> 0",
    workspace=None,
    field_info="OBJECTID OBJECTID VISIBLE NONE;IN_FID IN_FID VISIBLE NONE;NEAR_FID NEAR_FID VISIBLE NONE;NEAR_DIST NEAR_DIST VISIBLE NONE;NEAR_RANK NEAR_RANK VISIBLE NONE;FROM_X FROM_X VISIBLE NONE;FROM_Y FROM_Y VISIBLE NONE;NEAR_X NEAR_X VISIBLE NONE;NEAR_Y NEAR_Y VISIBLE NONE"
)
arcpy.AddMessage("Make filtered Table View completed successfully")

arcpy.management.AddJoin(
    in_layer_or_view="PavedShoulder_Dissolve_DanglePoitns_View",
    in_field="OBJECTID",
    join_table="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    join_field="IN_FID",
    join_type="KEEP_ALL",
    index_join_fields="NO_INDEX_JOIN_FIELDS",
    rebuild_index="NO_REBUILD_INDEX",
    join_operation=""
)
arcpy.AddMessage("Add Join completed successfully")
    
# list all fields after join
print ("Fields after join:")
for field in arcpy.ListFields("PavedShoulder_Dissolve_DanglePoitns_View"):
    print (field.name)

arcpy.management.CalculateField(
    in_table="PavedShoulder_Dissolve_DanglePoitns_View",
    field="PavedShoulder_Dissolve_DanglePoints.MyNearX",
    expression="!PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_X!",
    expression_type="PYTHON3",
    code_block="",
    field_type="TEXT",
    enforce_domains="NO_ENFORCE_DOMAINS"
)

 

 

View solution in original post

0 Kudos
2 Replies
MErikReedAugusta
MVP Regular Contributor

If there's an active attribute join, normally the field names end up with a qualifier to say which table/feature class they come from.

If you look at Line 32 and Line 33, they each show the qualified names, where the bit before the dot is the source and the bit after the dot is the field name.  Given we're working with joined data, this is as I'd expect.

What's weird here is your printout from Lines 25–28, though.  Based on the documentation, I thought field.name would produce the full qualified name and field.baseName would provide the unqualified name.  Based on your printout, that's either not correct, or you somehow don't have a join active at that point in the code.

The other issue I see is that the Near_X field referenced in Line 33 doesn't appear in your printout—with or without the qualified prefix.  Line 32 is similar, but not entirely.  Compare the field name there against the lats entry in your printout.  The former has a dot separating the source and the field name; the latter has an underscore.


To troubleshoot this, I would recommend temporarily removing the Calculate_Field portion and anything after it from your script—but remember to save a copy of that code elsewhere, though!

Then, manually open the attribute table or fields view and see what field names (not aliases) are being displayed.

You could also try adding a printout before the join, as well, and comparing the two printout results.

------------------------------
M Reed
"The pessimist may be right oftener than the optimist, but the optimist has more fun, and neither can stop the march of events anyhow." — Lazarus Long, in Time Enough for Love, by Robert A. Heinlein

If this post or another helped you out, please consider giving the post(s) a Kudo or marking them as the Solution. ESRI Staff use both of these features to help keep track of posts on these forums.
0 Kudos
LoriEmersonKDOT
Regular Contributor

When transitioning from testing my code in my current map, to using a script tool, I realized that I needed to make my feature class a layer using the MakeFeatureLayer tool first before joining to a table and calculating the field.  

Now, when I print a list of fields of my joined layer and table, the list is what I expected and the calculate field works.

Fields after join:
PavedShoulder_Dissolve_DanglePoints.OBJECTID
PavedShoulder_Dissolve_DanglePoints.Shape
PavedShoulder_Dissolve_DanglePoints.RouteID
PavedShoulder_Dissolve_DanglePoints.RouteDirection
PavedShoulder_Dissolve_DanglePoints.SUM_Length_ft
PavedShoulder_Dissolve_DanglePoints.ORIG_FID
PavedShoulder_Dissolve_DanglePoints.DANGLE_LEN
PavedShoulder_Dissolve_DanglePoints.POINT_X
PavedShoulder_Dissolve_DanglePoints.POINT_Y
PavedShoulder_Dissolve_DanglePoints.POINT_Z
PavedShoulder_Dissolve_DanglePoints.MyNearX
PavedShoulder_Dissolve_DanglePoints.MyNearY
PavedShoulder_Dissolve_DanglePoints.PavedShoulder_Dissolve_DanglePoints_MyNearX
PavedShoulder_GenerateNearTable_SameRouteDirection.OBJECTID
PavedShoulder_GenerateNearTable_SameRouteDirection.IN_FID
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_FID
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_DIST
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_RANK
PavedShoulder_GenerateNearTable_SameRouteDirection.FROM_X
PavedShoulder_GenerateNearTable_SameRouteDirection.FROM_Y
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_X
PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_Y
import arcpy
param1 = r"\\mynetworkpath\ResearchAndMaterialsCenter.gdb"

arcpy.management.MakeFeatureLayer(
    in_features="PavedShoulder_Dissolve_DanglePoints",
    out_layer="PavedShoulder_Dissolve_DanglePoitns_View",
    where_clause="",
    workspace=None,
    field_info=None
)

arcpy.management.MakeTableView(
    in_table="PavedShoulder_GenerateNearTable_SameRouteDirection",
    out_view="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    where_clause="NEAR_DIST <> 0",
    workspace=None,
    field_info="OBJECTID OBJECTID VISIBLE NONE;IN_FID IN_FID VISIBLE NONE;NEAR_FID NEAR_FID VISIBLE NONE;NEAR_DIST NEAR_DIST VISIBLE NONE;NEAR_RANK NEAR_RANK VISIBLE NONE;FROM_X FROM_X VISIBLE NONE;FROM_Y FROM_Y VISIBLE NONE;NEAR_X NEAR_X VISIBLE NONE;NEAR_Y NEAR_Y VISIBLE NONE"
)
arcpy.AddMessage("Make filtered Table View completed successfully")

arcpy.management.AddJoin(
    in_layer_or_view="PavedShoulder_Dissolve_DanglePoitns_View",
    in_field="OBJECTID",
    join_table="PavedShoulder_GenerateNearTable_SameRouteDirection_View",
    join_field="IN_FID",
    join_type="KEEP_ALL",
    index_join_fields="NO_INDEX_JOIN_FIELDS",
    rebuild_index="NO_REBUILD_INDEX",
    join_operation=""
)
arcpy.AddMessage("Add Join completed successfully")
    
# list all fields after join
print ("Fields after join:")
for field in arcpy.ListFields("PavedShoulder_Dissolve_DanglePoitns_View"):
    print (field.name)

arcpy.management.CalculateField(
    in_table="PavedShoulder_Dissolve_DanglePoitns_View",
    field="PavedShoulder_Dissolve_DanglePoints.MyNearX",
    expression="!PavedShoulder_GenerateNearTable_SameRouteDirection.NEAR_X!",
    expression_type="PYTHON3",
    code_block="",
    field_type="TEXT",
    enforce_domains="NO_ENFORCE_DOMAINS"
)

 

 

0 Kudos