Update feature class records based on matching ID

[PeteCoventry4]

I have a Feature Class dataset that contains over 7,000 records with approximately 20 Data Fields.  I need to update this Feature Class dataset with a spreadsheet containing updated information on approximately 1,700 of these records.  The spreadsheet only contains 6 of the Data Fields found in the Feature Class dataset which includes a Global ID number.  

I'm trying to update the records that share the same Global ID number and only for the 6 Data Fields found on my spreadsheet. 

I've tried to use the Append geoprocessing tool, but am getting an error stating that my schema doesn't match my target value.  I manually added the remaining Data Fields and made sure that the names all matched and applied the Append tool again, but still getting the same error. 

Any help would be greatly appreciated!
Pete

[StephanieOliver]

Hi Pete!

Not sure without the data in front of me but it might be that sounds like is is a mismatched field type that is not allowing you to append. So for example if the field in the feature class is text but in your spreadsheet that field is set to numeric, that might be causing the error.

From the Append tool help:
If Input schema must match target schema is chosen for the Schema Type parameter, the schema (field definitions) of the input datasets must match that of the target dataset for the features to be appended. If Use the Field Map to reconcile schema differences is chosen for Schema Type, the input dataset schema (field definitions) do not need to match the target dataset. However, any fields from the input datasets that do not match the fields of the target dataset will not be mapped to the target dataset unless the mapping is explicitly set in the Field Map parameter.

You probably want to look at field mapping to make sure your field schema matches: https://pro.arcgis.com/en/pro-app/latest/help/analysis/geoprocessing/basics/field-map.htm

[PeteCoventry4]

Hi Stephanie!

Thanks so much for responding! Is it possible to share the dataset with
you?

A couple side notes:

- The feature class is hosted on AGOL where 5 people are accessing the
data and making edits
- There are a few redundant data fields that are included in this
dataset. It was a mixup when I took over the project where we needed to
have EditDate and Editor added and when the feature class was published
from Pro, certain parameters were not checked.
- I'm using a field called FID as my unique datafield
- I don't want to mess up the original feature class, so I've pulled the
dataset through Portal on my ArcGIS Pro and made a copy (Feature to
Feature). When I did this, my FID field disappears and/or becomes a new
OBJECTID field as consecutive 1-7,000+
- If I download the Feature Class directly from AGOL and add it to Pro,
the FID data field is there, but it doesn't maintain the unique FID
number. For example, my FID data field starts with the number "1", but the
copy begins with "0". Also, the FID numbers are not (and shouldn't be)
consecutive. Sometimes a record is created, then later deleted, so the FID
numbers are going to have jumps in numbers (ie. 1, 2, 4, 7, 8, etc.

So bottom line, I have a spreadsheet containing some 1,700 records that
have updates that I want to somehow append/overwrite to the original
Feature Class based on the unique FID number.

Any help would be greatly appreciated! 🙂

Pete

[StephanieOliver · Accepted solution]

In case anyone else runs into this issue, I wanted to post the solution to solve it (and thanks to Pete for making a concise list of steps). We created a solution by using a join instead of append.

The reason the append did not work is because the FID field in the CSV and the OBJECTID field in the Feature Class did not match as far as field type. The Append (Data Management) tool did work once we corrected the field types and saved as CSV. However with an Append is adds records to the end of the Feature Class (https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/append.htm) not updating the fields. Hence why we used a join for the solution.

I recommend added a UNIQUEID field to the feature class is because the OBJECTID field can be reordered by the software and so it is not reliable as an ID field. I have had issues when I name a field FID in the past, but I never have an issue when I create a new field called UNIQUEID. This will ensure you always have the UNIQUEID field.

With the spreadsheet you want to format all the headers to make sure they do not have an underscores or spaces – that can prevent you from performing the join. You then must save the spreadsheet as a CSV file and then click and drag that CSV file from Windows File Explorer to the ArcGIS Pro project.

1. Pulled Feature Class into Pro via Portal and added a field called UNIQUEID - LONG, Numeric format
2. Used the Field Calculator to pull the FID numbers into the new field
3. Removed the underscores on the spreadsheet and Saved As as CSV
4. Added the CSV to Pro via Windows Explorer
5. Add joined the UNIQUEID from the FC to the FID in the CSV which returned the 1,700+ records successfully
6. Use a selection query on the ‘CSV unique value field’ is not NULL or unchecking Keep All Records when doing the join
7. Using Field Calculator and Python or Arcade on those fields, update those records

You can use Python and UpdateCursor function to create a script to do this process in a more automated way.

[MicZatorsky_AEC]

Hi Pete,

I have successfully updated an AGOL hosted feature layer (HFL) directly from an XLSX from ArcGIS Pro 2.5.3 using a manual point-n-click approach.  You can modify this to work with a FC instead of a spreadsheet.

Updates come in three types:

• Inserts - where you would use Append
• Updates - where you use Calculate Field
• Deletes - where you use Delete Rows

My general workflow for updates was:

• make a backup copy of the HFL
  • if FIDs are really your primary key, and you know they are unreliable, add a field to your HFL to hold a static version of the key  (e.g. FID_original) and populate it with the current FID value.  That way when you make a copy you'll know what the FID was.  
  • better would be to get used to using GlobalIDs.  Some tools will honor preserving these between operations

But for now your FIDs should work.

• prepare the XLS or FC and check that source and target keys will match, get record count for checking later
• use the Add Join tool, matching your HFL FID to your XLSX/FC FID.  Keeping all target features is optional, useful if you want to see what didn't match
• with the joined result, check that matched and unmatched rows are are exactly what you expect
• use Calculate Field to update the HFL values with XLSX / FC values
  • In pro 2.5 I had to use an Arcade expression to get it to work with Excel
• Remove join
• Check the update result through the AGOL interface - you can filter for records updated by you today and check the count matches what you expect

If this was a regular update for 20 fields, ModelBuilder or Python with a stack of Calcuate Field calls should work.

If you're cautions, publish a copy of your Hosted Feature Layer to an AGOL dev group and test on that first.  

Mic