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!
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