How do you generate sequential document IDs?

Computer Associates sells a sequential number generator as part of the QXCOM Tools.

A company called Sentor Communications also has a product called SequelNum. Call (+61 2 9391 0544) or email for more info. The list price is US$155 or AU$195.

I company called SISDAM also has Loyal Sequencer.

Under Version 4, you can use a triggered agent on the server which uses a locked file like so:

  Sub Initialize
  file% = Freefile
  Open "c:\\seqnum.bin" For Binary Access Read Write Lock Read As file%
   ' You can also use Open "\\NotesServer\SharedDirectory\seqnum.bin"
   ' if your client and server are Win95 or NT based.  Open can take
   ' a UNC path
  Get #file%, 1, SeqNum%
  SeqNum% = SeqNum% + 1
  Put #file%, 1, SeqNum%
  Close file%
  End Sub

This method does solve the race condition possible if you use the "field stored in a document technique", but the limitation of this is that it won't work with replicated databases (user or server). However, note that this agent cannot be run from the client because running from the client will cause the sequential numbering file to be created on the client machine.

Here is another technique that will work only if your client machines access a non-replicated database on a single server:

' MBCounter&(CounterName$, CounterToSave%)
'
' Marco Beri 
' marcob@equalis.it
'
' This functions give one or more progressive unique counters without race conflict condition
'
' Parameters:
'    CounterName$: counter name; every counter name represents a different counter; you could have as many counters as you want
'    CounterToSave%: if false MBCounter& just returns next free number, if true it gives you next free number and increment it by one
'
' Return value:
'    a long integer
'
' Usage:
'   MBCounter("EveryCounterNameYouWant", False) in QueryOpen if and only if it is a new document and just to display the next free number.
'   MBCounter("EveryCounterNameYouWant", True) in QuerySave if and only if it is a new document, just before exiting and only if you really
'   save the document (so you are sure not to waste a free number)
'
' 

Attention:
' - you cannot use form, view or folder with a name like (MBCounters); if you need this name change COUNTER_FOLDER_FORM_NAME$ constant value
' - if you want you can create a view to visualize the document with form COUNTER_FOLDER_FORM_NAME$ to check the counter values
'

Option Public
Option Explicit
Const COUNTER_FOLDER_FORM_NAME$="(MBCounters)"

Function MBCounter&(Byval CounterName$, Byval CounterToSave%)
     On Error Goto MBCounterError

     Dim CounterFieldName$
     Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim view As NotesView
     Dim doc As NotesDocument
     Dim docNext As NotesDocument
     Dim CounterValue&
     Dim FieldValue As Variant
     Dim Ret%
     Dim item As NotesItem     
     Dim FolderToBeCreated%
     
     CounterFieldName$ = "MB"+CounterName$
     Set db = session.CurrentDatabase
     Set view = db.GetView(COUNTER_FOLDER_FORM_NAME$)
     If view Is Nothing Then
          FolderToBeCreated%=True
     End If
     
     Do
          If Not FolderToBeCreated% Then
               view.refresh 
               Set doc = nothing
               Set doc = view.GetFirstDocument()
          End If
          If doc Is Nothing Or FolderToBeCreated% Then               
               Set doc = New NotesDocument(db)
               doc.Form = COUNTER_FOLDER_FORM_NAME$
               Call doc.Save(True, False)
               doc.putinfolder COUNTER_FOLDER_FORM_NAME$
               If FolderToBeCreated% Then
                    Set view = db.GetView(COUNTER_FOLDER_FORM_NAME$)
                    FolderToBeCreated% = False
               End If
          Else
               Set item=doc.GetFirstItem(CounterFieldName$)
               If item Is Nothing Then
                    Call doc.ReplaceItemValue( CounterFieldName$, 0 )
                    Call doc.Save(False, False)
               Else
                    FieldValue=doc.GetItemValue(CounterFieldName$)
                    If Not CounterToSave% Then
                         CounterValue&=FieldValue(0)+1
                         Exit Do
                    Else
                         Call doc.ReplaceItemValue( CounterFieldName$, FieldValue(0)+1)
                         Ret%=doc.Save(False,False)
                         If Ret% Then
                              CounterValue&=FieldValue(0)+1
                              Exit Do
                         End If
                    End If
               End If
          End If
     Loop

     ' This deletes double docs incidentally created in the Loop (almost impossible 
     ' to happen, just first time and not a problem, but just to be clean)
     ' [For the race condition at the start if two users access an uninitialized
     ' counter at the same time.  The best workaround for this is to create
     ' the initial counter by hand..Ed.]
     Set doc = view.GetNextDocument(doc)
     Do While Not doc Is Nothing
          Set docNext = view.GetNextDocument(doc)
          Call doc.Remove(True)
          Set doc=docNext
     Loop
     
     MBCounter& = CounterValue&
     
MBCounterExit:     
     Exit Function
     
MBCounterError:     
     Msgbox "MBCounter", Err, Error$
     MBCounter& = -1
     Goto MBCounterExit
End Function

Applies to Notes Versions: 3 4 4.5 4.6 5