This question goes to all developers… My issue is, I need to have a field, on a form that generates consecutive numbers, on every new form… Starting from 1… I am not sure if there is a formula that would do it automatically, Is there???
Now, if the answer is no, I had a crazy idea, but I am not sure if that would work… Again, I’ll need you help to answer that.
What if I had a hidden number field on my form having the number “1” as its default value. Then, I would have a view with total column related to that number field. Now, what I need to do is to have my generator field looks up that view and get the total value from that column. So, that’s my point: Is there anyway to use "@dblookup"to get that total value from the view??? Because, If there is, all I have to do is get that value and add 1… then I would have my generator. I am just not that sure, how to do it. I hope you guys can give me some idea.
First avail, thanks a lot for the answers. They gave some idea about what to do but they didn’t really helped me to solve my problem… I guess if I could figure out a way to use “dblookup” to get the total from a column in a view… That would do it… If one of guys have the answer I would appreciate a lot…
The LS:DO method (or any connection to a relational database) mentioned by Joe Nelson is the ONLY reliable method you’re going to find. Try searching both this forum and the R4 & 5 forum for “sequential AND number” – you’ll find a lot of suggestions, none of which will work all of the time. Basically, sequential numbering and replication don’t get along. You can’t count on views, numbering documents can get out of sync in different replicas, and so on. Even if you use a single, central numbering database, you have to count on there never being two users getting the same document in the same state. An RDBMS with an enforceable increment can do it, so the only way you’re going to make it happen in Notes is to connect to a relational database.
We use profile documents. Create a document with one field and set the default value at one.
On your form that is to have the consecutive number use the @GetProfileField to get the number. In the postsave of the document you can write script to increment the number in the profile document so the next time when a new document is created it will go and retrieve the next number.
The problem you are going to get is duplicate numbers when the system is going to be taxed by a number of users “all at once”. The biggest problem is that you are having the each LN client calculate the number based on conditions on the server.
Supposedly there is a 3rd party product called “Loyal Sequence” but it can not handle a high volume of users hitting a particular sequence.
The ideal solution would be a central system giving the number to the client (not allowing the client to calculate). That way there is a less likely chance of any duplicates.
I have used LS:DO and other Oracle connections to use an Oracle sequence. A query similar to “select sequencename.nextval from dual” where sequencename is the name of the particular sequence. I have had over 100K increments and every document is unique (no duplicate numbers).
All right guys… I’ve spent some time thinking about the subject and after a lot of research I finally got something… It might not be the best solution, but I thing it’s gonna work for me…
What I did it was… I first created one hidden field in my form and set its default value as one. Then, I created a view and a column related with this field.
Now, back to the form, on my number generator field, I used a “dbcolumn” formula to get a list from that view… All I got I was a list with a lot of ones… to solve it, I included in my formula “@count”, which gave the total number of documents in my database… All I had to do then, in order to increment the number was, I added one to the value I was getting, using “@sum”.
I am not sure if I was clear, so here is the formula (that might help you to understand):
You are asking for trouble with this method. If you are going to try and rely on a view count for sequential numbering then at the very least use the query save event to double check that the next number is the same as it was on the query open. I still would not recommend using the approach you described as it is guaranteed to generate duplicates but if you get the number on the query open and double check on the query save (AND MOST IMPORTANTLY do not have very many users on the app) then you may get away with it.
After much searching for a way to generate a sequence number for an autonumber type field I came up with the following solution.
I Made some modifications to some code posted by Mika Heinonen on 17.Feb.99 to the R5 forum and ended up with this scheme that locks the document that has the number, increments it, then unlocks. So, theoretically, it should work, even if you use replication since locking uses a “Master Server”.
forgive the formatting
Create a form that has the field with the sequnce number. Do a “Preview in Notes” to create the doc with this form.
Form
($prSequence)
field
prSequence
type text
field
Authors
type Authors computed
value "*"
Properties
Design
Do Not Show In Menus
Create the form that has an ID field and anything else. The idea being everytime you make a doc with the form it will get a new ID.
Form
TestSequenceNumbers
Field
DocID
type
text computed
Value
@If(prDocID="" | prDocID="AutoNumber";"AutoNumber";@Right("00000" + prDocID;5))
In Globals Section add this function:
Function prGetSequence() As Boolean
Dim workspace As New notesuiworkspace
Dim session As New notessession
Dim db As notesdatabase
Dim view As notesview
Dim doc As notesdocument, uidoc As notesuidocument, uibackdoc As notesdocument
Dim idnum As Integer
Set db=session.currentdatabase
Set view=db.getview("($prSequence)")
Set doc=view.getfirstdocument
Set uidoc=workspace.currentdocument
Set uibackdoc=uidoc.document
prGetSequence = False
On Error 4595 Resume Next 'doc is already locked error
If uibackdoc.prDocId(0)="AutoNumber" Then
If doc Is Nothing Then
Set doc=New notesdocument(db)
doc.Form="($prSequence)"
Call doc.save(True,False)
End If
prGetSequence = doc.lock
If prGetSequence Then
idnum=Val(Trim(doc.prSequence(0)))
idnum=idnum+1
doc.prSequence=idnum
Call doc.save(True,False)
doc.UnLock
Print "Got sequence " & idnum
uibackdoc.prDocID=idnum
End If
Else
prGetSequence = True
End If
End Function
QuerySave Event
Dim prGetSequenceRetryMessage As Integer
prGetSequenceRetryMessage = 1
While prGetSequence() = False
If prGetSequenceRetryMessage =1 Then
Print "Getting new sequence number..."
prGetSequenceRetryMessage = prGetSequenceRetryMessage + 1
Elseif prGetSequenceRetryMessage = 2 Then
Print "Trying to get sequence number again, call 1888 if you are stuck here for more than a few seconds..."
prGetSequenceRetryMessage = prGetSequenceRetryMessage + 1
End If
Sleep 3
Wend
Create a view that sees only one doc - the doc with the sequence number. This view is used in the code as well as you to access the sequcnce number doc.
View
Name
($prSequence)
View Selection
SELECT Form="($prSequence)"
Columns
Number
Created
Modified
prSequence
Hide the doc with Form=“prSequence” from other views. (SELECT Form!=“($prSequence)”)