Cannot Recognize Field when using Field Calculator on a Joined Field with Python

Question

I keep getting ERROR 000539 (Invalid field) when trying to use the calculate field on a joined feature class on my python script.

I am trying to create a spatial join between two feature classes, then copy the values from the join table (parcels) into the input table (cofo_geo).

I believe the issue is that the system won't accept the calculate expression for some reason, even though it worked flawlessly when I did it in ArcPro. I have tried doing the expression in Arcade and got the same result.

Here is a subset of my script for reference:

# import libraries
import arcpy

arcpy.env.workspace = #path
arcpy.env.overwriteOutput = True

cofo_geo = #path
parcels = #path

# add spatial join
arcpy.analysis.SpatialJoin(cofo_geo, parcels, 'cofo_SJ')
arcpy.management.AddJoin(cofo_geo, 'Permit_Number', 'cofo_SJ', 'Permit_Number')

arcpy.management.CalculateField(cofo_geo, 'Lot', '!cofo_SJ.LOT_1!', 'PYTHON3')

arcpy.management.RemoveJoin(cofo_geo)

ParkerWertz7 · Answer

Thanks Tony!

The issue was my join was not going through for some reason. The only reason I was trying to do it like this was because using the Add Spatial Join tool directly on the source layer makes it harder to use the Calculate Fields tool.

Every time I use the Add Spatial join tool, it iterates a number on the end of the field name (ex: _AddSpatialJoin_1, next time is _AddSpatialJoin_1, etc.) I was trying it for this reason but since its giving me less grief then using a simple join I think I try to find a workaround for this instead.

Tonyalmeida · Answer

When joined tables, the field names from the joined table are prefixed by the name of the join table, but the structure of the field reference might differ depending on the context.

try this,

# Import libraries
import arcpy

# Set workspace and overwrite settings
arcpy.env.workspace = r"path_to_workspace"  # Update the path
arcpy.env.overwriteOutput = True

# Define input paths
cofo_geo = r"path_to_cofo_geo"  # Update the path
parcels = r"path_to_parcels"  # Update the path

# Perform spatial join
cofo_sj = 'cofo_SJ'  # Name for the spatial join output
arcpy.analysis.SpatialJoin(cofo_geo, parcels, cofo_sj)

# Add the join
arcpy.management.AddJoin(cofo_geo, 'Permit_Number', cofo_sj, 'Permit_Number')

# List the fields to verify the join
fields = arcpy.ListFields(cofo_geo)
for field in fields:
    print(field.name)  # Print field names to ensure the join worked

# Calculate the field (ensure the field reference is correct)
try:
    arcpy.management.CalculateField(cofo_geo, 'Lot', "!cofo_SJ.LOT_1!", 'PYTHON3')
    print("Field calculation successful.")
except Exception as e:
    print(f"Error calculating field: {e}")

# Remove the join
arcpy.management.RemoveJoin(cofo_geo)