Forms Expert - Data Validation

Do you have customer data that is obviously incorrect? Wouldn't you like to have obvious errors picked automatically at the time of entering the data?

With Commence scripting in detail forms, there is no end to the data checking and validation that you can perform as data is being entered. Here are just a few simple examples.

Using VBscript in Commence Detail Forms

Using VBscript in Commence is covered in Chapter 12 of the Commence User Guide, and you should read through that chapter if you are not familiar with how to create, edit and load scripts.

By the way, if you are serious about VBscripting in Commence, ScriptEZ-RM is a third party tool designed to automate the process, which provides a graphical user interface for easy script editing. Please contact us if you would like more information.

Date Ranges

This is a simple (almost trivial) example to illustrate basic checking. Consider a maintenance contract with a field for start date and end date. Obviously the end date should be after the start date, so we need some code to perform this check, to avoid a simple data entry error.

Sub Form_OnLeaveField(ByVal FldNam)
   If FldNam = "Warranty Start" Then CheckDates : Exit Sub
   If FldNam = "Warranty End" Then CheckDates : Exit Sub
End Sub

Sub CheckDates
   'First check we have a date entered in both fields
   If Field("Warranty Start").Value = "" Or _
      Field("Warranty End").Value = "" Then Exit Sub
   'Now verify the dates make sense
   If DateDiff("d", Field("Warranty Start").Value, _
      Field("Warranty End").Value) < 0 Then
      MsgBox "Warning: End Date is before Start Date.", _
         vbExclamation, "Check Dates"
   End If
End Sub

When we leave either date field, the CheckDates sub is called. It firstly checks there is data in both fields; there is no point checking if only one field is populated. Then we work out the difference between the two dates: the number of days in not important - only whether the result is positive or negative. A negative results indicates the start date is after the end date, and we put up a friendly warning box.

In this case, CheckDates only raises a warning and work can continue. In some cases this may be an error, and it could be more appropriate to force the user to fix the problem before they can proceed.

As an aside, it may be friendlier to just enter the start date and and then the number of years for the contract. The script can then automatically determine the correct end date and write that into the field.

Part Number

Lets assume you are entering a part number which would normally have all numeric characters. The system should automatically indicate if an alphabetic character is include, as this would be an error.

Sub CheckPartNumber
   'First check we have data
   If Field("Part Number").Value = "" Then Exit Sub
   If Not IsNumeric(Field("Part Number").Value)Then
      MsgBox "Error: The Part Number must be numeric.",vbCritical, "Invalid Part Number"
      MoveToField("Part Number")
   End If
End Sub

In this case, we assume a correct Part Number must be entered, so we do not allow the user to leave the field until it is correct.

Of course, the field may be defined to Commence as a number, so Commence will automatically check for the correct data when the form is saved. But checking via script has the advantage of flagging the problem right after the user has entered that piece of data.

An extension of this is that the Part Number may not be so simple - it may be in the form of nnnn-nnnnn, ie 1012-00407. In this case Commence does not have a predefined data type to suit this (as it is specific to this application!) so we need to write a little code to check the form of the Part Number is correct:

Sub CheckPartNumber
   'First check we have data
   If Field("Part Number").Value = "" Then Exit Sub
   If Not (Len( Field("Part Number").Value) =10 and _
      IsNumeric(Left(Field("Part Number").Value,4) ) and _
     IsNumeric(Right(Field("Part Number").Value,5) ) and _
      InStr( Field("Part Number").Value, "-") =5 )Then
         MsgBox "Error: The Part Number must be in the form nnnn-nnnnn." & _
            vbCrlf & "where n is a numeric character.",vbCritical, "Invalid Part Number"
         MoveToField("Part Number")
   End If
End Sub

Our checking is getting a little more complex. When the part number is correct, there should be 10 characters, so this is what we check first. Then the first four characters should be numeric and the last five should be numeric. Finally a "-" should be the fifth character. If any of these conditions are not met, we flag the error and ask the user to correct.

Duplicate Data

This is perhaps one of the most common areas of data entry errors. It is also one of the most difficult to manage. If two data items are entered with a slight variation in the name, it may be obvious to us that it is a duplicate, but a computer cannot determine that, because there is a difference....

So really the only duplicates we can guard against is exact duplicates. We always recommend that you unselect "Allow Duplicates" in your key categories, and Commence will not let you save a form if that item already exists. But that can be annoying if you have just spent a few minutes entering the rest of the data in the form. It would be much better to flag the problem as you leave the Name field - here is a generic function to achieve that.

Function AUSLookupName(byval sField)
   'Generic function to check if the name has already been used.
   'Useful in categories where duplicates are not allowed.
   Dim newcontactname, rc, contact, cmcDB
   Dim x
   AUSLookupName = -1
   Set cmcDB = CreateObject("Commence.DB")
   newcontactname = Field(sField ).Value
   Set contact = cmcDB.GetCursor(0, CategoryName, 0)
   x = contact.SetFilter("[ViewFilter(1,F,,""" & sField & """,equal to,""" & newcontactname & """,)]",0)
   If x <> True Then MsgBox("AUSLookupName (Error:1)") : Exit Function
   rc = contact.RowCount()
   If rc = 1 Then
      MsgBox("Warning - Name exists: " & newcontactname & crlf & _
         "You may not be able to save with this name." )
      AUSLookupName = 1
   Else
      AUSLookupName = 0
   End If
End Function 'AUSLookupName

In this example, we are combining the use of the Commence API. Commence VBscripting enables us to read an manipulate data in the current item open in the form. But to access all the data in the database, you need to use the API.

We pass the field name to the function, then open a cursor on the database. We can get the value of the data in that field, then create the filter against that field for a direct match. If we find a match, we put up an appropriate warning box.

These simple examples give you a taste of what you can do with scripting in Commence. You can come up with a very sophisticated solution, to check your data input in a variety of ways, to minimise obvious data errors.