AnsweredAssumed Answered

Creating a sequential Unique ID when a new feature is added

Question asked by apence23 on Sep 9, 2015
Latest reply on Jul 9, 2018 by mvolz47

Like • Show 2 Likes2
Comment • 14

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!

5 people also have this question

Outcomes

14 Replies

jborgion Sep 9, 2015 1:57 PM
Mark Correct
Correct Answer
What happens when two editors fire off the app at the same time?
Like • Show 0 Likes0
Actions
- apence23 @ jborgion on Sep 9, 2015 2:14 PM
  Mark Correct
  Correct Answer
  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.
  Like • Show 0 Likes0
  Actions
  - jborgion @ apence23 on Sep 9, 2015 7:13 PM
    Mark Correct
    Correct Answer
    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.
    1 person found this helpful
    Like • Show 0 Likes0
    Actions
jskinner-esristaff Sep 10, 2015 6:49 AM
Mark Correct
Correct Answer
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?
6 people found this helpful
Like • Show 1 Like1
Actions
- apence23 @ jskinner-esristaff on Sep 10, 2015 8:31 AM
  Mark Correct
  Correct Answer
  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
  Like • Show 0 Likes0
  Actions
- fimpe001 @ jskinner-esristaff on Sep 15, 2015 8:35 PM
  Mark Correct
  Correct Answer
  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
  /
  4 people found this helpful
  Like • Show 1 Like1
  Actions
  - andrescastillo08 @ fimpe001 on Jul 6, 2018 10:36 AM
    Mark Correct
    Correct Answer
    Thank you, I will look into this.
    What do you guys think about this tool:
    https://www.arcgis.com/home/item.html?id=6997e32c54bd40ca8ca13e1fdaf5948b
    Like • Show 0 Likes0
    Actions
bixb0012 Sep 15, 2015 7:41 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
- apence23 @ bixb0012 on Sep 15, 2015 8:01 AM
  Mark Correct
  Correct Answer
  That was one of my ideas since it does it automatically, but they need the ID numbers to be .00001, .00002 and so on
  Like • Show 0 Likes0
  Actions
  - bixb0012 @ apence23 on Sep 15, 2015 9:57 AM
    Mark Correct
    Correct Answer
    But they need a working system more, right?
    Like • Show 0 Likes0
    Actions
    - apence23 @ bixb0012 on Sep 15, 2015 10:22 AM
      Mark Correct
      Correct Answer
      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
      Like • Show 0 Likes0
      Actions
      - mvolz47 @ apence23 on Jul 6, 2018 11:12 AM
        Mark Correct
        Correct Answer
        Would the concept of NextSequenceValue of Attribute Rules (ArcPro) in this thread work for you?
        
        Create an Auto-Increment numeric data type for Geodatabase fields
        Like • Show 0 Likes0
        Actions
        andrescastillo08 @ mvolz47 on Jul 6, 2018 2:11 PM
        Mark Correct
        Correct Answer
        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
        Like • Show 0 Likes0
        Actions
        mvolz47 @ andrescastillo08 on Jul 9, 2018 5:57 AM
        Mark Correct
        Correct Answer
        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:
        
        ArcGIS Pro Publish Services To ArcGIS Server
        
        You might want to vote up this idea if you have not already done so.
        1 person found this helpful
        Like • Show 1 Like1
        Actions