Model Builder calculate field expression

[2Quiker]

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

OBJECTID	PermitNum	ParcelNum1	Permits
1	aa1123	12345	12345, 12346
2	aa1123	12346
3	BB3361	67891	67891, 67892, 37893
4	BB3361	67892
5	BB3361	67893
6	UU9832	11123	11123
7	PP2225	6989	6988, 6989
8	PP2225	6988

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)).

[JakeSkinner]

@2Quiker,

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

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

[2Quiker]

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

[JakeSkinner]

You can add this logic to the Calculate Field tool:

Or you can add a script to a model:

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

[2Quiker]

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:

[RhettZufelt]

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_

[2Quiker]

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

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

[RhettZufelt]

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?

[RhettZufelt]

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_

[2Quiker]

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)).