Forms Expert - Testing If a Name Already Exists

How often have you added a contact, only to find the name already exists? Wouldn't it be sensible to check if the contact already exists, at the time of entering the name in the contact field, rather than when you try to save the form? 

If you don't find out that the contact  already exists (or for that matter, any item you are adding), you will type in all their details only to find that you are not able to save the detail form - this is frustrating for the user and a big time waster. Worse, if the category does allow duplicates you may inadvertently introduce duplicate data.

With a little bit of scripting, its easy to have this checked automatically as data is being entered, and alert the user if necessary. We have built a function (in fact a group of functions) to achieve this, and this technique is used in many places throughout the systems we set up.

AUSLookupName Function

The code in the function is as follows:

     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
          If IsAdd <> True Then Exit Function
          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

 Typically you would call this when you leave the Name field. You can optionally examine the return value for further processing. 

A sample of the way the function is used is as follows:

     Sub Form_OnLeaveField(ByVal FldNam)
          If FldNam = "Name" Then AUSLookupName(FldNam)
     End Sub

The function works as follows:

• Initialise the return value to -1, which would indicate an error

• If the form is being edited, we know the contact exists and we are not interested in doing the lookup on the name - so exit

• Create the Commence.DB object

• Find the name to search for, by getting the data from the field in the detail form, using the field name that was passed to the function

• Create a cursor on the current category - CategoryName is the current category. This is not always what you want, as we will see below, in a variation to this function.

• Set a filter on the field name passed to the function, using the name to search for.

• If the RowCount is not zero we have a match, and can display a helpful messagebox.

Using this technique helps prevent duplication of data, and makes the user's experience with Commence more productive and enjoyable.

AUSLookupNameinCat

For various reasons, you may not want to do the lookup in the category you are currently in. For example, if you have a category that combines data from several other categories for initial data entry, then you want to do the lookup in a different category and AUSLookup() will not work.

The following function solves this problem.

     Function AUSLookupNameinCat(byval sCat, sField, sLookup)
          ' sCat - Category to search in
          ' sField - Field name to search in sCat
          ' sLookup - string to search for
          'Generic function to check if the name has already been used.
          Dim contact, cmcDB
          Dim x
          AUSLookupNameinCat = -1
          Set cmcDB = CreateObject("Commence.DB")
          Set contact = cmcDB.GetCursor(0, sCat, 0)
          x = contact.SetFilter("[ViewFilter(1,F,,""" & sField & _
              """,equal to,""" & sLookup & """,)]",0)
          If x <> True Then MsgBox("AUSLookupNameinCat (Error:1)") : Exit Function
          AUSLookupNameinCat = contact.RowCount()
     End Function 'AUSLookupNameinCat

This works in much the same way as AUSLookup() except that the category is also passed as a parameter to the function.

In order to provide maximum flexibility, no assumption is made about the data to be looked up, and this is passed explicitly to the function. Also, the function does not make any assumption about warning the user; that is left to the programmer to take care of from the calling code. 

The return value is the number of rows matching, so any value other than 0 indicates the item already exists.

We also have other pre-configured functions in this family, to address other common tasks. AUSLookupContact() not only warns that the contact exists, but also looks up the company and company address, to help the user make an informed decision as to whether they in fact are entering the same contact again.