How To: Update a table with the changes made to a related table
ArcGIS for Desktop does not have a geoprocessing tool that automatically updates a table when a change is made to a related table; however, this can be accomplished with a model or a Python script.
The following examples describe two possible workflows using ModelBuilder and Python.
Consider a scenario where a group of professionals are tasked to find closed venues and to update the appropriate records from 'Open' to 'Closed'. After bringing this data into the lab, a secondary table must be updated according to the changes made in the first table. There are thousands of records to parse through, and an automated process is needed to update the records in the secondary table.
The requirements for the following procedures are two feature classes, knowledge of either ModelBuilder or Python, and each dataset must have a key field, which in an ArcMap session is used to relate one dataset to the other. For example:
The first feature class ("fc1") has:
- a key field "KeyFC1"
- a field ("NotesFC1") where the values are 'Open'
The second feature class ("fc2") has:
- a key field "KeyFC2"
- a field ("NotesFC2") where the values start off as 'Open'
In this scenario, selected records in "fc1" are manually changed to 'Closed'. The below model and script updates all related entries in "fc2" to 'Closed' as well.
Both the model and the Python script must be tailored to the specific data.
Method 1: Update a table when changes are made to a related table using ModelBuilder
1. Add the two feature classes to the model. Right-click each feature class and choose 'Model Parameter' so that this model prompts for the two input tables when run as a tool.
2. Add 'Make Feature Layer' to the model. Connect "fc2" (the feature class that is updated as a result of running the model) as the Input Feature to Make Feature Layer. The output is a layer ("fc2_Layer").
3. Add 'Add Join' to the model, which is a temporary join. Connect "fc1" (the feature class that is updated before running the model) as the Join Table. Connect "fc2_Layer" as the ‘Layer Name or Table View’. The output is another layer ("fc2_Layer (2)").
4. Add 'Calculate Field', which calculates the values of a field for a feature class, feature layer, or raster catalog. Have the following inputs:
a. Input Table: the latest layer ("fc2_Layer (2)")
b. Field Name: fc2.NotesFC2
c. Expression: change(!fc1.NotesFC1!, !fc2.NotesFC2!)
d. Expression Type: Python_9.3
e. Code Block:
def change(x, y):
if x == "Closed": return "Closed"
else: return y
Method 2: Update a table when changes are made to a related table using Python.
This is a stand-alone script that can be run outside of the ArcMap process.
# Define the workspace that will contain the two feature classes.
arcpy.env.workspace = r"C:\UpdateValue\UV2.gdb"
# Define the first feature class
fc1 = "fc1"
# Define the note and key fields
fc1_fields = ["NotesFC1","KeyFC1"]
# Define the value the code will look for in the first feature class.
fc1_value = "Closed"
# Define the second feature class
fc2 = "fc2"
# Define the note and key field
fc2_fields = ["NotesFC2","KeyFC2"]
# Define the value that the code will use to update the record in the second feature class.
fc2_value = "Closed"
cur = arcpy.da.SearchCursor(fc1, fc1_fields)
for row in cur:
cur2 = arcpy.da.UpdateCursor(fc2, fc2_fields)
for row2 in cur2:
if row == row2:
if row == fc1_value:
row2 = fc2_value
del row, cur, row2, cur2
Starting with the 'for' loop, this code reads:
For each record (row) in the first feature class, first create a new cursor to use to update the records in the second feature class (cur2). Second, search every record in the second feature class (for row2 in cur2). If the keys match (if row == row2 , which translates to if "KeyFC1" == "KeyFC2"), then check if the first table has been updated (if row == fc1_value, which translates to if "NotesFC1" == "Closed"). If the table has been updated, then update the table associated with the second table (row2 = fc2_value, which translates to "NotesFC2" = "Closed").
- Make Feature Layer (Data Management)
- Add Join (Data Management)
- Calculate Field (Data Management)
- Essentials of relating tables
- Relating the attributes in one table to another