Hi. I am looking for a way to generate record numbers for documents created in a database. I want each one to be unique, but they should also be sequential, so @Unique won’t work (also, we have a related DB that uses @Unique to generate record #'s, and it would make things too confusing).
I am currently getting a count of the records in a view that contains all of these records, and adding 1 to it, but that doesn’t work well because if someone deletes a record the next one created will have the same number as the last one. It also causes a problem if 2 people are creating a document at the same time, although I sorta worked around that by having the number generated when the document is saved.
I am thinking about having it check the # of the most recently created document in the view and add 1 to that. Will that work? How can I grab that doc?
Any ideas? Thanks!
Subject: Sequential numbering…one way.
Create a view GetNumber with ProjectID filed sorted largest to smallest. It will take last (largest) number and add 1
Formula
@If (@IsNewDoc & @IsDocBeingSaved; @Success;@Return(ProjectId));
Num := @DbColumn(“Notes”:“NoCashe”;“”;“GetNumber”;1);
Num1 := @If(@Iserror(Num) | Num=“”;0;@TextToNumber(@Subset(Num;1)));
NewNum := @Text(Num1+1);
@Right(“0000” + NewNum;4);
ANOTHER METHOD:
@If(@IsNewDoc & @IsDocBeingSaved; @Subset(@DbColumn(“” : “NoCache”; “”
: “”; “RMA Number”; 1); 1) + 1; RMAno)
Subject: Another way…using a profile document
Description: * This code goes in the “Initialize” event of an agent, set to
run if documents have been created or modified, and on newly
modified documents. The field that is updated is called
"number" and is an editable text field. The default value of
the "number" field is @If(number = "" ; "TBA" ; number). A
hidden view and profile documents are also used.
This agent is good up to 99,999 documents that contain the
field "number".
Code: *
Sub Initialize
Dim Session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim ProfileDoc As NotesDocument
Dim doc As NotesDocument
Dim item As NotesItem
Dim item2 As String
Dim Nitem As NotesItem
Dim Nitem2 As String
Dim num As Integer
Dim NumStr As String
Dim docnum As Integer
Dim TBAnum As Integer
Dim initialnum As Integer
Set db = Session.CurrentDatabase
Set view = db.GetView("HiddenViewName")
Set ProfileDoc = db.GetProfileDocument("ProfileDocName")
Dim maildoc As NotesDocument
Set maildoc = New NotesDocument(db)
Dim mailitem As NotesItem
Call db.UpdateFTIndex(True)
docnum = view.FTSearch("*",0)
TBAnum = view.FTSearch("TBA",0)
NumStr = ProfileDoc.nextid(0)
If NumStr = "" Then
initialnum = docnum - TBAnum
num = initialnum
Else
num = Cint(NumStr)
End If
ProfileDoc.nextid = Cstr(num)
Call ProfileDoc.save(False,False)
If TBAnum > 0 Then
For i = 1 To TBAnum
Set doc = view.GetNthDocument(i)
Set item = doc.GetFirstItem("number")
item2 = item.text
If item2 = "TBA" Then
num = num + 1
If num < 10 Then
newnum = "0000" & Cstr(num)
Elseif num < 100 Then
newnum = "000" & Cstr(num)
Elseif num < 1000 Then
newnum = "00" & Cstr(num)
Elseif num < 10000 Then
newnum = "0" & Cstr(num)
Elseif num < 100000 Then
newnum = "0" & Cstr(num)
Else
newnum = newnum
End If
doc.number = newnum
Call doc.save(False,False)
ProfileDoc.nextid =Cstr(num)
Call ProfileDoc.save(False,False)
End If
Next
End If
Subject: Another way…using a profile document
Description: *
This code goes in the "Initialize" event of an agent, set to
run if documents have been created or modified, and on newly
modified documents. The field that is updated is called
"number" and is an editable text field. The default value of
the "number" field is @If(number = "" ; "TBA" ; number). A
hidden view and profile documents are also used.
This agent is good up to 99,999 documents that contain the
field "number".
Code: *
Sub Initialize
Dim Session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim ProfileDoc As NotesDocument
Dim doc As NotesDocument
Dim item As NotesItem
Dim item2 As String
Dim Nitem As NotesItem
Dim Nitem2 As String
Dim num As Integer
Dim NumStr As String
Dim docnum As Integer
Dim TBAnum As Integer
Dim initialnum As Integer
Set db = Session.CurrentDatabase
Set view = db.GetView("HiddenViewName")
Set ProfileDoc = db.GetProfileDocument("ProfileDocName")
Dim maildoc As NotesDocument
Set maildoc = New NotesDocument(db)
Dim mailitem As NotesItem
Call db.UpdateFTIndex(True)
docnum = view.FTSearch("*",0)
TBAnum = view.FTSearch("TBA",0)
NumStr = ProfileDoc.nextid(0)
If NumStr = "" Then
initialnum = docnum - TBAnum
num = initialnum
Else
num = Cint(NumStr)
End If
ProfileDoc.nextid = Cstr(num)
Call ProfileDoc.save(False,False)
If TBAnum > 0 Then
For i = 1 To TBAnum
Set doc = view.GetNthDocument(i)
Set item = doc.GetFirstItem("number")
item2 = item.text
If item2 = "TBA" Then
num = num + 1
If num < 10 Then
newnum = "0000" & Cstr(num)
Elseif num < 100 Then
newnum = "000" & Cstr(num)
Elseif num < 1000 Then
newnum = "00" & Cstr(num)
Elseif num < 10000 Then
newnum = "0" & Cstr(num)
Elseif num < 100000 Then
newnum = "0" & Cstr(num)
Else
newnum = newnum
End If
doc.number = newnum
Call doc.save(False,False)
ProfileDoc.nextid =Cstr(num)
Call ProfileDoc.save(False,False)
End If
Next
End If
End Sub