Solved

Duplicate Numbering happening in an auto numbering routine

Posted on 2004-05-03
12
281 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now