Automating a Field Calculation Using 2 Datasets

BK710

I'm trying to populate a field based on the output of a "Field Join" between two datasets. Specifically:

IF an item in APN_Dataset "matches" (based on Field Join tool) an item in Parcel_Datset, THEN "JoinParcelDataset" field cell = name of Parcel_Dataset (there are 71 of these datasets that I also want to iterate through with "iterate dataset" in modelbuilder) 

My problem follows as such:

1. Moving the 71 datasets to a workspace in chronological order for sequential iteration (newest to oldest)

2. I do not know where to enter the SQL expression (calculate field/calculate value tool? inside of modelbuilder)

3. I only want the name of Parcel_Dataset to be assigned to the matching cell if the cell is <null>

This would be some kind of nested loop, inside one of the tools in model builder with an interator for the 71 datasets I would like to compare against a single table. Thanks for your help! 

 

JohannesLindner

Are you married to doing it with ModelBuilder? Because that seems like an easy job for Python (untested):

import os

parcels = [
    "path:/to/Parcel_Dataset_1",
    "path:/to/Parcel_Dataset_2",
    "path:/to/Parcel_Dataset_3",
    ]
apn = "path:/to/APN_Dataset"


# read the parcel data
parcel_data = dict()
for parcel in parcels:
    parcel_name = os.path.basename(parcel)
    join_field_values = [row[0] for row in arcpy.da.SearchCursor(parcel, ["JoinField"])]
    parcel_data[parcel_name] = join_field_values

# parcel_data looks like this:
#parcel_data = {
#    "Parcel_Dataset_1": [1, 2, 3, 4, 5, 6],
#    "Parcel_Dataset_2": [7, 8, 9],
#    "Parcel_Dataset_3": [1, 2, 5, 8, 10, 11],
#    }

# open an UpdateCursor on APN, filter only features where TargetField is empty
with arcpy.da.UpdateCursor(apn, ["JoinField", "TargetField"], "TargetField IS NULL") as cursor:
    # loop throguh the APN features
    for join_value, parcel_name in cursor:
        # loop through the parcel datasets
        for p in parcel_data:
            if join_value in parcel_data[p]:
                # there is a match in this parcel dataset
                parcel_name = p
                # stop looping through the rest of the parcel datasets
                break
        # write the parcel dataset name to the APN dataset
        # note: if no match was found in all of the parcel datasets, parcel_name
        # will be null
        cursor.updateRow([join_value, parcel_name])

 

Then the problem would be simply how to sort your parcel datasets. You can input them in the correct order manually, but if they have a date or version number or something similar in their name, you can also do it automatically