Latest Contributions by LindsayRaabe_FPCWA

I inserted the print statements at Lines 7 & 8 below and caught the data for a record that should NOT be updated. Row A is the target table, row B is the source table. If Row B contains values where Row A is None or a differing value, then it would update Row A. In this case, Row B contains None values in some fields (those should be ignored outright) and the values it does contain already match those in Row A (so can also be ignored).  with arcpy.da.UpdateCursor(fs2, updateFieldsList) as updateRows: for updateRow in updateRows: # store the Join value by combining 2 field values of the row being updated in a keyValue variable keyValue = updateRow[0]+ "," + str(updateRow[1]) # verify that the keyValue is in the Dictionary if keyValue in valueDict: print(f"A: {list(updateRow[2:])}") print(f"B: {list(valueDict[keyValue])}") if list(updateRow[2:]) != list(valueDict[keyValue]): A: ['Done previously on iForms', 'Done previously on iForms', 'Done previously on iForms', 'Done previously on iForms', 'Done previously on iForms', 'Done previously on iForms', 'john.doe on 2024-12-11', 'john.doe on 2025-01-24', None, None, None, None, None, None, None] B: [None, None, None, None, None, None, 'john.doe on 2024-12-11', 'john.doe on 2025-01-24', None, None, None, None, None, None, None] Here's my code in full - a lot of it is data prep with the stuff we've been discussing at the end (from Line 197).  import arcpy from datetime import datetime from arcgis.gis import GIS import certifi import urllib3 import ssl import warnings from urllib3.exceptions import InsecureRequestWarning # Create a default SSL context with certificate verification ssl_context = ssl.create_default_context(cafile=certifi.where()) http = urllib3.PoolManager(ssl_context=ssl_context) # Make a request to verify the setup response = http.request('GET', 'https://maps.arcgis.com') print("http response: " + str(response.status)) # Suppress only the single InsecureRequestWarning from urllib3 if necessary warnings.simplefilter('ignore', InsecureRequestWarning) # Create GIS object print("Connecting to AGOL") client_id = '##########' client_secret = '##################' gis = GIS("https://maps.arcgis.com", client_id=client_id, client_secret=client_secret) print("Logged in as: " + gis.properties.user.username) arcpy.env.overwriteOutput = True arcpy.env.preserveGlobalIds=True fs1 = "https://services-ap1.arcgis.com/###########/arcgis/rest/services/service_df503b7953e7471a975eb99994681d72/FeatureServer/0" fs2 = "https://services-ap1.arcgis.com/###############/arcgis/rest/services/Post_Planting_Inspection_Tracking___TEST/FeatureServer/0" # Update the "username" field with the "Creator" field value if "username" is NULL with arcpy.da.UpdateCursor(fs1, ["username", "Creator"]) as cursor: for row in cursor: if row[0] is None or row[0] == "": row[0] = row[1] cursor.updateRow(row) print("Username field updated successfully.") with arcpy.EnvManager(preserveGlobalIds=True): export_table = arcpy.conversion.ExportTable( in_table=fs1, out_table=r"C:\Temp\scratch.gdb\FPC416_survey_ExportTable", where_clause="inspection_number <> 'Ad-hoc Inspection'", use_field_alias_as_name="NOT_USE_ALIAS", field_mapping=r'globalid "GlobalID" false false true 38 GlobalID 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,globalid,-1,-1;username "USERNAME" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,username,0,254;date_of_inspection "DATE OF INSPECTION" true true false 255 Date 0 1,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,date_of_inspection,-1,-1;plantation "PLANTATION" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,plantation,0,254;tree_species "TREE SPECIES" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,tree_species,0,254;tenure "TENURE" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,tenure,0,254;years_planted "YEAR/S PLANTED" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,years_planted,0,254;inspection_number "INSPECTION NUMBER" true true false 255 Text 0 0,First,#,FPC416 - Plantation & Property Inspection Form\FPC416_survey,inspection_number,0,254', sort_field=None ) # Delete fields arcpy.management.DeleteField( in_table=export_table, drop_field="tree_species;tenure;inspection_type;fbs_type;fbs_respons;fbs_respons_other;fbs_adeq;fbs_trafcbl;fbs_issues;fbs_issues_other;fbs_action_tkn;fbs_action_tkn_other;fbs_fup_reqd;wtrpts_accessbl;wtrpts_sign_adeq;wtrpts_dry_summer;wtrpts_issues;wtrpts_issues_other;wtrpts_action_tkn;wtrpts_action_tkn_other;wtrpts_fup_reqd;pwrlne_clr_accept;pwrlne_clr_respons;pwrlne_clr_respons_other;pwrlne_clr_issues;pwrlne_clr_issues_other;pwrlne_clr_action_tkn;pwrlne_clr_action_tkn_other;pwrlne_clr_fup_reqd;nut_def_evident;nut_def_impact;nut_def_issues;nut_def_issues_other;nut_def_percent_aff;nut_def_action_tkn;nut_def_action_tkn_other;nut_def_fup_reqd;drought_evident;drought_impact;drought_issues;drought_issues_other;drought_percent_aff;drought_action_tkn;drought_action_tkn_other;drought_fup_reqd;frost_evident;frost_impact;frost_issues;frost_issues_other;frost_percent_aff;frost_action_tkn;frost_action_tkn_other;frost_fup_reqd;insects_evident;insects_type;insects_type_other;insects_plant_part;insects_plant_part_other;insects_impact;insects_percent_aff;insects_action_tkn;insects_action_tkn_other;insects_fup_reqd;vert_pests_evident;vert_pests_type;vert_pests_type_other;vert_pests_plant_part;vert_pests_plant_part_other;vert_pests_impact;vert_pests_percent_aff;vert_pests_action_tkn;vert_pests_action_tkn_other;vert_pests_fup_reqd;diseases_evident;diseases_type;diseases_type_other;diseases_plant_part;diseases_plant_part_other;diseases_impact;diseases_percent_aff;diseases_myrtle_rust_new_event;diseases_action_tkn;diseases_action_tkn_other;diseases_fup_reqd;weeds_impct_gwth;dec_weeds_prsnt;weeds_type;weeds_type_other;weeds_impact;weeds_issues;weeds_issues_other;weeds_percent_aff;weeds_action_tkn;weeds_action_tkn_other;weeds_fup_reqd;remnt_veg_evident;remnt_veg_type;remnt_veg_type_other;wind_dmg_evident;wind_dmg_impact;wind_dmg_issues;wind_dmg_issues_other;wind_dmg_percent_aff;wind_dmg_action_tkn;wind_dmg_action_tkn_other;wind_dmg_fup_reqd;sun_scorch_evident;sun_scorch_impact;sun_scorch_issues;sun_scorch_issues_other;sun_scorch_percent_aff;sun_scorch_action_tkn;sun_scorch_action_tkn_other;sun_scorch_fup_reqd;inundtn_evident;inundtn_impact;inundtn_issues;inundtn_issues_other;inundtn_percent_aff;inundtn_action_tkn;inundtn_action_tkn_other;inundtn_fup_reqd;stem_dfrms_evident;stem_dfrms_impact;stem_dfrms_issues;stem_dfrms_issues_other;stem_dfrms_percent_aff;stem_dfrms_action_tkn;stem_dfrms_action_tkn_other;stem_dfrms_fup_reqd;erosion_evident;erosion_impact;erosion_issues;erosion_issues_other;erosion_percent_aff;erosion_action_tkn;erosion_action_tkn_other;erosion_fup_reqd;stkhldr_obs;detail_stkhldr_obs;tree_types;tree_types_other;height_m;height;basal_area_m2;basal_area;stems_ha;stems;cone_dvpt;host_gwth_assess;sndlwd_stkng;sndlwd_stkng_other;sndlwd_gwth_assess;flwrng_seed_assess;flwrng_seed_assess_other;sndlwd_actions_tkn;sndlwd_actions_tkn_other;sndlwd_fup_action_reqd;rdsigns_vsble;rdsigns_issues;rdsigns_issues_other;rdsigns_actions_tkn;rdsigns_actions_tkn_other;rdsigns_fup_action_reqd;prop_signs_vsbl;prop_signs_issues;prop_signs_issues_other;propsigns_actions_tkn;propsigns_actions_tkn_other;propsigns_fup_action_reqd;fences_adeq;fences_issues;fences_issues_other;fences_actions_tkn;fences_actions_tkn_other;fences_fup_action_reqd;padlocks_reqd;padlock_issues;padlocks_actions_tkn;padlocks_actions_tkn_other;padlocks_fup_action_reqd;fire_canisters_intact;fire_canister_maps_unspoiled_current;fire_canisters_fup_action_reqd;bldgs_adeq;bldgs_fireprf;bldgs_issues;bldgs_issues_other;bldgs_actions_tkn;bldgs_actions_tkn_other;bldgs_fup_action_reqd;utils_accesbl;utils_fireprf;utils_issues;utils_issues_other;utils_actions_tkn;utils_actions_tkn_other;utils_fup_action_reqd;why_inspctn_missed;why_inspctn_missed_other;compltd_survey_date;CreationDate;Creator;EditDate;Editor;comments", method="DELETE_FIELDS" ) # Define the Inspection list lookup_list = ['2 weeks', '4 weeks', '6 weeks', '8 weeks', '10 weeks', '12 weeks', '4 months', '5 months', '6 months', '9 months', '12 months', '15 months', '18 months', '21 months', '24 months'] # Define the lookup dictionary lookup_dict = { '2 weeks': 'insp_2_weeks', '4 weeks': 'insp_4_weeks', '6 weeks': 'insp_6_weeks', '8 weeks': 'insp_8_weeks', '10 weeks': 'insp_10_weeks', '12 weeks': 'insp_12_weeks', '4 months': 'insp_4_months', '5 months': 'insp_5_months', '6 months': 'insp_6_months', '9 months': 'insp_9_months', '12 months': 'insp_12_months', '15 months': 'insp_15_months', '18 months': 'insp_18_months', '21 months': 'insp_21_months', '24 months': 'insp_24_months' } print("Lookup table created") # Add text fields to the table for each value in the lookup dictionary for field_name in lookup_dict.values(): print(f"Creating {field_name} field") arcpy.AddField_management(export_table, field_name, "TEXT") # Function to parse date with different formats def parse_date(date_str): for fmt in ("%d/%m/%Y %I:%M:%S %p", "%d/%m/%Y", "%d/%m/%Y %I:%M:%S.%f %p", "%d/%m/%Y %I:%M:%S %p"): try: return datetime.strptime(date_str, fmt).strftime("%Y-%m-%d") except ValueError: pass raise ValueError(f"Date format for {date_str} is not recognized") # Update the fields based on the inspection_number using CalculateField for v in lookup_list: update_field = lookup_dict[v] update_view = arcpy.management.MakeTableView( in_table=export_table, out_view="TableView", where_clause=f"inspection_number = '{v}'", workspace=None, field_info=f"globalid globalid VISIBLE NONE;username username VISIBLE NONE;date_of_inspection date_of_inspection VISIBLE NONE;plantation plantation VISIBLE NONE;years_planted years_planted VISIBLE NONE;inspection_number inspection_number VISIBLE NONE;{update_field} {update_field} VISIBLE NONE" ) expression = f"!username! + ' on ' + (datetime.strptime(!date_of_inspection!, '%d/%m/%Y %I:%M:%S %p') if '/' in !date_of_inspection! else datetime.strptime(!date_of_inspection!, '%d/%m/%Y')).strftime('%Y-%m-%d')" arcpy.management.CalculateField(update_view, update_field, expression, "PYTHON3") print("Fields added and updated successfully.") flat_table = arcpy.analysis.Statistics( in_table=export_table, out_table=r"C:\Temp\scratch.gdb\FlattenedInspectionTable", statistics_fields="insp_2_weeks FIRST;insp_4_weeks FIRST;insp_6_weeks FIRST;insp_8_weeks FIRST;insp_10_weeks FIRST;insp_12_weeks FIRST;insp_4_months FIRST;insp_5_months FIRST;insp_6_months FIRST;insp_9_months FIRST;insp_12_months FIRST;insp_15_months FIRST;insp_18_months FIRST;insp_21_months FIRST;insp_24_months FIRST", case_field="plantation;years_planted", concatenation_separator="" ) print("Table flattened") # List all fields in the table flat_fields = arcpy.ListFields(flat_table) # Iterate through the fields and rename if they start with "FIRST_" for field in flat_fields: if field.name.startswith("FIRST_"): new_field_name = field.name.replace("FIRST_", "", 1) arcpy.AlterField_management(flat_table, field.name, new_field_name, new_field_name) print("Fields renamed successfully in flattend table.") arcpy.management.CalculateField( in_table=flat_table, field="join_field", expression='!plantation!+"_"+str(!years_planted!)', expression_type="PYTHON3", code_block="", field_type="TEXT", enforce_domains="NO_ENFORCE_DOMAINS" ) print("Join field created in flattened table") with arcpy.EnvManager(preserveGlobalIds=True): Table2 = arcpy.conversion.ExportTable( in_table=fs2, out_table=r"C:\temp\scratch.gdb\PostPlantingInspectionTracking_TargetTable", where_clause="", use_field_alias_as_name="NOT_USE_ALIAS", field_mapping='Plantation "Plantation" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,Plantation,0,49;PlantingYear "Planting Year" true true false 0 Long 0 0,First,#,Post Planting Inspection Tracking - TEST,PlantingYear,-1,-1;insp_2_weeks "2 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_2_weeks,0,49;insp_4_weeks "4 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_4_weeks,0,49;insp_6_weeks "6 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_6_weeks,0,49;insp_8_weeks "8 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_8_weeks,0,49;insp_10_weeks "10 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_10_weeks,0,49;insp_12_weeks "12 week inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_12_weeks,0,49;insp_4_months "4 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_4_months,0,49;insp_5_months "5 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_5_months,0,49;insp_6_months "6 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_6_months,0,49;insp_9_months "9 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_9_months,0,49;insp_12_months "12 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_12_months,0,49;insp_15_months "15 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_15_months,0,49;insp_18_months "18 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_18_months,0,49;insp_21_months "21 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_21_months,0,49;insp_24_months "24 month inspection" true true false 50 Text 0 0,First,#,Post Planting Inspection Tracking - TEST,insp_24_months,0,49', sort_field=None ) print("Inspection tracking table exported") arcpy.management.CalculateField( in_table=Table2, field="join_field", expression='!Plantation!+"_"+str(!PlantingYear!)', expression_type="PYTHON3", code_block="", field_type="TEXT", enforce_domains="NO_ENFORCE_DOMAINS" ) print("Join field created in tracking table") sourceFieldsList = ['join_field', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] # Use list comprehension to build a dictionary from a da SearchCursor valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(flat_table, sourceFieldsList)} updateFieldsList = ['join_field', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] changeCnt = 0 with arcpy.da.UpdateCursor(Table2, updateFieldsList) as updateRows: for updateRow in updateRows: # store the Join value of the row being updated in a keyValue variable keyValue = updateRow[0] # verify that the keyValue is in the Dictionary if keyValue in valueDict: if list(valueDict[keyValue]) and updateRow[1:15]: # A newer value exists changeCnt += 1 for n in range (1,len(sourceFieldsList)): updateRow[n] = valueDict[keyValue][n-1] updateRows.updateRow(updateRow) # transfer the values stored under the keyValue from the dictionary to the updated fields. del valueDict print("Temp data prepped") # Update feature service sourceFieldsList = ['Plantation', 'PlantingYear', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] # Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 2 separate feilds valueDict = {str(r[0]) + "," + str(r[1]):(r[2:]) for r in arcpy.da.SearchCursor(Table2, sourceFieldsList)} updateFieldsList = ['Plantation', 'PlantingYear', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] with arcpy.da.UpdateCursor(fs2, updateFieldsList) as updateRows: for updateRow in updateRows: # store the Join value by combining 2 field values of the row being updated in a keyValue variable keyValue = updateRow[0]+ "," + str(updateRow[1]) # verify that the keyValue is in the Dictionary if keyValue in valueDict: if list(updateRow[2:]) != list(valueDict[keyValue]): # transfer the values stored under the keyValue from the dictionary to the updated fields. for n in range (2,len(sourceFieldsList)): if valueDict[keyValue][n-2] == None: pass else: updateRow[n] = valueDict[keyValue][n-2] updateRows.updateRow(updateRow) del valueDict print("Fields updated successfully.") Interestingly, when I tried to run this from our server, it failed to run Line 35 because Pro wasn't signed in (doesn't want to use the login process at Line 26) but alas, that's an issue for a different post! ... View more

No luck with that one. Layer output from Make Feature Layer is still missing the joined fields (e.g. table2.field) and the source table fields show as the original - not as table1.field name.  ... View more

Half way there! It left rows alone that were not present in the update table at all, but rows which were present but had no updates still had the edit date updated.  ... View more

Actually, I made a small change (added in the "pass" instead of converting nulls to 0) and it works perfectly (or perfect enough - not overly fussed about the editor tracking on this feature service being overwritten for every feature regardless of no changes happening for some of them).  # transfer the values stored under the keyValue from the dictionary to the updated fields. for n in range (2,len(sourceFieldsList)): if valueDict[keyValue][n-2] == None: pass else: updateRow[n] = valueDict[keyValue][n-2] updateRows.updateRow(updateRow)  Where a value exists and doesn't match, or the source field is null but a join value has been calculated, it gets updated. If a value already exists and the join table is null, it is skipped. If both tables contain nulls, it is also skipped. (these are the ones where it would be nice for editor tracking to be left alone, but alas - I'm just happy it works!) ... View more

Thank you for the update. I was looking at it trying to figure out the missing bit - didn't figure it was meant to be on the new line. That seems to have worked though! All features have been updated with either 0 or the value from the update table.  One step closer. Now to figure out how to ignore the nulls altogether (leave them null) and only update values where new doesn't exist in old or doesn't match.  ... View more

Thanks. Will try it out.  ... View more

Correct - I don't want a permanent join. And I thought Add Join was temporary (it appears in the list of joins which can then be removed).  ... View more

@RichardFairhurst syntax error in the updated code.  I was using the multi field join because I discovered that using the single field join was changing the GlobalID values for some reason.  ... View more

@RichardFairhurst Thank you - will test it out now. Definitely using a test dataset! ... View more

The key point in @PeterKnoop's reply is that you also had editor tracking enabled. Just having the fields present isn't enough.  ... View more

Hi @RichardFairhurst . I've been trying to implement some of your coding as it looks like just the ticket! I've hit a snag though and have no idea why.  Below is my version of your coding that uses multiple join fields to then updated multiple target fields. The problem is, it only seems to be updating the first field (insp_2_weeks), and not any of the subsequent fields. Any idea why? The only real changes I made were decreasing it from 3 join fields to 2, updating the field names and input tables.    # Update feature service sourceFieldsList = ['Plantation', 'PlantingYear', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] # Use list comprehension to build a dictionary from a da SearchCursor where the key values are based on 2 separate feilds valueDict = {str(r[0]) + "," + str(r[1]):(r[2:]) for r in arcpy.da.SearchCursor(Table2, sourceFieldsList)} updateFieldsList = ['Plantation', 'PlantingYear', 'insp_2_weeks', 'insp_4_weeks', 'insp_6_weeks', 'insp_8_weeks', 'insp_10_weeks', 'insp_12_weeks', 'insp_4_months', 'insp_5_months', 'insp_6_months', 'insp_9_months', 'insp_12_months', 'insp_15_months', 'insp_18_months', 'insp_21_months', 'insp_24_months'] with arcpy.da.UpdateCursor(fs2, updateFieldsList) as updateRows: for updateRow in updateRows: # store the Join value by combining 2 field values of the row being updated in a keyValue variable keyValue = updateRow[0]+ "," + str(updateRow[1]) # verify that the keyValue is in the Dictionary if keyValue in valueDict: # transfer the value stored under the keyValue from the dictionary to the updated field. updateRow[2] = valueDict[keyValue][0] print(updateRow[2]) updateRows.updateRow(updateRow) del valueDict Additionally, is it possible to make it only update fields where the target field is null instead of just a mismatch in values? I don't mind if the target field has a different value from the source (as it may have been done by the field worker manually), but if it's null, then I want a default value entered to show an inspection occurred.    ... View more

I feel like this was close, but it was more advanced than I'm used to wielding and couldn't make progress. I'm taking a different approach that I can follow easier, though it may not be as efficient as other methods! Will start a new post to simplify the question to it's core.  ... View more

Thanks - I suspect it ended up being both the <> and the incorrect "" '' usage as discussed below.  ... View more