Hi Gentlemen/Ladies
I wonder if you guys could help me with an issue. I currently stored Purchase Order numbers in a view… single column. The view looks up all Purchase Order numbers on my Sales Orders Documents.
The problem i have is when my 3 buyers process orders using a Process Order button. The button gets the next PO# by doing a lookup on the view…
code :
tmpLastPoNum1 := @Word(@DbColumn(“”:“NoCache”;“”:“”;“OrderNos\” + @Left(CompanyAbrv ; 1)) ;"; " ; 1) ;
tmpLastPoNum := @Subset(@DbColumn(“”:“NoCache”;“”:“”;“OrderNos\” + @DbLookup(“” : “NoCache” ; “” ; “Admin\Companies” ; Company ; 6);1);+1) ;
tmpNextPoNum1 := @If(tmpLastPoNum = “” ; “10001” ; @Text(@TextToNumber(@Right(tmpLastPoNum ; @Length(tmpLastPoNum) -1)) + 1)) ;
tmpNextPoNum :=@If(tmpLastPoNum = “” ; “10001” ; @Text(@TextToNumber(tmpLastPoNum) + 1)) ;
tmpPurchaseOrder := @If(@Text(@Left(DeptCode ; 1)) = “3” ; “T” ;@Text(@Left(DeptCode; 1)) = 6; “F”; @DbLookup(“” : “NoCache” ; “” ; “Admin\Companies” ; Company ; 6)) + tmpNextPoNum ;
Now. The issue is that sometimes i have buyers who issue the same order number when they process orders. I could force all my buyers to go through our citrix solution, which would force them all to use 1 License/Tunnel for acces to Lotus, and this has transaction handling etc… However, thats a waste on my citrix licenses.
Is there a way to Lock a view for only 1 DBLookup/DBColumn at a time? This way forcing consistent order numbers on my sales orders?
Any ideas would be greatly appreciated.
Subject: Your requirement for Sequential numbering
is very common
and the method you’ve chosen i.e. using a view is one that many people try
BUT unfortunately this a BAD solution
There are very many problems. Search this and the R4/R5 forum for ‘Sequential Numbers’ to see what and why 
.
As to your specific mechanism to fix this… it might help but I dont know any direct way to lock a view and the indirect ways that might work are over complicated.
You had best adopt a completely different approach to sequential numbering.
again this forum has many solutions but if I wre to do this I might choose to start by storing the next sequential number in a separate “counter” document (NOT a profile)
and produce code to
find and lock that document
update your working document
update the count
save and free the counter document
This can be done by formula or more easily by lotus script
That may seem complex but Accurate sequential numbering is difficult even if all users are accessing the replica on the same server. It’s even worse if users are on different replicas 
have fun … and let us know how you get on
Subject: View Locking
Looks like this is a touchy subject with many threads. Thank you so much for your responses. I do need absolute sequencing in my numbers i.e. 567, 568, 569 etc. as they are my requesition numbers. Bearing in mind my users cant even use their computers properly id hate to see them chasing up req’s based on UNIQUE numbers. However, i’ve managed to go with the first suggestion and its working great!!
This would definetly be something IBM should have a look into. I don’t know a database system other than Notes that doesnt have some sort of IDing/Sequencing built in…
Thanks again guys
Subject: RE: View Locking
Nor do you know of another database that allows the same kind of independant unconnected use. (I’d hate to think of the kinds of people you’re working with if they can’t handle a ten-character unique identifier; the cost of the telephone assistance department must be a huge part of your operating budget.)
Subject: RE: View Locking
Alan is right, but in addition I encourage you to push back on the requirement. Often people ask for sequential numbers when there is no need for them, and all they really require is a unique identifier. That’s much easier to do, and there are ways to do it that let you preserve all the special benefits of using Notes (high availability thru multiple replicas, and working offline in a local replica, for instance).
Subject: RE: View Locking
I don’t agree Andre. We have many applications where the requirements are for a sequential number. They don’t want a guid or @Unique.
Think of the number as more of a verbal/written communication tool.
Subject: RE: View Locking
I know you have many applications where the customer asks for a sequential number. I just think people ask for it a lot without realizing that it’s more than is required and will cost them more than they realize. If you point these things out to them they might well be happy with an identifier which is merely unique.
How is 0055403 is any more of a communication tool than TY45E8U? Don’t they both identify the document? What more do you need from the identifier?
Subject: RE: View Locking
Maybe I’m used to creating applications for call centers and service groups.
Users find it much easier to discuss ticket number 403 (in your scenario of 0055403) than TY45E8U (they can’t abbreviate because the next one in the view might be IIP47E8U).
Time is money and customer satisfaction to these people.
Subject: RE: View Locking
I realize that numeric identifiers are occasionally useful, and I will even admit that sequential identifiers are sometimes necessary, which is why I wrote this blog entry describing one way to do it right: Sequential numbering by outside database.
Here in Lotus, we constantly discuss SPRs by their IDs, which are assigned by @Unique, and we usually refer to them in just the way you mention – by citing the last three characters. Never once in my experience has this been ambiguous, among a set of SPRs under discussion (e.g. the ones assigned to me – of which there are many). There are many more unique combinations of three of the 32 characters used for @Unique, than there are of three digits. So if you have a lot of requests being created for a lot of different customers, the odds of two with the same last three characters being in the open issues for a particular customer, is much higher if they are digits – though neither one is terribly probable.
Alternatively, if you like having all digits, you can easily enough assign unique identifiers that are all digits by assigning each user a range of numbers. The point is to allow for offline use and replication. Again, your chances of the last three digits matching more than one document for a given customer are pretty small – and more to the point, they’re about the same whether the numbers are sequential or not.