Issues with joining and calculating fields in Model Builder

[SimonCrutchley]

Hi folks, I’ve been trying to automate a process in Model Builder, but it refuses to work and I don’t understand why. I’m trying to make a join based on attribute data and then use calculate field to populate a field in the original layer, but it’s just not working. Although the join claims to have worked, I cannot access the joined fields, and so cannot complete the process. The weird thing is that if I close MB and do it all as separate processes, it works fine. I assume that I’m doing something wrong, but I don’t know what.

I attach a subset of the data I’m trying to join (join_testing) and the join table (Period_list). What I’m trying to do is join the PER1 in 'join_testing' to NAME in 'Period_list' and use it to populate PERCODE1 in 'join_testing' with the CODE field from 'PERIOD_list'. I then want to remove the original joins and then join PER2 to NAME in the joining table and use it to populate PERCODE2 etc, but I can’t even get to the first step.

Any help would be most appreciated.

Thank you.

[RichardHowe]

Before I taught myself python, then I used to get irritated when I was trying to do something in model builder and users would suggest pythonic solutions...so I apologise that I am doing something similar. What I will say though is that update cursors are game changing for processes like the one you describe and insanely quick - there's some excellent walkthroughs, descriptions and examples here. You could do all your calculations at once.

I seem to remember this kind of process being a total pain in model builder in ArcMap because you need a variable name in the field calculation value for the post-join code, so it doesn't shock me to find that it's still a pain in Pro

[SimonCrutchley]

Hi Richard,

Thanks for that, but I’m a little confused. This is the code that appears to be what I want

Using a Python Dictionary Built using a da SearchCursor to Replace a Join Connecting Two Feature Classes:

 Example 1 - Transfer of a Single Field Value between Feature Classes

from time import strftime

print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S"))

import arcpy

sourceFC = r"C:\Path\SourceFeatureClass"

sourceFieldsList = ["JoinField", "ValueField"]

# Use list comprehension to build a dictionary from a da SearchCursor

valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\Path\UpdateFeatureClass"

updateFieldsList = ["NAME", "ValueField"]

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:

    for updateRow in updateRows:

        # store the Join value of the row being updated in a keyValue variable

         keyValue = updateRow[0]

         # verify that the keyValue is in the Dictionary

        if keyValue in valueDict:

             # transfer the value stored under the keyValue from the dictionary to the updated field.

            updateRow[1] = valueDict[keyValue][0]

            updateRows.updateRow(updateRow)

del valueDict

print( "Finished script: " + strftime("%Y-%m-%d %H:%M:%S"))

The guidance says to replace various bits e.g.

sourceFC = r"C:\Path\SourceFeatureClass” which I assume becomes

sourceFC = r" F:\Data\GIS\0000Monument_v24 (002)\PERIOD_list.xls" and

updateFC = r"C:\Path\UpdateFeatureClass” which I assume becomes

updateFC = r" F:\Data\GIS\0000Process\Process.gdb\PERIOD_testing" but also to

‘change lines 9 and 16 to replace the field list with the name of the Join field and the name of the field being transferred.’

I’m not sure which value I’m replacing with which value, but more importantly I’m not quite sure how the whole ‘Python’ bit runs.

From my extremely limited use, I know that there is the first box where you define what your  field equals and then there’s the ‘Code block’. I assume that the code here is the code block, but I don’t know what the “=” box needs to be.

Sorry, but as you can tell I’m a complete novice.

[RichardHowe · Accepted solution]

@SimonCrutchley you;re really close.

Below is what I think your code should look like  (albeit with my fil;e paths in), with a caveat. It's best that you convert your excel file into a table. You can do this using the excel to table tool in pro before you start and putting it in a geodatabase. After that the code is as below (I've put some comments to make it clear)

from time import strftime

print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S"))

import arcpy

sourceFC = r"C:\temp\Coding.gdb\DemoTablePeriod" # The explicit path to your imported table

sourceFieldsList = ["Name", "Short_Name"] #The first entry is the name of the field you are using for the join, the second entry s the name of the field that contains the value you wish to transfer

# Use list comprehension to build a dictionary from a da SearchCursor

valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)}

updateFC = r"C:\temp\Join_testing_MB.shp" # The explicit path to your shapefile

updateFieldsList = ["PERIOD", "percode1"] #The first entry is the name of the field you are using for the join, the second entry is the name of the field you wish to write your values into

with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows:

    for updateRow in updateRows:

        # store the Join value of the row being updated in a keyValue variable

        keyValue = updateRow[0]

         # verify that the keyValue is in the Dictionary

        if keyValue in valueDict:

             # transfer the value stored under the keyValue from the dictionary to the updated field.

            updateRow[1] = valueDict[keyValue][0]

            updateRows.updateRow(updateRow)

del valueDict

print( "Finished script: " + strftime("%Y-%m-%d %H:%M:%S"))

 
Then all you need to do is click "Analysis" at the top of your Pro window to open the Analysis menu and where it says "python" in the top left hand corner, click the arrow next to it and choose "Python window". The just paste the code into that window and hit enter twice to run it. You should then hopefully get a start script and finished script message, and then if you open your shapefile then the specified field should now be populated

[SimonCrutchley]

Brilliant, thank you.

That worked for Per1 to PERCode1 and filled the field. However, when I tweaked the code to Per2 and PERCode2 it didn't, and I don't understand why. The underlying tables are the same, it's just different fields in the target layer, so I don't see why it doesn't work. Do I need to change something else other than line 17?

Thanks

[RichardHowe]

Simon, no reason why it shouldn't work with a tweak of Line 17. Can you give it another go, it works ok for me? Obviously Per2 isn't fully attributed, so it won't calculate a value where per2 is empty. Screenshot of my output when tweaking Line 17 and running a second time