Creating a sequential Unique ID when a new feature is added

[AlicePence]

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!

[JoeBorgione]

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

[AlicePence]

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.

[JoeBorgione]

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. 

[JakeSkinner]

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?

[AlicePence]

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

[TobiasFimpel1]

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

/

[AndresCastillo]

Thank you, I will look into this.

What do you guys think about this tool:

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

[JoshuaBixby]

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.

[AlicePence]

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