Solved

Duplicate Numbering happening in an auto numbering routine

Posted on 2004-05-03
12
289 Views
Last Modified: 2013-12-18
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; "";"").

0
Comment
Question by:Neetanayak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
12 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 75 total points
ID: 10976486
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!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10976544
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.

0
 

Author Comment

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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 10976657
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
0
 
LVL 15

Assisted Solution

by:Bozzie4
Bozzie4 earned 75 total points
ID: 10976942
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.

cheers,

Tom
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10977117
So in short : your current code can never work correctly :-)

Tom
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 10997277
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
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10998394
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
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 11003475
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).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question