topic Re: Add an incremental, unique ID in Geodatabase Questions

Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 17:21:03 GMT

This is a topic that's been around for as long as I can remember. The attribute assistant provided a couple methods to do this: autonumber, generate id, and there us a tool called Add Incrementing ID field but it is limited to database tables, not GEOdatabase tables; if an object id is present, the tool won't work.

Is there a method or tool available in ArcGIS Pro 2.4.3 that will autoincrement a field in an enterprise geodatabase table and/or feature class?

Re: Add an incremental, unique ID

Robert_LeClair — Thu, 02 Jan 2020 17:24:27 GMT

Joe - not sure the details of your workflow but there is a "Add Incrementing ID Field" GP tool that "may" work for you. Have you seen/used this before?

Re: Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 17:32:11 GMT

Robert- for some reason the second half of my post didn't make the trip. I looked at that tool, but it has limited application to a database table, not a GEOdatabase table. If an object id already exists, the tool won't work...

Re: Add an incremental, unique ID

Robert_LeClair — Thu, 02 Jan 2020 17:46:43 GMT

Yes, limited for sure. I did look into the ObjectID issue you mentioned and the Help says "If you do not specify a name for the field, ObjectID is used by default. If a field named ObjectID already exists, the tool will not run until you provide a different name." So you could create a new field for the unique values and then the GP tool would run.

Re: Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 17:55:27 GMT

These are two lines in the help that pretty much torpedo my use of the tool:

This tool can only be used with database tables, not geodatabase tables.

If a database-maintained, incrementing ID field already exists in the table, this tool will not add another one.

My use case is when a record is added, I need to generate an incremental unique id; I could use a trigger on the back-end sql database, but I'd like to avoid that if at all possible.

Re: Add an incremental, unique ID

Robert_LeClair — Thu, 02 Jan 2020 18:01:24 GMT

Yes, those 2 lines would definitely throw a monkey wrench in not being able to use the tool. Just guessing here but I'm thinking a trigger on the back end might be the best approach. I'd be curious to see if others reading this post have other ideas.

Re: Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 18:15:39 GMT

There are some python calculator approaches you can find with dr google; here's one from esri but it just calcs a field for the entire feature class or table.

Re: Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 18:23:03 GMT

Taking a look at Attribute Rules. The help states:

To save time during editing, specific attribute values are automatically populated for newly created features. For example, when a new pole is created, the assetID attribute is generated based on a sequence.

Re: Add an incremental, unique ID

JoeBorgione — Thu, 02 Jan 2020 20:27:11 GMT

Just took a stab at Attribute Rules and it provides the solution I'm after.

Set up sequence in gdb
Add global ids to target table/feature class( attribute rules requirement)
Add the attribute rule (see illustration below)

Attribute rules use Arcade so this rule will add the text SomePrefix along with the next number in the sequence. I started the sequence at 100 an incremented by 1. Once you have the run the add attribute rule tool, you are ready to make your edits....

Re: Add an incremental, unique ID

Anonymous User — Tue, 28 Apr 2020 19:03:58 GMT

This is exactly what I've been attempting to do. However, I'm using desktop. Thoughts?

Re: Add an incremental, unique ID

JoeBorgione — Tue, 28 Apr 2020 19:08:30 GMT

Desktop as in ArcMap/ArcCatalog? Attribute assistant.

Attribute rules is a Pro thing only; might be time to migrate?

Re: Add an incremental, unique ID

Anonymous User — Tue, 28 Apr 2020 21:11:38 GMT

You're right. Just adding to list of reasons to upgrade...

For the purposes of this thread (and those who are still using Desktop), Attribute Assistant is definitely the way to go. I've spent a few hours configuring it and now I'm up and running.

Thanks, and sorry for hijacking the thread!

Re: Add an incremental, unique ID

JoeBorgione — Tue, 28 Apr 2020 21:37:30 GMT

I've used attribute assistant a bunch, but since January of this year, I'm totally ArcGIS Pro, and while it has it's ups and downs, I don't really miss ArcMap... (I don't think you can hijack a thread, really; at least you got a solution out of the deal!)

Re: Add an incremental, unique ID

TL2 — Fri, 01 May 2020 15:18:39 GMT

This is great, but it doesn't seem to work correctly when creating records in a Web App or Survey123. It increments by 2. Posting from the server seems to make two inserts causing the double incremental value.

Anyone figure this out?

Re: Add an incremental, unique ID

CraigSwadner — Fri, 01 May 2020 15:35:58 GMT

This doesn’t effect survey123, however we have multiple software apps that touch our sde database and we created a db trigger to maintain our ids.

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

Re: Add an incremental, unique ID

TL2 — Fri, 01 May 2020 16:58:29 GMT

Have you tested with S123? I have not had luck with a trigger producing incremental values with S123. I currently have to use a Trigger & Job for my incremental values.

SQL Trigger on table causes CODE 500 Error.

SQL Trigger on featurclass in SQL SERVER

Re: Add an incremental, unique ID

CraigSwadner — Fri, 01 May 2020 17:20:58 GMT

I haven’t however any gis application that uses versioning should work fine, ArcMap, Pro and our webapps work just fine. I don’t think survey123 works with any kind of sql db to my knowledge, probably why we don’t use it much at all.

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Re: Add an incremental, unique ID

RayburnClipper — Fri, 18 Dec 2020 16:15:00 GMT

My GDB is in Oracle, I use a trigger to insert a unique id based on the sequence. I discovered this post because there are some odd things happening to my unique ids now. I am getting duplicates , nulls. So I started looking to see what others are doing and I am very surprised that ESRI doesn't have something that will create an auto-incrementing id field. Also curious about what others are doing to create their auto-incrementing id's. Having a unique id seems to be a pretty important thing.

Re: Add an incremental, unique ID

GretaParis — Tue, 05 Jan 2021 15:20:20 GMT

I successfully created incrementing attribute tables for the layers within my map thanks to this thread, so thank you! I am currently trying to publish my map as a feature layer to my company's Portal and I keep getting errors saying that the service could not be created. I was wondering if creating the incremental attribute tables might be interfering with my ability to publish - has anyone else experienced this problem?

Re: Add an incremental, unique ID

GretaParis — Tue, 05 Jan 2021 15:23:45 GMT

The errors I am receiving: 001487 & 001369