Model Builder calculate field expression

1218
11
03-16-2022 11:08 AM
2Quiker
Occasional Contributor II

I have a model that I would like to use the the following code to update a field "Permits" based on how many text occurrences are in "ParcelNum1" field based on the "PermitNum" field. I get the following error and need help figuring out what the issues is and all fields are text fields. I need this in model builder.

Example

OBJECTIDPermitNumParcelNum1Permits
1aa11231234512345, 12346
2aa112312346 
3BB33616789167891, 67892, 37893
4BB336167892 
5BB336167893 
6UU98321112311123
7PP222569896988, 6989
8PP22256988 

Pre-Logic Script code:

 

 

 

 

 

 

dict1 = dict()
with arcpy.da.SearchCursor('TableTest6',['ParcelNum1','Permit_Count','PermitNum']) as cursor:
    for row in cursor:
        if row[0] not in (None, "", " "):
            dict1.setdefault(row[0],[]).append(str(row[2]))
        
def concat(nf):
    x = None
    if nf in dict1t:
        x = ",".join(dict1[nf])
    return x

 

 

 

 

 

 

Permits = conact(!ParcelNum1! )

 

Error.

ERROR 000539: Runtime error
Traceback (most recent call last):
File "<string>", line 2, in <module>
RuntimeError: cannot open 'TableTest6'

Failed to execute (Calculate Field (4)).

0 Kudos
11 Replies
JakeSkinner
Esri Esteemed Contributor

@2Quiker,

Does it matter if the Permits field is populated for all rows?  Ex:

JakeSkinner_0-1647456504380.png

 

If not, you could do something like below:

dict = {}
with arcpy.da.SearchCursor(table, ['PermitNum1', 'ParcelNum']) as cursor:
    try:
        for row in cursor:
            dict.setdefault(str(row[0]), [])
            dict[str(row[0])].append(str(row[1]))
    except:
        pass
del cursor

with arcpy.da.UpdateCursor(table, ['PermitNum1', 'Permits']) as cursor:
    try:
        for row in cursor:
            permitsList = dict[row[0]]
            permits = ', '.join(permitsList)
            row[1] = permits
            cursor.updateRow(row)
    except:
        pass
del cursor
0 Kudos
2Quiker
Occasional Contributor II

Thanks for the response Jake but I was trying to this in Model Build with Calculate field.

0 Kudos
JakeSkinner
Esri Esteemed Contributor

You can add this logic to the Calculate Field tool:

JakeSkinner_0-1647465779715.png

 

 

Or you can add a script to a model:

https://desktop.arcgis.com/en/arcmap/latest/analyze/modelbuilder/integrating-scripts-within-a-model....

2Quiker
Occasional Contributor II

This did technically work but only if I add the Table path to the code,

Table = "C:\temp\temp.gdb\table"

with arcpy.da.SearchCursor(Table,['ParcelNum1','Permit_Count','PermitNum']) as cursor:

Does not work with the Add Field output "TableTest6".

with arcpy.da.SearchCursor("TableTest6",['ParcelNum1','Permit_Count','PermitNum']) as cursor:

0 Kudos
RhettZufelt
MVP Frequent Contributor

Are you running the entire model, or just the Calculate Field (4)?  Have you tried to re-validate and run the entire model again?

The error says cannot open 'TableTest6' which is output from Add Field (3).

If these are temp tables, they do not persist across sessions, and often you need to re-run the tool(s) that generate the input (TableTest6) or it won't find them.

R_

 

 

0 Kudos
2Quiker
Occasional Contributor II

I have re-validated and re-ran the model.

What do you mean by re-run the tool(s) that generated the input, example ?

0 Kudos
RhettZufelt
MVP Frequent Contributor

re-validate and re-run will do it.

Just meant to make sure that Add Field (3) runs right before the Calculate Field (4) runs to ensure that TableTest6 exists.

In the image, TableTest6 is the last one that shows a shadow behind it, meaning it has "ran".  If you were to save that model at that state, then open it up later, it will still show the shadow, but TableTest6 wouldn't exist (if it is temp table) until the tool that creates it is ran again.

 

Do you see it put TableTest6 in the TOC?

0 Kudos
RhettZufelt
MVP Frequent Contributor

Upon closer inspection, noticed a few other things that might be causing issues.

You have a def called "concat(nf)", but the Expression block says "conact(!ParcelNum1!)" which is spelled incorrectly, so shouldn't even run that def. (also spelled this way in the search cursor which could cause it to not open TableTest6 if the field in the cursor doesn't exist)

also, sending !ParcelNum1! to it ( conact(!ParcelNum1! )), which, according to the table you supplied doesn't exist.  It says ParcelNum.

also, !ParcelNum! is a field getting passed to the def as "nf", but I see no iterator to go through the rows of that field to evaluate it.  I think it is the dictionary you are wanting to iterate through, not a field.  Might need something like:

for key in dict1:
   x = ",".join(dict1[key])

 

And, I've never put stuff in the Code Block outside of a def before, so not sure if that would even run (though, the error tends to suggest it is).  I would move all of the code within the def to ensure it runs, and in the order expected.

R_

 

0 Kudos
2Quiker
Occasional Contributor II

Thank you for the replay, the example table I posted was incorrect and I have corrected it. I have verified that the fields are in the table and the code. The fields are  'PermitNum','ParcelNum1' & 'Permits". I have struggled with calculate fields calculations so I apologize if I am not understanding correctly. I have re-validated the entire model and re-ran it. This is in ArcCatolog and not in ArcMap or Pro.

I have the following based on comments but I am still getting the error.

 

def update():
    dict1 = dict()
    with arcpy.da.SearchCursor("TableTest6",['ParcelNum1','Permit_Count','PermitNum']) as cursor:
        for row in cursor:
            if row[0] not in (None, "", " "):
                dict1.setdefault(row[0],[]).append(str(row[2]))
    if key in dict1t:
        x = ",".join(dict1[key])
    return x

 

 

Error:

ERROR 000539: Error running expression: update()
Traceback (most recent call last):
File "<expression>", line 1, in <module>
File "<string>", line 3, in update
RuntimeError: cannot open 'TableTest6'

Failed to execute (Calculate Field (5)).

0 Kudos