Forms Expert - Logging a History of Changes

Have you ever wanted to be able to check back to verify when a change was made to you data and exactly what the change was? Maybe you need to see an old phone number, or find out who altered a price. With a little scripting and a new field in a form, its easy to log the history of changes in commence.

Category Modification

You can log changes in any category, and you can decide exactly what you want to record. In this example, we assume you want to track the date, time, user, original data and new data.

So the first task is to add a new field "History" to your category. As the amount of text to go into this field will be variable, you should make this field type large text.

VBscripting

Next you need to write some VBscript to capture the changes and write to the History field. If you are not already familiar with writing VBscript in Commence detail forms, you might want to review the basics in the Commence Users Guide, in Chapter 12, VBscripting.

To build a log of changes, we are going to use Form_OnEnterField and Form_OnLeaveField events. Here is the code to capture the changes:

Dim enterField, leaveField
Sub Form_OnEnterField(ByVal FldNme)
   '--- Used for history
   enterField = Form.Field(FldNme).Value
End Sub

Sub Form_OnLeaveField (ByVal FldNam)
   '--- Used for history
   Dim history, UserName
   UserName = GetMe("Name")
   leaveField = Form.Field(FldNam).Value
End Sub

Note that enterField and leaveField are defined as global variables, since these have to be accessed from both Sub Form_OnEnterField and Sub Form_OnLeaveField.

Also notice that we have not had to mention any field names at all! We are simply using the field name passed to the Sub - very convenient. But, have you spotted the problem? This works well for all the fields in the category, but does not work for connection fields - more on that later.

Also, GetMe() is a simple utility function that returns the user name defined under preferences.

So now we know the original value and the new value, all we have to do is compare and then update the history. Of course if we have made a change we will have to leave the field, so all this is done in Sub Form_OnLeaveField:

Sub Form_OnLeaveField (ByVal FldNam)
   '--- Used for history
   Dim history, UserName, crlf
   crlf = Chr(13) & Chr(10)
   UserName = GetMe("Name")
   leaveField = Form.Field(FldNam).Value

   If enterField <> leaveField Then
      If IsAdd <> TRUE Then
         'MsgBox("Enter Field = " & enterField & _
            ", Leave Field = " & leaveField)
         history = Form.Field("History").Value
         history = Date & " " & Time & ", " & _
            UserName & ", " & FldNam & ", " & _
            "Original Value: " & enterField & _
            ", New Value: " & leaveField & _
            crlf & history
         Form.Field("History").Value = history
      End If 'IsAdd
   End If
End Sub

If the original value is the same as the new value, then no changes were made and we are done. Likewise, if we are adding a new item, we are probably not interested in logging changes (every entry would be a change).

If we are editing and have made a change, we firstly capture the existing history. Then we add the details we want to track. Finally write the new history out to the history field.

And for completeness, here is the GetMe() function, which uses some simple api calls to get the user name:

Function GetMe(ByVal FieldName)
   '--- Function to return current user details
   '--- Commence Corporation
   Dim lDB, lCursor, lQRowSet, lIndex, lResult
   Set lDB = Application.Database
   Set lCursor = lDB.GetCursor(0, "user", 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

Tracking Changes to Connections

As mentioned above, this technique does not work for connections. This is because the syntax to get the data from a connection is different; in fact we need to specify the name of the connection and the connected field we are interested in. This is easily handled by explicitly checking each connection field.

So lets assume we wanted to track any changes in the name of the company a person works for. In Form_OnEnterField we just add the lines:

If FldNme = "is employed by Company" Then
   enterField = Form.Connection("is employed by", _
      "Company").Fieldvalue("Name")
End If

And in Form_OnLeaveField

If FldNam = "is employed by Company" Then
   leaveField = Form.Connection("is employed by", _
      "Company").Fieldvalue("Name")
End If 

This does assume there is only one connection. Multiple connections can be handled, but it requires more code and is getting a little complex for this discussion. Contact our consulting services if you need help with this...

Protecting the history

In a corporate situation, you might want to ensure that once the history is logged, staff can't go in and later make changes. This is easily achieved with the CantGoHere utility function.

Add the following to Sub Form_OnEnterField

If FieldName = "History" Then CantGoHere : Exit Sub

Then add the CantGoHere function.

Function CantGoHere
   '--- Check modification details are not changed.
   Dim mb
   mb=MsgBox("Warning: You cannot edit this field. _
      This data is automatically generated by Commence.", _
      0, "Warning")
   enterField = Form.Field("Name").Value 
   'Force setting as it won't be triggered
   Form.MoveToField("Name")
End Function 'CantGoHere

So with a few lines of code, we now have a mechanism to log and track changes. This is ideal for providing an audit trail and to help manage the integrity of your customer information.