Create a Unique Document Record Number

Document numbers are often assigned to work orders, service requests, or similar documents for tracking and reference purposes. Document numbers are usually numeric or alphanumeric. Instructions to implement both options are provided in this section. Although there are many potential programmatic solutions to this problem, this section details how to use the form's QuerySave event to assign document numbers.

How It Works

The document record number is generated the first time the document is saved. LotusScript code is added to the QuerySave event. When the user attempts to save the document for the first time, code in the QuerySave event determines whether the RecordNo field contains a value. If a number has already been assigned, the QuerySave event continues executing.

If the document has not been assigned a record number, the routine queries a view and retrieves the top-most document number. This number is incremented and assigned to the document. If no documents are present in the view, a default starting number is assigned to the document.

Three components are required to implement this solutiona hidden view, a computed field, and code in the QuerySave event. The hidden view is used to compute the record number. The first column in the view must contain the record number sorted in descending order. The field is used to store the computed record number. The QuerySave event is used to compute the record number value. This occurs the very first time the user saves the document.

Note

It's important to understand that duplicate document numbers may occur with local or server replicas (e.g., when the database is replicated to multiple servers). Whether a duplicate number is generated will depend on the frequency with which new documents are generated and the frequency with which updates are replicated. Depending on the database usage and replication frequency, this current model may be sufficient. However, if you desire more strict control over the assignment of document numbers, you could (1) disable local replicas, (2) create a scheduled agent to identify and correct duplicate numbers, (3) create a scheduled agent to assign record numbers, or (4) force users to create new documents on the server instance of the database. This last option provides the ability for local replicas but requires that new documents be created on the server instance. See "Limit the Ability to Create Documents on a Local Database" in this chapter for additional information regarding this option.

 

Implementation

The following steps describe the procedures for implementing a document identifier. After the initial setup is complete, you can elect to implement either the numeric or alphanumeric document identifier by selecting from the following code.

Step 1.	Create a new field on the form. In the properties dialog, set the field name to RecordNo and field type to Number and Computed. After the field type has been set to Computed, locate the Value section in the Programmer's pane for this field and insert RecordNo as the formula. If you decide to use a different field name, be sure to adjust the following instructions and code accordingly. Finally, make note of the form name. You will need the form name for step 2. Save and close the form.
Step 2.	Create a view that contains document record numbers. To create the view, select the Create > Design > View menu options. When prompted, set the view name to (RecordNoView) and set the Copy From style to -Blank- (see Figure 13.20). Also check the By Formula option and insert Select Form = "MYFORM" & RecordNo <> "" (where MYFORM represents the form name from step 1) in the formula field.   Figure 13.20. Example view used to track document record numbers Click Save and Customize to create the view.
Step 3.	Set the column value for the view. Click on column 1. Change the field type to Field and select the RecordNo field. Next, select the Design > Column Properties menu options. Switch to tab 2 and select Descending as the sort order. This will ensure that the document with highest RecordNo is displayed at the top of the view. This number will be incremented and assigned to the next document created in the database. Save the view.
Step 4.	Add code to compute the document number. Two implementation options have been provided herenumeric (option A) and alphanumeric (option B). The document number is computed using the QuerySave event. Return to the form and complete the instructions based on the selected option as outlined next.

Option ANumeric Only

This first option illustrates the code to generate a numeric document number. By default, the first document will start with 1000 and will sequentially increment as additional documents are created. To adjust the start number, change the value in the following code.

Step 5a.

Insert this code in the QuerySave event of the form.
 

Sub Querysave(Source As Notesuidocument, Continue As Variant)
 Dim s As NotesSession
 Dim w As NotesUIWorkspace
 Dim uidoc As NotesUIDocument
 Dim doc As NotesDocument
 Dim db As NotesDatabase
 Dim num As Integer
 Dim numView As NotesView
 Dim numDoc As NotesDocument

 '----------------------------------------
 ' Set object values
 '----------------------------------------
 Set s = New NotesSession
 Set db = s.CurrentDatabase
 Set w = New NotesUIWorkspace
 Set uidoc = w.CurrentDocument
 Set doc = uidoc.Document

 '----------------------------------------
 ' Increment the document number
 '----------------------------------------
 If doc.RecordNo(0) = "" Then
 Set numView = db.GetView ("(RecordNoView)")
 Set numDoc = numView.GetFirstDocument
 If (numDoc Is Nothing) Then
 ' Set the default starting number
 num = 1000
 Else
 num = numDoc.RecordNo(0) + 1
 End If
 doc.RecordNo = num
 End If
End Sub

 

Tip

You can prefix a text string on the form (just prior to the field) to give the appearance that the document has an alphanumeric value. This is a simple method to update the interface without having to make significant code changes. For example, let's say you have a form that tracks customer Requests for Service (RFS) (see Figure 13.21). To prefix the document number with "RFS", insert a text string on the form just prior to the field (as illustrated next). You will also need to update all view columns that display this field. This provides a quick implementation solution to convert from a numeric to pseudo-alphanumeric document tracking number.

Figure 13.21. Appending text to the field on a form

 

Option BAlphanumeric

This second option illustrates the code to generate an alphanumeric document number. This option works like the numeric-only option. However, implementation requires two fieldsone field to store the numeric value (e.g., RecordNo) and a second field to store the alphanumeric value (e.g., RecordID). By default, the first document will start with 1000 and will sequentially increment as additional documents are created. The RecordNo field will be used to store only the number valuesimilar to the first option.

After it is computed, this value is then converted to a string and used to create the alphanumeric value. This value is subsequently stored in the RecordID field as a Text value. For example, you could prefix the document identifier with text such as RFS (Request for Service) or SR (Service Request), or you could append the transaction year.

• RFS-1000
• SR-1000-2005
• 2005-SR-1000

Step 5b.	Create a new field on the form. In the properties dialog, set the field name to RecordID and the field type to Number and Computed. After the field type has been set to Computed, locate the Value section in the Programmer's pane for this field and insert RecordID as the formula.
Step 6b.	Insert the following code in the QuerySave event. To adjust the start number, change the default start value. (Note: The following example will produce a document number in the format RFS-1000-2006, where 1000 represents the unique record number and 2006 represents the year the document was created.)   Sub Querysave(Source As Notesuidocument, Continue As Variant) Dim s As NotesSession Dim w As NotesUIWorkspace Dim uidoc As NotesUIDocument Dim doc As NotesDocument Dim db As NotesDatabase Dim num As Integer Dim numView As NotesView Dim numDoc As NotesDocument '---------------------------------------- ' Set object values '---------------------------------------- Set s = New NotesSession Set db = s.CurrentDatabase Set w = New NotesUIWorkspace Set uidoc = w.CurrentDocument Set doc = uidoc.Document '---------------------------------------- ' Increment the document number '---------------------------------------- If doc.RecordNo(0) = "" Then Set numView = db.GetView ("(RecordNoView)") Set numDoc = numView.GetFirstDocument '---------------------------------------- ' Set the numeric value '---------------------------------------- If (numDoc Is Nothing) Then ' Set the default starting number num = 1000 Else num = numDoc.RecordNo(0) + 1 End If '---------------------------------------- ' Set the alpha-numeric value '---------------------------------------- doc.RecordNo = num doc.RecordID = "RFS-" & num & "-" & Year(Today) End If End Sub