How to main the sequentially generated numbers for each document when Replication is enabled usin Lotus Script?


Current Scenario is - When a document is created, a number is assigned to that particular document. This is incremented sequentially as and when users created documents on Production. Issue faced, is when Replication is enabled, there is a duplication of these sequentially generated numbers. How do we maintain them unique when Replication is being used? Please help.
Who is Participating?
RonaldZaalConnect With a Mentor Commented:
in the code snippets below you find the code
doc Postopen
function calculate the number
the function called by the agent which runs as server so it sees all documents.
You need to check/change some field names!
Let me know how things workout.

' we use Postopen, you can maybe better use QuerySave in your case
Sub Postopen(Source As Notesuidocument)
'__ create Ticket NR if possible / needed
	Dim ticketNr As String	
	Dim doc As notesdocument     
	Dim db As NotesDatabase
	Dim parameterDoc As NotesDocument
	Dim homeServer As  NotesName
	Dim currentServer As  NotesName
	Set doc = source.Document
	Set db = doc.ParentDatabase
	If doc.IsNewNote Then ' new doc, check home server 
		Set parameterDoc = db.GetProfileDocument("nApplicationProfile")
		Set homeServer = New NotesName(parameterDoc.GetItemValue("homeServer")(0))
		Set currentServer = New NotesName(db.Server)
		If homeServer.Common = currentServer.Common Then
			doc.gesaNR = CalcgesaNR(Source)  ' get me my number
			Call source.Refresh
		End If
	End If	
End Sub

The function called resides in a script lib
Function CalcgesaNR(uidoc As NotesUIDocument) As String
'__ function to get next Ticket Nr when user is working on the HomeServer
'__called by doc postopen
	Dim session As New notessession
	Dim ReturnValue As String
	Dim intReturnValue As Long
	Dim tmpDocID As String
	Dim gesaDB As String
	Dim NumberRoot As String
	Dim Separator As String
	Dim DocType As String
	Dim DocDomain As String
	Dim CheckAgent As notesAgent
	Dim paramdoc As NotesDocument
	ReturnValue = ""
	Set CheckAgent=session.currentdatabase.GetAgent("(GetNextDocNumber)")
	gesaDB=Trim$(uidoc.FieldGetText("gesaDB"))  ' you don't need this, it takes a preset value for the numbering to get AAA-0000021, or BBB-0000232 etc
	Set paramdoc=session.CurrentDatabase.GetProfileDocument( "(GetNumberProfile)" , session.CommonUserName )
	Set Paramdoc=GetProfileDoc(paramdoc)
	Call removeAllItems(paramdoc)
	Call paramdoc.replaceItemValue("gesaDB",gesaDB)
	Call paramdoc.replaceItemValue("gesaNR","")
	Call paramdoc.replaceItemValue("test","before agent")
	Call paramdoc.Save(True,False)
'	Call TESTDoGetNumberList(tmpDocID)          	' For testing of agent
	Call checkAgent.RunOnServer(tmpDocID)		' Normal call to agent
	Set paramdoc=Nothing
	Set paramdoc=session.CurrentDatabase.GetDocumentByID(tmpDocID)
	Set Paramdoc=GetProfileDoc(paramdoc)
	If Len(Cstr(ReturnValue))  > 6 Then
		ReturnValue = Right("0000000"+Cstr(ReturnValue),7)
		ReturnValue= Right("000000"+Cstr(ReturnValue),6)
	End If
End Function

The agent which gets called only has code : call this function. it runs as server
So in the agent you have :
Use "name of scriptlibrary"  ran as server
Call DoGetNumbersList()
Sub DoGetNumbersList()
'Called by agent
	Dim session As New notessession
	Dim curAgent As notesAgent
	Dim paramDocID As String
	Dim paramdoc As NotesDocument	
	Dim viewToSearch As NotesView
	Dim gesaDB As String
	Dim Result As String
	Dim coll As NotesViewEntryCollection
	Set curAgent=session.CurrentAgent
	If curAgent Is Nothing Then Exit Sub

	Set ParamDoc=session.CurrentDatabase.GetDocumentbyID(ParamDocID)
	If Paramdoc Is Nothing Then	Exit Sub
	Set ViewToSearch=Session.CurrentDatabase.GetView("gesaNrs") ' This is your all numbers view
	If ViewToSearch Is Nothing Then Exit Sub
	gesaDB=paramdoc.GetItemValue("gesaDB")(0)  ' this is used to get the prefix used here
	' if you don't need a prefix you can remark this out
	' if you don't use a prefix you can use here to get the last document in the view
	' you have to change the next 3 lines 	
	Set coll=ViewToSearch.GetAllEntriesByKey(gesaDB)
	Dim curEntry As NotesViewEntry
	Set curEntry=Coll.GetFirstEntry  ' it is sorted asc
	Result = curEntry.ColumnValues(1)  ' return first column value
	Call paramdoc.ReplaceItemValue("gesaNR",Result)  ' stock number
	Call paramdoc.replaceItemValue("test","after agent") ' to test if the agent ran ok
	Call paramdoc.Save(True,False)
End Sub

Open in new window

Sjef BosmanGroupware ConsultantCommented:
A great link! My advice:
- don't rely on sequential numbering in Notes... never!
- look for alternatives
- can you use @Unique to produce a unique key?
- last resort: use system-wide locks (could be a disaster for the performance)
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Another option if you need to work with existing numbering and if the numbers don't have to be immediately available:

. Create the document without a number
. Create a view to display documents without numbers, sorted in created order, ascending
. Create a scheduled agent to assign numbers - schedule it to run overnight
We do numbering on multiple db's without problems when using the following setup
- create a profile document for the db, in a field you define a "Main" server
- when a document is created and saved for the first time you check the server and if it is the main calculate or lookup the new number in the querysave of the document
- at night an agent runs on the documents which are not created on the main server to populate the number

Agree with SJef to find alternatives to Sequential numbesr but it seems that some people just love to have them nice and neatly ordered no matter what the designers/admins say :)

Depending on your connectivity to servers and how often new docs are created compared to just edits.

If you are using a field and @dblookup or @DBcolumn then make it pick from a specific server and database rather than null values from current database. also form should save doc immediately once number allocated.
If value/formula  is @error then create a local random ID that agent picks up on central server once every x minutes/hours.
Have a check for duplicates and inform an admin once a day etc.

Sort of combining the above into one solution that covers most times they absolutely insist a document is sequential and generally when creating documents is not as heavy as editing etc.
shals0628Author Commented:
@All - Thanks.

@Sjef - @unique I can propose, but what about the existing documents? As doninja says, people do love sequential numbers!!!

Let me explain you the current working.

A Hidden View is present where action button is available to create a form only to enter a 'Record Number'.  The Admin will create the document and enter the value in Record Number (start point for sequential number)

Once creates document completes all data in the form and click on 'Save and Close' - Script Library is called in which one sub function is called to assign the Number. The code for the same is as below:

Sub AssignChkNo(uidoc As NotesUIDocument)
      Dim recordvw As Notesview
      Dim profdoc As NotesDocument
      If uidoc.IsNewDoc Then
            Set recordvw = db.GetView("(Record Number)")
            Call recordvw.Refresh
            Set profdoc = recordvw.GetFirstDocument
            childnumber = profdoc.Checknumber(0)
            chkdoc.LogNo = childnumber +1            
            profdoc.Checknumber = childnumber +1
            Call profdoc.Save(1,1)
      End If
      End Sub

Is there a possibility to retain the code and achieve the objective to maintain the uniqueness of this number generation?
To retain the code, use RonaldZaal's suggestion, ie add a check for the designated "Main" server and only add numbers for documents on this server.  Documents created on other replicas to be processed by a scheduled agent outside normal office hours, on the designated "Main" server, to assign numbers.
shals0628Author Commented:
@RonaldZaal - Can you please help me how to go about the agent? The Db Profile is already set up and the Number is assigned when user creates the document on Server. At least provide me the logic to it.
@Gingerdeb - Thanks to you too.
gingerdebConnect With a Mentor Commented:
I may be missing something in the code above, but it looks like you still need a scheduled agent to process those documents without numbers that have been created in other replicas.  I don't see anything that's processing a collection of these documents.
RonaldZaalConnect With a Mentor Commented:
Nope GingerDeb, you are better awake then me.

agent code to do initial numbering :
Sub Initialize
	Dim s As New NotesSession
	Dim allDocs As NotesViewEntryCollection
	Dim curView As NotesView
	Dim db As NotesDatabase
	Dim profileDoc As NotesDocument
	Dim GESAdb As String
	Set db = s.CurrentDatabase
	Set curview = db.GetView("gesaNrsByDate") ' your view with all docs by date
	Call curview.Refresh
	curview.AutoUpdate = False
	Set allDocs = curview.AllEntries
	Set profileDoc = db.GetProfileDocument("nApplicationProfile")
	If  profiledoc Is Nothing Then 	Exit Sub ' no profile
	GESAdb = profiledoc.GetItemValue("gesaDB")(0)  ' again for the prefix which you don't need
	Call CalcgesaNR(allDocs, GESAdb)
End Sub

'The function in the same agent which does the actual initial numbering
Sub CalcgesaNR(allDocs As NotesViewEntryCollection, dbname As String) 
	Dim curDoc As NotesDocument
	Dim vEntry As NotesViewEntry
	Dim j  As Integer
	Set vEntry = allDocs.GetFirstEntry
	j = 1  ' counter
	Do Until(vEntry Is Nothing)
		Set curDoc = vEntry.Document
		curdoc.gesaDB = dbname
		If j= 1 Then
			curdoc.gesaNR =  "000001"
			If Len(Cstr(j))  > 6 Then
				curdoc.gesaNR = Right("0000000"+Cstr(j),7)
				curdoc.gesaNR= Right("000000"+Cstr(j),6)
			End If
		End If
		Call curdoc.Save(True,False)		
		j= j+1
		Set vEntry = allDocs.GetNextEntry(vEntry)
End Sub

Open in new window

shals0628Author Commented:
Thanks a lot !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.