topic Re: Creating a sequential Unique ID when a new feature is added in Data Management Questions

Creating a sequential Unique ID when a new feature is added

AlicePence — Wed, 09 Sep 2015 20:52:04 GMT

I have a sde feature class of different sites around the city that editors will use to create new ones created through an application. I am hoping that there is a way to when the editor goes into the app to create a new site, as the form pops up to begin editing- the site ID (unique ID for each site) will be automatically populated with the next number in sequence (last one created was site .00001 and the next one will be .00002 and so on). This will help with making sure that a unique ID isn't used twice and the editors don't have to remember the last one they used.

Thanks!

Re: Creating a sequential Unique ID when a new feature is added

JoeBorgione — Wed, 09 Sep 2015 20:57:34 GMT

What happens when two editors fire off the app at the same time?

Re: Creating a sequential Unique ID when a new feature is added

AlicePence — Wed, 09 Sep 2015 21:14:35 GMT

Nothing at the moment - but now that you brought that up I'll probably add something to the app if this is needed for the unique IDs to not be duplicated.

Re: Creating a sequential Unique ID when a new feature is added

JoeBorgione — Thu, 10 Sep 2015 02:12:31 GMT

If you do a search on the forums, you'll find a lot of discussions, but I've never seen a viable solution. It's not a trivial matter to be sure.

Re: Creating a sequential Unique ID when a new feature is added

JakeSkinner — Fri, 10 Dec 2021 22:33:27 GMT

Hi Alice,

One possible solution is using a database trigger. Below is an example using SQL Server:

CREATE TRIGGER [Vector].[trg_insert_id]
    ON [vector].[SITES]
    FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ID float
    SELECT @ID = MAX([SiteID])
        FROM [vector].[SITES]
        SELECT @ID
    UPDATE [vector].[SITES]
          SET SiteID = @ID + 0.00001
          WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
   
END

The trigger is created on a feature class called 'Sites'. After a feature is added, the trigger finds the largest SiteID, adds 0.00001 to this value and then updates the SiteID with this value.

The above example is a for a non-versioned feature class. This will be more difficult for a versioned feature class. Each update to a feature will be an insert into the A table, causing the Site ID to increase. For example, you add a site and the Site ID is updated. If you go back and update the site (i.e. change an attribute), the Site ID will update again.

Also, when editing a feature in the web map the Site ID was not updated within the initial pop-up:

I set a refresh interval on the layer. After making the edit and the refresh occurs, I was able to see the Site ID update:

One issue that I ran into is with the editing. When using an ArcGIS Online Web Map/App, if I edited additional attributes, the trigger would not fire. I have to add a point, close the attribute window, select out of editing (i.e click on 'Details') and then go back into the edit session to update the other attributes. I'm currently looking into why this is occurring. Are you going to use an ArcGIS Online Web Map/App, or develop a custom one for the editors?

Re: Creating a sequential Unique ID when a new feature is added

AlicePence — Thu, 10 Sep 2015 15:31:55 GMT

Right now I am using Latitude's Geocortex HTML5 web applications as the app, but have been tossing around the idea of also using AGOL for just editing- if the steps you showed above work to some degree then I'll take the route of using AGOL

Re: Creating a sequential Unique ID when a new feature is added

JoshuaBixby — Tue, 15 Sep 2015 14:41:51 GMT

Since you are working with an SDE feature class, why not go with the built-in unique identifier, ObjectID? You seem to be after a basic, incremental unique identifier, which isn't much different than the ObjectID that SDE is already managing for you.

Re: Creating a sequential Unique ID when a new feature is added

AlicePence — Tue, 15 Sep 2015 15:01:33 GMT

That was one of my ideas since it does it automatically, but they need the ID numbers to be .00001, .00002 and so on

Re: Creating a sequential Unique ID when a new feature is added

JoshuaBixby — Tue, 15 Sep 2015 16:57:20 GMT

But they need a working system more, right?

Re: Creating a sequential Unique ID when a new feature is added

AlicePence — Tue, 15 Sep 2015 17:22:31 GMT

Well right now, I have a SQL script in my application that runs a query on the last record found and displays it for them- it's a band-aid on this problem right now, but I tired convincing them about the ObjectID but they need it to be that string of numbers

Re: Creating a sequential Unique ID when a new feature is added

TobiasFimpel1 — Wed, 16 Sep 2015 03:20:35 GMT

Jake,

I observed the same issue, that "if I edited additional attributes, the trigger would not fire." After inspecting the HTTP traffic I noticed that the editing tool actually does cause the trigger to fire (at the insert of a new row), but then overwrites it with NULL when appyEdits is called again (and when editing attributes it is always called again.)

My solution was to enable geodatabase editor tracking on the feature class in order for my SQL trigger to be able to distinguish between edits to existing points and inserts of new points, like shown below (But it's Oracle syntax, not SQL Server.) -Tobias

CREATE OR REPLACE TRIGGER EGISADMIN.PED_COUNTERS_ID_BEF_INS

BEFORE INSERT OR UPDATE ON EGISADMIN.PED_COUNTERS

FOR EACH ROW

BEGIN

IF :NEW.CREATED_DATE = :NEW.LAST_EDITED_DATE THEN

SELECT PED_COUNTERS_S.NEXTVAL

INTO :NEW.REQUESTID

FROM DUAL;

END IF;

IF :NEW.CREATED_DATE <> :NEW.LAST_EDITED_DATE THEN

--this is an edit to an existing point. here we need to block the incoming null value for requestid

SELECT :OLD.REQUESTID

INTO :NEW.REQUESTID

FROM DUAL;

END IF;

END

Re: Creating a sequential Unique ID when a new feature is added

AndresCastillo — Fri, 06 Jul 2018 17:31:51 GMT

Thank you, I will look into this.

What do you guys think about this tool:

https://www.arcgis.com/home/item.html?id=6997e32c54bd40ca8ca13e1fdaf5948b

Re: Creating a sequential Unique ID when a new feature is added

MichaelVolz — Tue, 22 Jun 2021 08:18:26 GMT

Would the concept of NextSequenceValue of Attribute Rules (ArcPro) in this thread work for you?

https://community.esri.com/ideas/8531

Re: Creating a sequential Unique ID when a new feature is added

AndresCastillo — Fri, 06 Jul 2018 21:09:15 GMT

This looks cool, but I am using arcmap to publish services from our local servers.

Maybe this is another option?

https://community.esri.com/thread/182383-auto-generate-sequential-asset-ids

Re: Creating a sequential Unique ID when a new feature is added

MichaelVolz — Tue, 22 Jun 2021 08:18:28 GMT

Andres:

It looks like you might be able to use the Auto-Increment solution once ArcPro can publish services directly to ArcGIS Server without too many bugs once it is hopefully released by ESRI in first half of 2019:

https://community.esri.com/ideas/12258

You might want to vote up this idea if you have not already done so.