asked on

Duplicate Numbering happening in an auto numbering routine

Hi,

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;
REM;
@MailSend(SendTo; ""; ""; Subject; Remark; "";"");
@MailSend((@Name([CN];@UserName));"";"";Subject;Remark;"";"");
@MailSend(@GetProfileField("(Job Profile)";"JBP_AccountsPerson"); ""; ""; Subject; Remark; "";"").

ASKER CERTIFIED SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sjef Bosman

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.

Neetanayak

ASKER

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

Sjef Bosman

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

SOLUTION

Bozzie4

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bozzie4

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

Tom

Jean Marie Geeraerts

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
loop

'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.

Regards,
JM

Bozzie4

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 ....

cheers,

Tom

Jean Marie Geeraerts

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).