Automated unique sequential ID field.

[MayaDominice]

Hi all,

I posted this in another ArcGIS forum (link), and it was suggested to post it here as well.

[INDENT]This may be a bit of a newbie question but I was hoping someone could help me. I need to create a unique sequential id field ("ID_NUMBER") that is not a GlobalID or based on the Object ID field and whose range is fixed (say from 200,000 to 400,000). It is for a feature class of approx 1000 records. I have managed to use the field calculator to populate the "ID_NUMBER" field for existing records, but what I would like to do now is to automate this process so that when a new feature is drawn/created the "ID_NUMBER" field is automatically populated with the next available ID number within the specified range.

Is there a way to do this using existing ArcGIS functionality/settings, such as through subtypes or domains? Or will this need to be programmatically implemented? I've had a brief look through the previous forums but couldn't find anything to point me in the right direction.

I am working with ArcGIS Desktop 9.3.1 and have access to an ArcInfo licence.

Any advice much appreciated![/INDENT]

Cheers,
Maya

[BrettHill]

Hey Maya, here's a good place to start, it took me a little while to remember where I found this.  The State of Utah put this nice little VBA script together.  It doesn't generate a unique id, but it does use an Editor event listener that responds to an On Create Feature event.  You'd need to modify the script to generate a unique id rather than applying a time stamp.

http://gis.utah.gov/code-visual-basic/vba-edit-timestamp-script-2

I talked to someone at the users conference last year who had a different solution.  He created Python script and scheduled it to run every night.  The script first found the Max ID and then all the features with a null ID.  It then populated the null IDs incrementing from the Max ID.

Good Luck!

[SteveFang]

You are likely looking at some kind of customization in ArcGIS for what you want to do.  If you are using an enterprise DBMS (Oracle/SQL server) then you might want to look into setting up triggers on the DB level as an alternative.

[MayaDominice]

Thanks for the pointers - much appreciated!

At this stage the data is being maintained and updated in a single file geodatabase, but it is envisioned for it to eventually migrate to a DBMS environment, which I imagine is definitely something to keep in mind when setting up fields.

I will have a look at both options (the VBA code and Python script idea) and see which suit the situation the best (perhaps even a combination of the two?). From what I can remember offhand its possible to call a Python script from within VBA code, though I could be wrong.

My VBA and Python skills are at a somewhat beginner-intermediate level at this stage, so I will give it a go and see what comes out of it. I'll post back with any code/problems, in case other people are having similar troubles/questions 🙂

Cheers,
Maya.

[MayaDominice]

Hi all,

I've managed to figure out a (if anything, temporary) solution. This is the code I've written, as adapted from the script provided on the State of Utah website (by Bert Granberg). It seems to work, though I haven't had much of a chance to test it thoroughly. I'm also going to add in an error handler to check for duplicate/non-unique/Null values as well as non-numeric/'user-accidentally-entered/altered' values. But this is the code as it stands at the moment. Thanks again for the pointers! If anyone has any further advice/comments, post away; otherwise, consider this query answered 🙂

'===========================================================================
'Title: Automate ID_NUMBER field population
'Purpose: To automatically populate the above mentioned field with a unique sequential ID number.
'Date: 14/03/2011
'Author: Maya Dominice
'References:The code was adapted from Bert Granberg's "VBA Edit & Timestamp Script", available at:
'http://gis.utah.gov/code-visual-basic/vba-edit-timestamp-script-2
'===========================================================================
'===========================================================================
Option Explicit

Private m_bPopulate As Boolean
Private m_EditEventChange As Boolean
Private WithEvents m_pEditEvents As Editor

Private Function MxDocument_OpenDocument() As Boolean

    Dim pEditor As IEditor
    Dim pUID As New UID

    pUID = "esriCore.Editor"
    Set pEditor = Application.FindExtensionByCLSID(pUID)
   
If pEditor Is Nothing Then
        MsgBox "Unable to Enable UIC Custom Editing Environment", vbOKOnly, "ERROR...UIC Editing Environment"
        Exit Function
    End If

    Set m_pEditEvents = pEditor
    m_bPopulate = True
    m_EditEventChange = False

    MsgBox "Edit Event Field Population is Enabled", vbOKOnly, "AGRC Custom Editing..."

End Function


Private Sub m_pEditEvents_OnCreateFeature(ByVal obj As esriGeoDatabase.IObject)

'Creates new ID number for new feature

  Dim pRow As IRow
  Dim newIDIndex As Integer
  Dim pTable As ITable
  Dim pCursor As ICursor
  Dim pData As IDataStatistics
  Dim pStatResults As IStatisticsResults
  Dim IDMax As Variant
  Dim NewID As Long
  Dim changeMade As Boolean
 
  Set pRow = obj
 
  If Not m_bPopulate Then Exit Sub
 
  'Find index (as long) of "ID_NUMBER" field
  newIDIndex = pRow.Fields.FindField("ID_NUMBER")
 
  'Set up cursor
  Set pTable = obj.Table
  Set pCursor = pTable.Search(Nothing, False)
 
  'Set up statistics parameters
  Set pData = New DataStatistics
  pData.Field = "ID_NUMBER"
  Set pData.Cursor = pCursor
 
  'Find the max value of the "ID_NUMBER" field
  Set pStatResults = pData.Statistics
  IDMax = pStatResults.Maximum
 
  'Add 1 to create next (sequential)ID number
  NewID = IDMax + 1
 
  changeMade = False
 
  If newIDIndex > 0 Then
    pRow.Value(newIDIndex) = NewID
    changeMade = True
  End If
 
  If changeMade Then
    m_EditEventChange = True
    pRow.Store
  End If

  m_EditEventChange = False

End Sub