Auto-Incrementing ObjectID Field Outside of ArcGIS

7655
12
Jump to solution
06-12-2015 02:09 PM
KyleHeulitt
New Contributor II

I have migrated a Filemaker Pro database to an enterprise geodatabase in SQL Server Express so that it can be viewed/edited in both Filemaker Pro and ArcGIS.  I'd like the ObjectID field in both feature classes and stand-alone tables to auto-increment when new records are added in Filemaker Pro.  I thought I could do this using the Identity data type in SQL Server but it does not appear that this data type is supported in ArcGIS (http://support.esri.com/cn/knowledgebase/techarticles/detail/40329).  I also don't appear to be able to alter this field in Filemaker to make it auto-increment.  Just wondering how people deal with this - is manually entering the value the only way to update the ObjectID field outside of ArcGIS?  This doesn't seem ideal.  Thanks in advance for any help!

0 Kudos
1 Solution

Accepted Solutions
ThomasColson
MVP Frequent Contributor

Messing with the ObjectID outside of the ESRI stack is pretty dangerous, and I don't know why ESRI continues to maintain that unnecessary restriction in this world of robust data applications. Anyhow, you'll need to set up a trigger or a stored proc to handle any CRUD operations to your ESRI table that are made outside of the normal way ESRI handles things. See Christian Wells's Blog  for a really good example of how to do this. I'm assuming this is a table that was created by ArcMap? If not, this is still possible, but you'll have to add some more SQL code to be able to handle CRUD by both ArcMap and FMP. I'm curious how you're interfacing FMP with SQL, are you able to sync edits on mobile devices with FMP to the SQL database?

View solution in original post

0 Kudos
12 Replies
JakeSkinner
Esri Esteemed Contributor

Hi Kyle,

Take a look at the Next_RowID property.

0 Kudos
KyleHeulitt
New Contributor II

Thanks for your response Jake.  I don't know SQL all that well so forgive me if I'm not understanding 100%.  It seems like this would work for making edits in SQL Server Management Studio, but is there a way to use this property to cause the ObjectID field to auto-increment outside of SQL Server (i.e. Filemaker Pro)? 

0 Kudos
JakeSkinner
Esri Esteemed Contributor

I'm not familiar with Filemaker Pro.  When you update the table in Filemaker Pro, how is it automatically updating the table in the enterprise database?

0 Kudos
KyleHeulitt
New Contributor II

I can view and edit the geodatabase table as an ODBC data source in Filemaker Pro.  When I set the Identity property to "Yes" in SQL Server, it auto-increments in Filemaker Pro too.  But this doesn't work when I try to use the table in ArcGIS unfortunately.

0 Kudos
ThomasColson
MVP Frequent Contributor

Messing with the ObjectID outside of the ESRI stack is pretty dangerous, and I don't know why ESRI continues to maintain that unnecessary restriction in this world of robust data applications. Anyhow, you'll need to set up a trigger or a stored proc to handle any CRUD operations to your ESRI table that are made outside of the normal way ESRI handles things. See Christian Wells's Blog  for a really good example of how to do this. I'm assuming this is a table that was created by ArcMap? If not, this is still possible, but you'll have to add some more SQL code to be able to handle CRUD by both ArcMap and FMP. I'm curious how you're interfacing FMP with SQL, are you able to sync edits on mobile devices with FMP to the SQL database?

0 Kudos
KyleHeulitt
New Contributor II

Thanks Thomas.  I will take a look at this tonight and report back.  Sounds like I might need to come up with another solution - I don't want to do anything dangerous with my database design since I'm a novice.  Maybe I will just store the location data in the geodatabase and have the non-location data reside outside of the geodatabase so SQL Server can manage the ID's.  Come to think of it this probably makes more sense than the way I was trying to do it.

Regarding your question about the mobile devices - yes, I have successfully synced edits to a SQL Server database using Filemaker Go.  I haven't tried it with a geodatabase yet though.   

0 Kudos
ThomasColson
MVP Frequent Contributor

I'm more than curious about the syncing with SQL server....what components/version of FMP do you have on the server/mobile client? This is something we're starting to get into....we've got the linking of ESRI and non-ESRI SQL tables pretty much solved. On that note: use the GLobal ID GUID in your spatial location table as a primary key to relate it, via SQL or some other app, to your non-spatial data table. DO NOT USE ESRI to manage the relationship, and you can avoid the burdensome OID restriction. The only drawback is that this requires a new location to first hit the ESRI location table to generate the UID, which can be dealt with by tinkering with the syncing from the FMP device to force two sync calls: One for new locations which will bring locations from FMP that have been entered in say, lat/long, which then create your location objects with the OID and a UID; then another call to add/update related location information, which cursors through new or edited non-GIS rows based on the UID PK/FK. Complex...sort of...which is why I'm interested in the details of how you're syncing FMP to SQL.

0 Kudos
KyleHeulitt
New Contributor II

I am using Filemaker Pro Advanced 13.0v3 on my laptop and Filemaker Go (free app) installed on my iPhone.  I'm currently in design/testing mode so I am using a database stored in a SQL Server instance on my laptop.  With this setup I am able to view/edit the SQL Server tables using Filemaker Go on my iPhone through the ODBC data sources stored in Filemaker Pro on my laptop.  In the final version the ODBC data sources will be set up in a database on Filemaker Pro Server which will allow concurrent mobile access to the SQL Server tables through a wireless or cellular network.

FileMaker Go 14 Overview and What's New | FileMaker

0 Kudos
KyleHeulitt
New Contributor II

Would this all be done in SQL?  Can you recommend any resources which would provide additional information about setting up such procedures?  Seems like I might need to take a course in SQL. 

0 Kudos