Duplicate Numbering happening in an auto numbering routine


I have created a profile document where I store a number. When the user saves a document a number is generated for this document using the following code in Querysave. Sometimes the documents have duplicate numbers & the profile docuemnt is not updated. Could you help me to correct my code  or is there any better way of auto generating numbers.

In the code below JBP_Serialno is the no picked up from the profile document & incremented & saved back.

tmpjobno := "";
ViewName := "LUDebtor";
ViewName1 := "LUProduct";
Key1 := JB_DebtorID;
Key2 := JB_ProductCode;
database := @Subset(@DbName; -1);
server := @Name([CN]; @Subset(@DbName; 1));

@If(@IsNewDoc;@Do(@SetField("JB_JobNumber";@GetProfileField("(Job Profile)";"JBP_OfficeCode")+"/" +@Text((@GetProfileField("(Job Profile)";"JBP_SerialNo") + 1))+"/"+@GetProfileField("(Job Profile)";"JBP_FinancialYr"))  ;@SetProfileField("(Job Profile)";"JBP_SerialNo";@GetProfileField("(Job Profile)";"JBP_SerialNo") + 1));"");

tmpClDesc := @DbLookup("":"NoCache";server:"LocalOffice\\connect\\Timesheet\\Timesheet10c.nsf"; ViewName ; Key1 ;2);
tmpPdDesc := @DbLookup("":"NoCache";server:"LocalOffice\\Connect\\Timesheet\\Timesheet10c.nsf"; ViewName1 ; Key1+key2 ;"PD_ProductName");

SendTo :=@Unique(@Trim(JB_MailTo));
Subject := "Job : -  " + JB_JobNumber + " generated for  "+ JB_JobTitle;
Remark := "Please  note new job :  " + JB_JobNumber + "  has  been created for  " + @NewLine + @NewLine + "Client          : " + tmpcldesc + @NewLine + @NewLine + "Brand          : "   + tmpPddesc + @NewLine + @NewLine + "Title             : " + JB_JobTitle + @NewLine + @NewLine + "Remarks   : " + JB_Remarks;
@MailSend(SendTo; ""; ""; Subject; Remark; "";"");
@MailSend(@GetProfileField("(Job Profile)";"JBP_AccountsPerson"); ""; ""; Subject; Remark; "";"").

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
See http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_20975747.html (Thanks Madheeswar!)

Note that serail numbers generated like this will always fail if the database is to be on multiple servers!
Sjef BosmanGroupware ConsultantCommented:
It's a never-ending story, these serial numbers. It's best done from LotusScript usually, and even then you get into trouble when your database is on multiple servers.

NeetanayakAuthor Commented:
No , My database is on a single server. Can the code  I have pasted be foolproof with some modification.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Sjef BosmanGroupware ConsultantCommented:
Foolproof? Never. It's not embedded in Notes, it can be interrupted by other tasks/users.

What is a bit more reliable is to set the field first, but you have to ask the value immediately:
    @If(@IsNewDoc; jbpsn:= @SetProfileField("(Job Profile)";"JBP_SerialNo";@GetProfileField("(Job Profile)";"JBP_SerialNo") + 1);"");

AFAIK it is legal in R6 to do this... Alternative:
    @If(@IsNewDoc; @Do(
        jbpsn:= @GetProfileField("(Job Profile)";"JBP_SerialNo")+1;
        @SetProfileField("(Job Profile)";"JBP_SerialNo"; jbpsn);

and then use jbpsn as new serial number
Bozzie4Connect With a Mentor Commented:
A Profile document is a *bad* solution for autonumbering.  If someone has the database open, he will open the profile document.  Changes to the profile document (by someone else) will not be visible by the 1st user until he closes and reopens the database.

You could use a normal document, or (the more common way), use a view.

Sort all your documents according to the Serial no. (descending) and then take the number in the first document, and add 1.  This will give a better result then the profile document approach, but this solution won't be 100% foolproof either.

My preferred solution, is to have a scheduled agent assign the serial numbers for new documents.  That way, you can replicate the application between servers (not important in your case), and you can always be sure the serial number is unique.


So in short : your current code can never work correctly :-)

Jean Marie GeeraertsApplication EngineerCommented:
A solution I've used is to build in a 'lock document'.
When you create a new document, first you check if the profile document is free by checking for the existance of a lock document. If no lock document is present, create one.
Update the profile document and then release the profile document by removing the lock document.

The chance that two users at the exact same time open the profile document and create the lock document is negligable (I hope I spelled that right), so you are very unlikely to have duplicate numbers.

If a lock document is present, loop back to the lookup of the lock document until there is no document present.

Sample code:

Dim session as New NotesSession
dim db as NotesDatabase
dim docLock as NotesDocument
dim docProfile as NotesDocument
dim docToNumber as NotesDocument
dim luLock as NotesView

set db=session.CurrentDatabase
set luLock = db.GetView("luLock") 'luLock is the name of the view containing the lock document
set docLock = luLock.GetFirstDocument
do while not (docLock is nothing) 'repeat until no document is found
   set docLock = luLock.GetFirstDocument

'you exit the loop when no document is retrieved
set docLock = db.CreateDocument
docLock.Form = "Lock" 'The name of the form for a lock document is 'Lock'
call docLock.Save(true, true) 'you have now locked the profile document yourself

'Enter the code to create your document and increase the profile document number here

'release the lock
call docLock.Remove

Hope this helps you in any way.

Like I already said, the problem is in using a profile document , so the solution jerrith proposes will still (sometimes) create duplicates, although there is a lock document.  

You can use his solution though, just don't use a profile document to store the number, use a regular document (and put it in a folder, and get it from the folder (or view))

Document locking functions in @formulas / lotusscript are new in R6 btw, so you can use those ....


Jean Marie GeeraertsApplication EngineerCommented:
You're absolutely right, Tom.
My proposed solution is just to minimize the events where a duplicate number will be generated. Creating the lock document takes only a fraction of a second, so you'd have to have split second timing to create a duplicate, but it's always possible. In that case you would get two lock documents as well.

Hm. Nice looks like I really want R6 now :-) (We're still working with R5 here).
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.