AnsweredAssumed Answered

Corner case on arcpy.CalculateField_management

Question asked by hua17 on Jun 21, 2018
Latest reply on Jun 27, 2018 by Dan_Patterson

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.

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

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

  1. arcpy.MakeTableView_management(mainTable, "tableView")
  2. arcpy.AddJoin_management("tableView", "Province_Short", lookupTable, "Prov_Short")
  3. arcpy.AddField_management(mainTable, "Province_Long", "TEXT", 100) 
  4. arcpy.CalculateField_management("tableView","MainTable.Province_Long","!LookupTable.Prov_Long!","PYTHON")
  5. 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

Outcomes