We have a button on a form that is used to generate a sequencial number from a hidden view.
The code in the button is:
@If(@IsError(@Text(@TextToNumber(@LeftBack(@Subset( @DbColumn (“Notes” : “NoCache”; @DbName; “Z Jobs By Number”; 1); 1) ; “-”)) +1)) ; 9000 ;@Text(@TextToNumber(@LeftBack(@Subset( @DbColumn (“Notes” : “NoCache”; @DbName; “Z Jobs By Number”; 1); 1) ; “-”)) +1)); JobNumber);
The view has a selection formula of:
SELECT Status != “Template” & JobNumberWhole != “” & @LeftBack(JobNumberWhole;“-”)!=“” & @TextToNumber(@LeftBack(JobNumberWhole;“-”))>61500
and everything works great, and has for many years. However, when we change the selection formula to a more-dynamic code the sequencial numbers that are generated start to duplicate. Not always, but more times that not it will generate the same number for the new document even though I can verify that in the hidden view it is, in fact, already used. The duplicate numbers are sometimes generated 2-10 times then it will finally step up the number, once or twice, then start repeating again.
The new code for the selection formula that causes this symptom is:
SELECT Status != “Template” & JobNumberWhole != “” & @Date(Datein)>@Date(@Adjust(@Now;0;0;-30;0;0;0))
The original code whos selection formual uses a >jobnumber criteria works perfectly.
The new code that uses a more dynamic >a certain date criteria causes this perplexing symptom.
Any help would be most appreciated.
Subject: RE: Very Strange and Perplexing Symptom…
You can read about the performance implications of using @Now in a view selection formula here and in various other places. The index of such a view is handled differently than a view with a “normal” selection formula.
Do not use such a view for generating your unique numbers.
In this forum, within the last few days, I replied to someone with a formula almost exactly like yours on how they could improve performance. Search for it.
Subject: RE: Very Strange and Perplexing Symptom…
Thank you for your insight. I thought it might have to do with the way that indexing worked with @now.
Do you know of a way to select documents in a view compared to a date critera? Is there a work around to using @now in the view selection?
Subject: RE: Very Strange and Perplexing Symptom…
Yes – the workaround is to use a different view that doesn’t have dates in its selection criteria.
If you want to have the same effect in the view you are using without using @Now in the selection formula, you can use an agent to run nightly and check whether a document is old enough to not appear there. If it is, assign a hidden field that the selection formula can check. This will not only give your users better performance using this view, but your formula will work – with the caveats others have expressed.
Subject: Very Strange and Perplexing Symptom…
I don’t exactly know what causing Your problem, but the problems and difficulties of having sequential numbering in Notes has been widely discussed in both ND6 and Notes4/5 forums. There has also been a LDD Today article about the issue:http://www-10.lotus.com/ldd/today.nsf/62f62847467a8f78052568a80055b380/74bd0cdadb2602c385256b56001c0517?OpenDocument&Highlight=0,sequential,numbering
My two cents about Your problem:
The first problem is that You’re having a jobnumber created through a dblookup in a view that increases the number by one each time. This will cause the duplicates to arise if You try to create the number when a new document is created and not when it is saved. What do You think will happen if two or more persons creates a new document before anyone of the has saved their document? Yes, they will all have the same jobnumber in their newly created document and voila - You have duplicates…
A way to minimize duplicates is to have the jobnumber to be created when the document is saved the first time. But this will also have flaws and possibilities for duplicates.
Search the forum for sequential numbering:
http://www-10.lotus.com/ldd/nd6forum.nsf/Search?SearchView&Query=sequential%20numbering&SearchOrder=0&Start=1&Count=100
and You will have some ideas of how to solve Your problems.
hth