Corner case on arcpy.CalculateField_management

Question

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

DanPatterson_Retired · Answer

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

JoshuaChisholm · Answer

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)

DanPatterson_Retired · Answer

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