Is it possible to append ObjectID using fieldmappings?

ThomasColson · ‎07-24-2017

Using Python 2.7.8, I'm trying to use the default field mappings for all fields except for 2 (PIC_ID and Object ID). The two tables are essentially identical, the tinkering with the globalid is something I have to do in order to preserve the globalid part of an attachment relationship when syncing an AGOL feature service to an on-premise SDE, and not something I'm looking to change. The below snippet works when I'm just altering one field out of the mapping:

target_layer = "C:/blah blah blah.sde/blah.DBO.blah"
 append_layer = renamefgdb+'\yada_yada_yada'
 fieldmappings = arcpy.FieldMappings()
 
 fieldmappings.addTable(target_layer)
 fieldmappings.addTable(append_layer)
 
 field_to_map_index = fieldmappings.findFieldMapIndex("globalid")
 
 field_to_map = fieldmappings.getFieldMap(field_to_map_index)
field_to_map.addInputField(append_layer, "PIC_ID")
 
 fieldmappings.replaceFieldMap(field_to_map_index, field_to_map)
 
 arcpy.Append_management(append_layer, target_layer, "NO_TEST", fieldmappings, "")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However I have an operational requirement to maintain the objectid during append: that is, the objectID's coming from the append source need to be the same when they go to the target layer.

But when I do something like this, or many many many variations thereof, it fails.

target_layer = "C:/blah blah blah.sde/blah.DBO.blah"
 append_layer = renamefgdb+'\yada_yada_yada'
 fieldmappings = arcpy.FieldMappings()
 
 fieldmappings.addTable(target_layer)
 fieldmappings.addTable(append_layer)
 
 field_to_map_index = fieldmappings.findFieldMapIndex("globalid")
 field_to_map_index = fieldmappings.findFieldMapIndex("objectid")
 
 field_to_map = fieldmappings.getFieldMap(field_to_map_index)
field_to_map.addInputField(append_layer, "PIC_ID")
 field_to_map.addInputField(append_layer, "OBJECTID")
 
 fieldmappings.replaceFieldMap(field_to_map_index, field_to_map)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So is it possible to add ObjectID to a field mapping in python, and if so, how?

RebeccaStrauch__GISP · ‎07-24-2017

I'm working on a really similar issues right now, so will be interested to hear other's thoughts too. I am using other GP tools, but the results are similar.

Just FYI - Once thing I discovered is that GlobalIDs will not copy/join, and of course ObjectIDs can change.

What I recommend is old-school...create a new uniqueID field and populate is with the ObjectID. The new and ObjectID field will most likely get out of sync fairly fast, but at least you have a static ID.

What I am testing is trying to get a trigger/notification is the global or ObjectID changes do to a polygon splitting etc. I just started testing so still working on it.

ThomasColson · ‎07-24-2017

Per this, https://community.esri.com/thread/185431-append-tool-and-field-mapping-help-examples?q=field%20mappi... , I tried this:

target_layer = "C:/blah blah blah.sde/blah.DBO.blah"
 append_layer = renamefgdb+'\yada_yada_yada'
 fieldmappings = arcpy.FieldMappings()
 
 fieldmappings.addTable(target_layer)
 fieldmappings.addTable(append_layer)
 list_of_fields_we_will_map = []
 list_of_fields_we_will_map.append(('PIC_ID', 'globalid'))
 list_of_fields_we_will_map.append(('objectid', 'objectid'))
 for field_map in list_of_fields_we_will_map:
 field_to_map_index = fieldmappings.findFieldMapIndex(field_map[0]) 
 field_to_map = fieldmappings.getFieldMap(field_to_map_index)
 field_to_map.addInputField(append_layer, field_map[1])
 fieldmappings.replaceFieldMap(field_to_map_index, field_to_map)
 
 #field_to_map_index = fieldmappings.findFieldMapIndex("globalid")
 #field_to_map = fieldmappings.getFieldMap(field_to_map_index)
 #field_to_map.addInputField(append_layer, "PIC_ID")
 
 
 arcpy.Append_management(append_layer, target_layer, "NO_TEST", fieldmappings, "")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

and it fails with

Traceback (most recent call last):
 File "C:/PRODUCTION/GRSM_VEG_AA_SRV_LOAD/ARCHIVE_AA_S123_TEST.py", line 67, in <module>
 field_to_map = fieldmappings.getFieldMap(field_to_map_index)
 File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\arcobjects\arcobjects.py", line 328, in getFieldMap
 return convertArcObjectToPythonObject(self._arc_object.GetFieldMap(*gp_fixargs(args)))
RuntimeError: FieldMappings: Error in getting field map from field mapping for GetFieldMap‍‍‍‍‍‍

I've already worked around the globalid's changing....my destination table has a field called "globalid", but it is not a "globalid" field, so the source globalid writes nicely into it, and doesn't change. "GIS_ID" is defined as the "globalid" in the destination schema.

Really need to get the source OID to write into the destination OID without changing.....

DavidBlanchard · ‎07-25-2017

As the OBJECTID field is used by ArcGIS as an internal unique primary key field, it is protected from manual editing and is instead automatically assigned by internal processes within ArcGIS (see What is an ObjectID?—Help | ArcGIS Desktop).

This field is meant to be unique within the table in question, and copying the OID across tables would make it possible for duplicates to exist. Therefore, this isn't possible.

Even when editing directly within a database engine (outside ArcGIS), one must use the correct procedures to generate unique OIDs (see Next_RowID—Help | ArcGIS Desktop ).

BlakeTerhune · ‎07-26-2017

Sorry to go off topic, but is there a way to get the current RowID without incrementing it by calling Next_RowID? Like calling CURRVAL instead of NEXTVAL?

DavidBlanchard · ‎07-26-2017

There's no simple stored procedure to call. But essentially, the last OID is stored in a lookup table that uses the tables registration id (e.g. data_owner.i10). The following code can be used in SQL Server with SDE 10.4 to retrieve the last ObjectID:

DECLARE     @owner nvarchar(128) = 'data_owner',
          @table nvarchar(128) = 'MyTable';

DECLARE @regid int;

SELECT @regid = registration_id
FROM sde.SDE_table_registry
WHERE owner = @owner AND table_name = @table;

DECLARE @sql nvarchar(1024);

SET @sql = 'SELECT base_id FROM ' + @owner + '.i' +cast (@regid AS VARCHAR(10)) + ' WHERE id_type = 2';
EXECUTE sp_executesql @sql;‍‍‍‍‍‍‍‍‍‍‍‍‍

JoshuaBixby · ‎07-26-2017

I guess it depends on what blake.terhune‌ means by "current RowID." Doesn't the base_id field in the SQL above represent the next value that will be returned by calling next_rowid? At least that is how it works when I test it on my system. Although last_id seems temping to use, the fact is that the last ID issued might not be the highest objectID in use since records can be deleted.

Blake, why do you want "current RowID"? Or, are you after the highest/largest ObjectID in use?

DavidBlanchard · ‎07-26-2017

Joshua Bixby‌ you are correct, it returns the next ObjectID. You would therefore need to subtract 1 in order to get the last returned ObjectID. This returns the largest OID that has ever been used in the table. However, it doesn't guarantee that the OID is still present or has ever been used.

If you want the largest currently in use, then simply sort your table by ObjectID.

SELECT OBJECTID
FROM owner.mytable
ORDER BY OBJECTID DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;‍‍‍‍

BlakeTerhune · ‎07-26-2017

Original Content Removed

Off topic discussion continued in new thread...

https://community.esri.com/message/703697-query-max-objectid-from-base-and-adds-tables

JoshuaBixby · ‎07-26-2017

Instead of sidetracking the original discussion anymore, it might be worth spawning a new question using your last reply.