Forms Expert - Generating a Unique Identifier

Very often in a form you need to track the data as an important physical document or "virtual document" in your business. Examples are quotes, jobs, incidents or RMAs.

Generating a Unique Identifier can be tackled many ways; we prefer to use a combination of VBscript and the API.

The following function can be used to generate a unique identifier, in this case for an item in a category called Incident:

Function GetJobNumber

If Form.Field("Job Number [Name]").Value <> "Auto Generate" Then Exit Function

Dim intNumLen
intNumLen = 5 'fill with leading 0's
Dim objDB, Jobs, qrs, strHighNum, strNextNum, x, Initials
Dim ars, y, z

'--- Find my initials
Initials = GetMe("Initials")

set objDB = CreateObject("Commence.DB")
set Jobs = objDB.GetCursor(0,"Incident",0)

Jobs.SetSort "[ViewSort(Job Number [Name], descending)]",0
Jobs.SetColumn 0,"Job Number [Name]", 0
set qrs = Jobs.GetQueryRowSet(1,0)
strHighNum = qrs.GetRowValue(0,0,0)
strHighNum = Left(strHighNum,8) 'Throw away the initials
strHighNum = Right(strHighNum,5) 'Throw away CIN
'MsgBox(strHighNum)
strNextNum = Cstr( Cint(strHighNum) + 1)
strNextNum = ( String(intNumLen-Len(strNextNum), "0") & strNextNum)
'MsgBox("Result = " & strNextNum)
Form.Field("Job Number [Name]").Value = "CIN" & strNextNum & Initials

End Function 'GetJobNumber

So what's happening here? To begin with, find out if the field contains "Auto Generate" - our default value. This indicates it is a new item. If this is not the value of the field, exit the function as the number has been generated previously.

Next we use the GetMe() function to find the user's initials - more about this later. Then we use the api to open a cursor on our Incident category, and sort the items in descending order. This will place the highest number at the top of the list, so we only have to open a QueryRowSet on the first item.

By finding the value of the "Job Number [Name]" field we can then do some simple text processing to strip the prefix and suffix and come up with the raw number. By casting this to an Integer, adding 1 then casting back to a String, we get the next available sequence number.

You can uncomment the MsgBox() calls to see the process in action.

In this example, we also add a prefix (CIN) that makes it clear this is an Incident tracking number. So we have now generated the next number in sequence - quickly and easily!

But the story is not quite complete. What happens if simultaneously another user also obtains the next number in sequence --- before this one has been synced and even saved? We have the same number twice...

So this is easily fixed by appending the user's initials, which is the final statement in the code above. And of course, we need to ensure those initials are unique.

You will have noticed a GetMe() function above; this returns the value of the specified field, as set be the (-Me-) preference under Personal Info. GetMe() can be implemented as follows:

Function GetMe(ByVal FieldName)

Dim lDB, lCursor, lQRowSet, lIndex, lResult
Set lDB = Application.Database
Set lCursor = lDB.GetCursor(0, "Employee", 0)
lResult = lCursor.SetFilter("[ViewFilter(1, F, , Name, Equal to, ""(-Me-)"")]", 0)
Set lQRowSet = lCursor.GetQueryRowSet(1, 0)
lIndex = lQRowSet.GetColumnIndex(FieldName, 0)
GetMe = lQRowSet.GetRowValue(0, lIndex, 0)

End Function 'GetMe

We pass the required field name to GetMe()and get back the user's initials. It is then a simple matter to append this to the number.