Field calc across join to table view

[KevinBell]

Why doesn't this work?!  I'm going nuts!

    addrTbl = r'Database Connections\Connection to blah.sde\blah.dbo.addr'
    parcelTbl = r'Database Connections\Connection to blah.sde\blah.dbo.parcel'
    arcpy.MakeTableView_management(parcelTbl, 'parcelTv')
    arcpy.MakeFeatureLayer_management('default.gdb\\bm_parcels_calc', 'calcLyr')
   
    arcpy.AddJoin_management('calcLyr', "PARCEL_PIN", 'parcelTv',"parcel_id","KEEP_ALL")
   
    # works to here.
   
    arcpy.CalculateField_management('calcLyr',"PARCEL_KEY", 'parcelTv.parcel_key')


Show me how to fix it and I'll buy you lunch at next years UC!

[JamesCrandall]

Don't you also need to specify the full layer.fieldname in the calculate (it looks like you did it for 'parcelTv.parcel_key' but what about "PARCEL_KEY"?)

arcpy.CalculateField_management('calcLyr',"originallyrname.PARCEL_KEY", 'parcelTv.parcel_key')

[RichardFairhurst]

Why doesn't this work?!  I'm going nuts!

    addrTbl = r'Database Connections\Connection to blah.sde\blah.dbo.addr'
    parcelTbl = r'Database Connections\Connection to blah.sde\blah.dbo.parcel'
    arcpy.MakeTableView_management(parcelTbl, 'parcelTv')
    arcpy.MakeFeatureLayer_management('default.gdb\\bm_parcels_calc', 'calcLyr')
   
    arcpy.AddJoin_management('calcLyr', "PARCEL_PIN", 'parcelTv',"parcel_id","KEEP_ALL")
   
    # works to here.
   
    arcpy.CalculateField_management('calcLyr',"PARCEL_KEY", 'parcelTv.parcel_key')


Show me how to fix it and I'll buy you lunch at next years UC!

I believe you forgot to qualify the calclyr PARCEL_KEY field with the underlying feature class name, especially since both the fc and table have the PARCEL_KEY field.

So I believe it should be (double check the single/double quotes also):

    arcpy.CalculateField_management('calcLyr','bm_parcels_calc.PARCEL_KEY', 'parcelTv.parcel_key')

[KevinBell]

I believe you forgot to qualify the calclyr PARCEL_KEY field with the underlying feature class name, especially since both the fc and table have the PARCEL_KEY field.

So I believe it should be (double check the single/double quotes also):

    arcpy.CalculateField_management('calcLyr','bm_parcels_calc.PARCEL_KEY', 'parcelTv.parcel_key')

Thanks, but no cigar...  the quote thing doesn't matter, and the second argument is derived from the first...  still stumped.  I'll likely pull the data into memory and try that, or use pyodbc if that tanks.

[KevinBell]

it turns out that python doesn't calc fields across inner joins...

dust off the old python dictionary and the work around is WAY faster than using the calculate field tool!

import arcpy
arcpy.env.workspace = r'E:\gis\engineering\20130806_SLCO_parcel_process'

parcelTbl = r'Database Connections\Connection to slcisql.sde\GEODB.dbo.parcel'

d = {}
flds = ['parcel_id', 'PARCEL_KEY', 'ADDR_SERIAL', 'UNIT', 'BLOCK', 'SUBDIVISION', 'LAND_USE_CODE']
with arcpy.da.SearchCursor(parcelTbl, flds) as c:
    for r in c:
        d[r[0]] = (r[1], r[2], r[3], r[4], r[5], r[6])
        
print len(d)
print d['07352010040000']

flds = ['PARCEL_PIN', 'PARCEL_KEY', 'ADDR_SERIAL', 'UNIT', 'BLOCK', 'SUBDIVISION', 'LAND_USE_CODE']
with arcpy.da.UpdateCursor('default.gdb\\bm_parcels_calc', flds) as cc:
    for rr in cc:
        if d.has_key(rr[0]):
            rr[1] = d[rr[0]][0]
            rr[2] = d[rr[0]][1]
            rr[3] = d[rr[0]][2]
            rr[4] = d[rr[0]][3]
            rr[5] = d[rr[0]][4]
            rr[6] = d[rr[0]][5]
            cc.updateRow(rr)
        else:
            pass
 

[RichardFairhurst]

While you are welcome to solve your problem without using a join and the field calculator, you are flat out wrong that Python does not calculate across inner joins.  I do it in practically every model I build and it works if I converted the Join and calculation from Model Builder to Python script.  You needed to build it using Model Builder with the query parser and then export it to Python before declaring that it cannot be done.  Here is an example that works every week for me:

# Process: Calculate First_STNAME Field...
arcpy.CalculateField_management(RDNUMBER_Routes_Layer, "RDNUMBER_Routes.First_STNAME", "[RDNUMBERS_ALL.FIRST_STNAME]", "VB", "")

So based on this example (which I had not looked up previously and could not test before) I can now point out what you needed to do to make your original code work.

You had:

arcpy.CalculateField_management('calcLyr',"PARCEL_KEY", 'parcelTv.parcel_key')

which had several errors.  You had not included the feature class qualifier for the field to be calculated, you had not enclosed the calculation expression with field delimiters and you had not specified the Parser.  So correcting your code to work, here is how the syntax should have been for a VB calculation (faster than Python with joins at 10.0):

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "[parcelTv.parcel_key]", "VB", "")

or for a Python calculation:

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "!parcelTv.parcel_key!", "PYTHON_9.3", "")

I also agree that the code you came up with is faster, especially as more fields are compared or transferred.  I have used that code technique recently to fill in Null values for 34 fields in a table prior to making those fields not accept Null values in a new feature class and it is definitely faster.  I just don't want misinformation about what can or cannot be done with Python using joins and the field calculator to go uncorrected.

[KevinBell]

Thanks for looking at this!

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "[parcelTv.parcel_key]", "VB", "")

##  I get this:
##  arcgisscripting.ExecuteError: ERROR 999999: Error executing function.
##  Failed to execute (CalculateField).

or for a Python calculation:

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "!parcelTv.parcel_key!", "PYTHON_9.3", "")

##  I get this:
##  arcgisscripting.ExecuteError: ERROR 000539: Invalid field parcelTv.parcel_key
##  Failed to execute (CalculateField).

...so I'm glad you are having success, but your recommendation isn't working though I suspect it's a data type thing.  The field I'm calculating is a Long, and the source is an OBJECTID, and it didn't like me trying to cast to long like this  

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", long("!parcelTv.parcel_key!"), "PYTHON_9.3", "")

...and with that I think I'll stick with the cursor method for the speed.  I'd love to testing the speed difference but as I can't get the high level tool method to work I suppose that's not going to happen.  I'd guess 100 times faster though.  😉

[RichardFairhurst · Accepted solution]

Thanks for looking at this!

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "[parcelTv.parcel_key]", "VB", "")

##  I get this:
##  arcgisscripting.ExecuteError: ERROR 999999: Error executing function.
##  Failed to execute (CalculateField).

or for a Python calculation:

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "!parcelTv.parcel_key!", "PYTHON_9.3", "")

##  I get this:
##  arcgisscripting.ExecuteError: ERROR 000539: Invalid field parcelTv.parcel_key
##  Failed to execute (CalculateField).

...so I'm glad you are having success, but your recommendation isn't working though I suspect it's a data type thing.  The field I'm calculating is a Long, and the source is an OBJECTID, and it didn't like me trying to cast to long like this  

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", long("!parcelTv.parcel_key!"), "PYTHON_9.3", "")

...and with that I think I'll stick with the cursor method for the speed.  I'd love to testing the speed difference but as I can't get the high level tool method to work I suppose that's not going to happen.  I'd guess 100 times faster though.  😉

You could get the tool to work if you built it in Model Builder and exported it to a Python Script.  Also the problem is not a field data type error.  Based on the error you posted, you have typed either an incorrect table name or an incorrect field name (most likely the table name) that does not actually exist in your join.  You could definitely fix this error with the Query Builder interface available in the Model Builder Field Calculator tool to get the correct join field references and then export that to a Python script to get exactly the right values for your script, or get the correct join field names within ArcMap by manually doing the join and using the Query Builder in its Field Calculator.  That is what I always do.  I very rarely ever write any Field Calculator expressions directly in Idle.

Also, there should be no need to cast the OBJECTID to a long.  I never do.  I just calc it straight in.

Reading your code that worked for cursors your table appears to be named "parcel" and not "parcelTv".  So these should work.

arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "[parcel.parcel_key]", "VB", "")


arcpy.CalculateField_management('calcLyr', "bm_parcels_calc.PARCEL_KEY", "!parcel.parcel_key!", "PYTHON_9.3", "")

But if they don't work, the above methods for building the query expressions in Model Builder of ArcMap should be followed.

[KevinBell]

ok, I never use model builder but went ahead on your recommendation and here's how it looks after export!

arcpy.CalculateField_management('bm_parcels_calc', "bm_parcels_calc.PARCEL_KEY", "!myDB.\"myNT\\myUsername\".%parcel.parcel_key!", "PYTHON_9.3", "")

"!myDB.\"myNT\\myUsername\".%parcel.parcel_key!" seriously!!!  I wouldn't have guessed!

[RichardFairhurst]

ok, I never use model builder but went ahead on your recommendation and here's how it looks after export!

arcpy.CalculateField_management('bm_parcels_calc', "bm_parcels_calc.PARCEL_KEY", "!myDB.\"myNT\\myUsername\".%parcel.parcel_key!", "PYTHON_9.3", "")

"!myDB.\"myNT\\myUsername\".%parcel.parcel_key!" seriously!!!  I wouldn't have guessed!

I would never have guessed either.  Anyway, at least now you know it can work with a Join and the Field Calculator.  Your code is almost certainly faster, especially since it appears to update 5 fields in one pass, where Field Calculator only can update 1 field in a pass and would have to be run 5 times to do the same.