Corner case on arcpy.CalculateField_management

JoshuaChisholm — Sat, 11 Dec 2021 06:48:02 GMT

Hey GIS experts,

I ran into a strange problem with the arcpy.CalculateField_management tool. I'm not sure if this is a bug or just an error in my thinking, but I thought I'd post it just in case someone else has the same issue.

If you run the commands in this order they work fine.

arcpy.MakeTableView_management(mainTable, "tableView")
arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100)
arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short")
arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")
Result are as expected (the Province_Long field has data in it)

If you run the commands in this order they do not work.

arcpy.MakeTableView_management(mainTable, "tableView")
arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short")
arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100)
arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")
Result are not as expected (the Province_Long field has no data in it)
No error is thrown, meaning that the field Province_Long exists and can be found, but for some reason the data is not being retrieved from the joined table. This only occurs when you are calculated from a field from the joined table.

I also built a stand-alone script if someone wants to replicate the issue on their end. To change the script from not working to working, swap the two lines of code under #Swap these two lines

import arcview
import arcpy

#=====Allow overwrite=====
originalOverwriteOutput = arcpy.env.overwriteOutput
arcpy.env.overwriteOutput = True

#=====Build main table=====
mainTable=arcpy.CreateTable_management(arcpy.env.scratchGDB, "MainTable")
arcpy.AddField_management(mainTable, "Address", "TEXT", 100)
arcpy.AddField_management(mainTable, "Province_Short", "TEXT", 100)
with arcpy.da.InsertCursor(mainTable, ["Address","Province_Short"]) as cursor:
    cursor.insertRow(["123 Sesame Street","ON"])
    cursor.insertRow(["10 Downing Street","NS"])
    cursor.insertRow(["221B Baker Street","QC"])
    cursor.insertRow(["4 Privet Drive","BC"])
    
#=====Build lookup table=====
lookupTable=arcpy.CreateTable_management(arcpy.env.scratchGDB, "LookupTable")
arcpy.AddField_management(lookupTable, "Prov_Short", "TEXT", 100)
arcpy.AddField_management(lookupTable, "Prov_Long", "TEXT", 100)
with arcpy.da.InsertCursor(lookupTable, ["Prov_Short","Prov_Long"]) as cursor:
    cursor.insertRow(["NL","Newfoundland and Labrador"])
    cursor.insertRow(["PE","Prince Edward Island"])
    cursor.insertRow(["NS","Nova Scotia"])
    cursor.insertRow(["NB","New Brunswick"])
    cursor.insertRow(["QC","Quebec"])
    cursor.insertRow(["ON","Ontario"])
    cursor.insertRow(["MB","Manitoba"])
    cursor.insertRow(["SK","Saskatchewan"])
    cursor.insertRow(["AB","Alberta"])
    cursor.insertRow(["BC","British Columbia"])
    cursor.insertRow(["YT","Yukon"])
    cursor.insertRow(["NT","Northwest Territories"])
    cursor.insertRow(["NU","Nunavut"])


#=====Run lookup=====
arcpy.MakeTableView_management(mainTable, "tableView")

#==================================================
#Swap these two lines
arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short") #If this line is first, calc field does nothing
arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100) #If this line is first, it works properly
#==================================================

arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")

#=====Print output=====
with arcpy.da.SearchCursor(mainTable, ["Address","Province_Short","Province_Long"]) as cursor:
    for row in cursor:
        print("Address: "+str(row[0])+", Province_Short: "+str(row[1])+", Province_Long: "+str(row[2]))
        arcpy.AddMessage("Address: "+str(row[0])+", Province_Short: "+str(row[1])+", Province_Long: "+str(row[2]))

#=====Reset overwrite=====
arcpy.env.overwriteOutput = originalOverwriteOutput‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Corner case on arcpy.CalculateField_management

JoshuaBixby — Thu, 21 Jun 2018 16:43:50 GMT

What product (ArcGIS Pro or ArcMap) and what version are you running? I just ran your script on ArcMap 10.6, and I get the same correct answers regardless of which line is first.

Re: Corner case on arcpy.CalculateField_management

JoshuaChisholm — Thu, 21 Jun 2018 17:00:40 GMT

Hello Bixby,

I'm using ArcMap 10.4.1.

Thanks for testing it out! I guess it's not an issue anymore.

Re: Corner case on arcpy.CalculateField_management

DanPatterson_Retired — Sat, 23 Jun 2018 01:23:59 GMT

Hey Josh!... try PYTHON_9.3 don't know what or if that would make a difference.

Your group should look into PRO soon, Python 3.6 is out and the support life of python 2.7 ends in 2010

Re: Corner case on arcpy.CalculateField_management

JoshuaChisholm — Wed, 27 Jun 2018 19:36:47 GMT

Hey Dan! Thanks for replying I've tried PYTHON_9.3, same problem.

Sounds like it's time for PRO...I guess that means my education has become dated

(also I think 2010 was a type. Looks like 2.7 support ends in 2020)

Re: Corner case on arcpy.CalculateField_management

DanPatterson_Retired — Thu, 28 Jun 2018 03:40:06 GMT

support ends... but don't expect anything new beyond a fix...

Besides python 3.8 is in development.. it is almost time for the 4.0 series

topic Re: Corner case on arcpy.CalculateField_management in Python Questions

Corner case on arcpy.CalculateField_management

Re: Corner case on arcpy.CalculateField_management

Re: Corner case on arcpy.CalculateField_management

Re: Corner case on arcpy.CalculateField_management

Re: Corner case on arcpy.CalculateField_management

Re: Corner case on arcpy.CalculateField_management