Duplicate ObjectID

1681
4
02-01-2019 09:14 AM
GaryBowles1
Occasional Contributor III

We recently ran a year end report for quarterly Inspection data collected using Survey123. Several of the inspections did not show up in the year end report. When we reviewed the quarterly reports, the data had appeared in those. A little digging led to finding that some of the objectid values have been duplicated in the hosted feature service. I am assuming this is why only one of the records is showing in our year end query and not all of them. I have attached a screen grab of a sql query to the datastore with the results.

Any idea how this can happen, and what is the fix?

Thanks,

--gary

0 Kudos
4 Replies
JamesTedrick
Esri Esteemed Contributor

Hi Gary,

This is interesting.  Can you double check the indexes on the objectID and globalID columns to ensure the UNIQUE keyword is present?  That should have prevented this from occurring.  Aside from that, has data been loaded into this table from outside of the ArcGIS platform (i.e., direct SQL uploads)?

0 Kudos
GaryBowles1
Occasional Contributor III

James,

Did not find UNIQUE keyword (see attached).

After talking to our field data collectors and reviewing the data, we think that the issue was caused by the users going into the Sent box and using the Edit and resend survey option (changing the inspection date and other inspected values in the original survey) as a means to create a new survey. We queried the datastore from SQL and found the original survey that was completed 3 months prior and the "new" survey and both had the same objectid and GlobalID. Only the "new" survey is transferred to our SDE database via daily python script. We assume that this something in PostgreSQL akin to versioning in SDE.

Is this the expected behavior of the Edit and resend survey option in the Sent box?

Thanks,

--gary

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi Gary,

No, using the Sent box to generate a new form for submission should not result in duplicate objections/globalids.  That being said, I would expect the indexes for objects and globalids to have a unique requirement - it's a vital part of the geodatabase configuration.  This is something more easily viewable in ArcGIS Pro/ArcCatalog (or the JSON view of the REST endpoint; this is hidden in the HTML view).

GaryBowles1
Occasional Contributor III

James,

Attached is a grab of the JSON view of the REST endpoint for the feature service. My reading of it (see highlight) shows that the GlobalID field index is set yo unique but i do not see an index for the objectid field.

I have tested this survey and it does indeed create duplicate objectid and globalid when editing an existing record via the Sent Box. Advice?

Thanks,

--gary

0 Kudos