Marko Bonaci commented on Apr 2, 2010

Assigning Unique/Sequential Document Identifiers

I added the new article explaining how to use document locking to implement number sequencing:

Marko Bonaci commented on Apr 2, 2010

Assigning Unique/Sequential Document Identifiers

Great article!

I noticed that many inexperienced Lotus developers develop unreliable document numbering solutions, usually employing view counting method. To be completely honest, the first thing that came to my mind 6 years ago was exactly that.

Why "counting view documents" approach is not reliable way to go?

Let's analyze how that works:

1 - new document receives save request from the user (QS) and calls the function that gets next number

2 - the function gets a database and a view (sorted descending by doc number)

3 - the function sets the reference to the top view document

4 - the function reads the value from the document field (or counts the number of docs in a view)

5 - the function returns last number incremented by 1

6 - new document receives value from the function and stores it in a field

7 - new document is saved

Now, what if there are multiple new documents being saved on, lets say 3 workstations, at the same time.

Notice that steps 4 to 7 are all critical. If someone saves his new document while you're on any of those steps, his document is going to receive the same sequence number as yours.

The point is that you need some kind of locking mechanism.

If you want to read more about this subject search on:

- critical section

- thread (thread safe, semaphores, monitors)

- mutex

This is how the NotesDocument's Lock method works (available from version 6):

- if the document is not locked, the method places the lock and returns True

- if the document is locked and the current user is one of the lock holders, the method returns True

- if the document is locked and the current user is not one of the lock holders, the method returns False

- if the document is modified by another user before the lock can be placed, the method raises an error

This approach requires that you either:

- ALWAYS have access to administration server (server that provides document locking)

- or use composite document key where one of the components would be location (OU - organizational unit), which would then produce sequential numbering on OU level, and that way globaly provide unique document keys

Design prereqs and implementation:

1. in your db, on all forms that you need sequential numbering for, add hidden, text field - strDocType

2. create new ScriptLibrary (or open an existing one) and add this function to it:

Function GenSeqNo( docType As String ) As String

'// mbonaci, 11.03.2006

'// - returns next sequence number (as String) for document of type

'// - increments counter doc


'// add your custom error handling

Dim s As New NotesSession

Dim w As New NotesUIWorkspace

Dim db As NotesDatabase

Dim v As NotesView

Dim doc As NotesDocument

Dim nextNum As Integer

'// db that holds counter docs - pull these settings from a profile doc

Set db = s.GetDatabase( "", "" )

'// you may want to replace msgbox with something that best suits your needs

If db Is Nothing Then

Msgbox "Lock db doesn't exist"

End If

'// exit if document locking is not enabled in db properties

If Not db.IsDocumentLockingEnabled Then

Msgbox "Document locking not enabled"

Exit Function

End If

Set v = db.GetView( "SeqNoView" ) '// get the view containing the counter doc

Set doc = v.GetDocumentByKey( docType, True ) '// locate counter doc for docType

Call doc.Lock( True )

nextNum = Cint( doc.strSeqNo(0) ) + 1 '// retrieve next number

doc.strSeqNo = Cstr( nextNum ) '// increment existing number in counter doc

Call doc.Save( True, False, False ) '// save should always return true - doc is locked by myself

Call doc.Unlock( )

GenSeqNo = Cstr( nextNum )

End Function

3. on each form you want to implement numbering for:

- use the script library you just created in Form's Globals > Options (Use "")

- hard-code this in PostOpen event:

Sub Postopen(Source As Notesuidocument)

'// Adding value to type field of new documents

If source.IsNewDoc Then

source.document.strDocType = ""

End If

End Sub

- add this piece of code to QuerySave form event:

Sub QuerySave( Source As NotesUIDocument, Continue As Variant )

'// Get sequence number from QuerySave, but only if it hasn't yet been issued

Dim newSeq As String

If source.document.sRegNo(0) = "" Then

newSeq = GenSeqNo( source.document.strDocType(0) )

source.Document.sRegNo = "composite_key_components-" & newSeq

End If

End Sub

5. create new (blank) database that will hold counter docs (we'll call it "counter db")

6. on counter db:

- db Access control > Advanced tab > Administration server > select option "Server" and choose master lock server from the list

- db properties > first tab > check option "Allow document locking"

- create new form (counter doc) and add two text fields:

- docType //holds document type name (value from your document's strDocType field)

- strSeqNo //holds last issued number for that doc type

- create new view that would:

- show all counter docs

- have first column show docType field, categorized, ascending

7. create new counter document for each document type

- in docType field enter the same value you hard-coded in form's PostOpen event, in strDocType field

- in strSeqNo field enter 0 (or any other number you want start counting from)

That's it, now test...

To test your solution (whichever you choose to implement) you should create an agent that requests 1000 sequence numbers (by calling getSeqNo function 1000 times).

Then start the agent on at least three workstations simultaneously.

If all the numbers are unique you're good to go.

Harris Huckabee commented on Dec 11, 2008

technique that uses combo of sequential number and alphas to provide uniqueness and sortability

Andre, over the years I have benefitted from reading the good advice you have given to many people. I have pondered frequently the problem of key uniqueness/user readability. The solution I use in a lot of my projects produces a number like this... "00105-RDY". It produces a fairly sortable and readable identifier. It is much like the "user initials" solution, but instead it turns the "seconds" component of the time of day into base26 alphas. One lookup at time of first save to get the sequential component... the alpha component is computed when composed.

It is a compromise. Duplicate sequential numbers can occur as users work on unsynchronized local replicas etc, but the alpha component will only be the same if a confluence of events record is first saved during the same 5-second interval. Not perfect sorting but generally pretty good, and not perfect uniqueness, but pretty close.

I was thinking that if I were to make it a little stronger for higher-transaction loads, I would add in an additional alpha for a component of the user name. That might give it a 15-or 20-fold increase in likelihood of uniqueness. But I don't like to add the extra alpha unless needed because it makes the numbers less readable for humans (call desks, etc).