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!
What happens when two editors fire off the app at the same time?
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.
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.
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?
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
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
/
Thank you, I will look into this.
What do you guys think about this tool:
https://www.arcgis.com/home/item.html?id=6997e32c54bd40ca8ca13e1fdaf5948b
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.
That was one of my ideas since it does it automatically, but they need the ID numbers to be .00001, .00002 and so on