I am an enthusiastic dabbler in Python and I have (enthusiastically) attempted to write a script to create a one-to-many join to populate rows in the source table (Table1) from data in the join table (Table2).
(I have attached a diagram which explains this process)
The tables will have two ID values - one will have duplicates (ID1) and the other will be unique (ID2).
Table1 has unique IDs which will be duplicated after processing (ID1).
Table2 has two ID fields - there are duplicate ID1's and unique values for ID2.
I need to populate rows from table2 to table1.
The first match will populate the existing row in table1.
All subsequent matches to ID1 will be populated as new rows.
My script currently processes the one-to-many join successfully...but only when there is a maximum of 1 duplicate.
When there is more than 1 new row to insert, my script inserts all rows as duplicates of the 'final matched' row.
If there is anybody who can spot the error in my code or point me in the right direction, I would be really grateful as I have been stuck at this roadblock for a while now....
Thank you for taking the time to read about my troubles
Megan
import arcpy
arcpy.env.overwriteOutput = True
aprx = arcpy.mp.ArcGISProject(r'C:\sample_aprx.aprx')
m = aprx.listMaps("Map")[0]
table1 = r'C:\sample_aprx.gdb\table1'
table1_fields_update
table1_fields = ["ID1","ID2","FIELD1","FIELD2","FIELD3","FIELD4","FIELD5"]
table1_dict = {r[0]: (r) for r in arcpy.da.SearchCursor(table1,table1_fields)}
table2 = r'C:\sample_aprx.gdb\table2'
table2_fields = ["ID1","ID2","FIELD3","FIELD4","FIELD5"]
table2_dict = {(r[:2]): (r[2:]) for r in arcpy.da.SearchCursor(table2,table2_fields)}
InsertRows_list = []
with arcpy.da.UpdateCursor(table1, table1_fields) as UpdateCursor:
for row in UpdateCursor:
rowStatus = ""
table2match_list = [(k, table2_dict[k]) for k in table2_dict if k[0] == row[0]]
for table2match in table2match_list:
if row[1] is not None:
rowStatus = "Insert"
row[1], row[4], row[5], row[6] = table2match[0][1], table2match[1][0], table2match[1][1], table2match[1][2]
if rowStatus == "Insert"
InsertRows_list.append(row)
else:
UpdateCursor.updateRow(row)
with arcpy.da.InsertCursor(table1, table1_fields) as InsertCursor:
for table2_row_data in InsertRows_list:
table1_row_data = table1_dict.get(row[0])
new_join_row = (table1_row_data[0],table2_row_data[1],table1_row_data[2],table1_row_data[3],table2_row_data[0],table2_row_data[1],table2_row_data[2])
InsertCursor.insertRow(new_join_row)