Forms Expert - capitalising names and checking phone numbers

Introduction

The ability to embed VBscripts into detail forms in Commence is an important feature that is often overlooked and underutilised. VBscripts can perform such operations as setting default values, filling in fields, and navigating within the form, as well as performing validation checks before an item is saved.

VBScript code can be written to handle various events, such as the loading of a form, entering or leaving tabs, pressing the Save button, etc. While processing these events, VBScript code can read and write field values, move the cursor to different fields and/or tabs, display messages, abort a requested save, etc.

One benefit of this feature is faster data input, since scripts are able to fill in field values based on other field values, or even skip over some fields. Better data validation is achieved since the data is able to be checked prior to saving an item.

An additional benefit for users in a workgroup environment is the significant reduction of workgroup traffic. Since values set by VB Script code are in place prior to the Add or Edit operation, only a single transaction needs to be distributed over the network. Similar operations can sometimes be performed using Agents however agents can only operate after an item has been saved. This generates additional Edit transactions which must then also be distributed over the network. More importantly from the user's perspective, Agents tend to be slower, whereas the VBscripts execute very quickly, resulting is less time waiting.

We will examine two simple data validation operations that illustrate these concepts. When entering data such as company names or client names, sometimes we make a typo and forget to capitalise the first letter. This can then look unprofessional in letters, faxes and emails that are sent later. When entering phone numbers, we can do some basic checking, to at least ensure there are the correct number of digits. We can then present the phone number, with digits spaced in a manner that is easy to read.

Capitalising the first letter of names

The fragment of code to achieve this has been encapsulated into a function for portability:

'--- Find each space in the field contents and ensure 
'---the next char is upper case
'--- Writes the modified string directly to the field
'--- tf 16/6/00
Function CapitalizeFirstLetter(ByVal FldNam)
   Dim lngLen, thisChar, lastChar, strNewVal, strOldVal, i
   strOldVal = Form.Field(FldNam).Value
   lastChar = " " 'initialise to Ucase first char
   strNewVal = "" 'just to be sure

   For i = 1 to Len(strOldVal)
      thisChar = Mid(strOldVal,i,1)
      if lastChar = " " Then
         thisChar = Ucase(thisChar)
      End if
      strNewVal = strNewVal & thisChar
      lastChar = thisChar
   Next
   Form.Field(FldNam).Value = strNewVal
End Function 'CapitalizeFirstLetter

This function is then called from whatever fields you would like to have this formatting applied to.

Sub Form_OnLeaveField (ByVal FldNam)
   If FldNam = "Name" Then CapitalizeFirstLetter(FldNam)  : Exit Sub
End Sub 'OnLeave 

So when the user leaves the field on the form (either with the cursor, tab or save), the CapitalizeFirstLetter function is called and passed the field name. CapitalizeFirstLetter get the existing value in the field then goes through character by character checking for a blank. The assumption is that the next character following a blank should always be capitalised - this is not always the case, but it is what you want 99% of the time. The character is then added to the new string and the loop moves forward. Finally the correctly capitalised string is written out to the field.

Checking phone numbers

The function is as follows

'--- Check 10 digits, and format
Function FormatTelephone(ByVal FldNam)
   Dim strNumber, strNumberLength, strOneChar, strTwoChar
   strNumber = Form.Field(FldNam).Value
   '--- Normalise to remove blanks in telephone number
   strNumber = replace(strNumber, " ", "")
   strNumber = replace(strNumber, "-", "")
   strNumber = replace(strNumber, "(", "")
   strNumber = replace(strNumber, ")", "")
   '--- check number is 10 digit, allow any no starting 
   '--- with +, 13 or 1 800
   strOneChar = Left(strNumber, 1)
   strTwoChar = Left(strNumber, 2)
   If ( (strOneChar = "+") OR (strTwoChar = "13") OR _
         (strTwoChar = "18" ) ) Then 
      Exit Function
   End If 'strOneChar = "+"
   strNumberLength = len(strNumber)
   If NOT( (strNumberLength = 0) OR (strNumberLength = 10) ) Then
      Abort()
      Msgbox "A phone or fax number must contain 10 digits", 64, "Commence"
   Else
      strNumber = Left(strNumber, 2) & " " & Mid(strNumber, 3, 4) & _
         " " & Mid(strNumber, 7, 4)
      Form.Field(FldNam).Value = strNumber
   End If 'strNumberLength = 0
End Function 'FormatTelephone

As for capitalisation, the FormatTelephone function is called from Form_OnLeaveField() for the required fields. FormatTelephone first gets the value from the field and strips unwanted characters like spaces and brackets. Then we check the first one or two characters to see if it may be a special phone number (eg 13..., overseas or 1800). If this is the case we exit the function and no checking or formatting is performed. For a standard telephone number we simply count up the number of digits and then alert the user if it is not a standard 10 digit number. Finally we format the number in an easy to read fashion - nn nnnn nnnn - and output back to the form.

A useful enhancement would be to better handle the various forms of 13..., 1800..., 1900... 0055... numbers that are available today. It would also be helpful to lookup the area code (ie 02, 03, 08..) and validate against the state.

We hope these fragments of VBcode may be helpful, and provide inspiration to automate the data checking you need to perform. Please note that we take all care with the examples provided, but they are provided as is and unsupported. We offer consulting services to provide additional development to suit your requirements. Advanced User Systems makes no warranty as to the Product,  customised version of the Product, or fragments of code, including any warranty as to its performance, accuracy, or freedom from error, or as to any results generated through its use, including without limitation, any implied warranties or fitness for a particular purpose.