Record history, full audit, and future affected rows

I have often been asked about recording the history of a record and the best way of doing it. What I propose to cover in this post are some of the different techniques for tracking history and propose a potential additional benefit by introducing it.

So to begin with we will take a look at tracking updates to a single field in a single module. So by way of an example, let’s say that we wanted to track when someone updates the Description field of a change request such that one can see previous versions. To do this we can create a simple business rule:

Change Request – Track Changes -Change Description

Conditions

when an Change Request

Update occurs with

Expression 1	Comparison Operator	Expression 2	Logical Operator
{CUR,Change Description}	NotEqual	{TR,Change Description}	None

Actions

Action 1

Method	Module	Form
Create	Change Details	New Change Details

Field Name	Value
Change #	{TR,Sequence}
Note	The previous description was: {CUR,Change Description}
Date	{DATE} {TIME}
Login ID	{MAGICUSER}
Description	The description of the change has been updated
Action ID	NOTE

So now each time a user changes the Description field (i.e. comparing {CUR, Change Description} with {TR, Change Description}) an entry is written to the Change Details module of type NOTE.

This method will work for any of the modules that already have details tables e.g. Incidents, Work Orders, Problems, Changes, Inventory Items etc. Additionally, it is similar to the way that BMC does it with, for example, changes of subject/category. Other advantages include the fact that it is very simple to get your head around and very easy to implement – there is no requirement for any database changes and consequently this kind of record history can be implemented very quickly whilst the application is operational.

Ok, so what are the disadvantages. Well, this method will work for tracking of a single field or even maybe a few fields. The issue however, is that if you wanted to track say five fields then this gets really messy and, in my opinion, is wholly unsuitable. The reason is that you either need a single business rule with five “OR” conditions writing the CUR record and TR record of all five fields (which is incredibly inefficient) or you have five business rules each tracking a field (again horrible). Additionally, if you wanted to track changes to say Clients, you would need to create an additional module or two in the same way as the incident details. So what’s the alternative.

An alternative is to create a new module for tracking the updates of the module you want to track. The difference here is that the new module is basically a copy of the module you want to track with a couple of additional fields. By way of an example, we will decide that HR wants to be able to track a change made to any field in the client module. Using the first method I mentioned, this would be a nightmare.

What we need to do is use the DB Admin Tool to create a copy of the Clients module.

Open the DB Admin Tool and right-click on the Clients module and select Replication Module… from the popup menu. From the Replication Module popup, enter a table name e.g. TBL_CUS_UPDATES, a view name e.g. Client Updates, and a display name e.g. Client Updates and check the Copy Selected Fields checkbox. A list of fields in the client module will be displayed with, by default, all fields checked. You can leave it like that but personally I would only check the fields I wanted to track (remember it is easy to add additional fields later). For demonstration purposes I have only selected a limited number of fields. It is sensible to check the Copy Indexes checkbox but don’t check the Copy Data checkbox.

Important: Right-click on the Client ID field in the newly created Client Updates module and select modify. You need to remove the Unique constraint otherwise you will only be able to have one update per client!

As mentioned earlier we need to add a couple of additional fields (all these fields should be mandatory i.e. not nullable):

• Seq.Client – Foreign Key -> Clients Module. This is needed so that each update can be tracked against a given client and a given client can have many updates.
• Added By – String(30) Default = SUSER_SNAME. This is going to be a read-only field that will track which SDE user updated the record. The SUSER_SNAME bit picks up the logged on user’s database account name.
• Date Added – DateTime Default = Current Date and Time. A simple timestamp of when the update record was added.

In order to get the best out of this new module we also need to add all the fields from the client module as virtual fields in Seq.Client foreign key we added with a Current prefix. So for example we will add the virtual field Current Client ID in the Seq.Client foreign key referencing the Client ID field. This will allow us to have the current and the updated view of a field on the same form – cool eh? You should end up with something like what is shown below:

So now all we need to use this module is a business rule:

Client – Track Changes

Conditions

when an Clients

Update occurs with

Expression 1	Comparison Operator	Expression 2	Logical Operator

Actions

Action 1

Method	Module	Form
Create	Client Updates	New Client Updates

Field Name	Value
Client ID	{CUR,Client ID}
First Name	{CUR,First Name}
Middle Initial	{CUR,Middle Initial}
Last Name	{CUR,Last Name}
Phone	{CUR,Phone}
Ext.	{CUR,Ext.}
EMail Address	{CUR,EMail Address}
Seq.Client	{TR,Sequence}
Added By	{MAGICUSER}
Date Added	{DATE} {TIME}
Effective Date	{DATE} {TIME}

We can now track any change made to any field in the client module. What is more if you wanted to go back in time and see what the client record looked like back then, you simply select the Updates tab from the client record in question, double-click the record in question and you can compare then with now on the same form as shown in screen shots below:

Now that is cool – but it gets better. Let’s assume that a given female client is getting married in three weeks and, as frequently happens in organisations, submits a service desk ticket to ask that her login and email are changed appropriately for her new surname. Wouldn’t it be really cool if you could schedule that change to take effect and better still invoke a business process surrounding it – it’s easy simply by adding a single field and creating an additional business rule. First the field which needs to be added to the Client Updates module:

• Effective Date – DateTime Default = Current Date and Time.

We then need a scheduled business rule:

Client Updates – Update Client

Conditions

when an Client Updates

Update occurs with

Expression 1	Comparison Operator	Expression 2	Logical Operator
{TR,Effective Date}	GreaterThan	{TR,Date Added}	None

Actions

Action 1

Method	Module	Form
Update	Clients	New Clients

Field Name	Value
Sequence	{TR,Seq.Client}
Client ID	{TR,Client ID}
First Name	{TR,First Name}
Last Name	{TR,Last Name}
Phone	{TR,Phone}
Ext.	{TR,Ext.}
EMail Address	{TR,EMail Address}

Schedule

At a later timeSTARTING AT: {TR,Effective Date}

Now we can simply open the client form, find the relevant record, right-click in the Client Updates tab and create a new update to take effect in the future. Now we have a way of both tracking changes and applying changes in the future.

Minor word of caution – you need to go back and modify the Client – Track Changes business rule and add a condition where {MAGICUSER} NotEqual MAGIC (or whatever account your business rules run as). If you don’t do this then when an update is scheduled for the future, after it runs you will also get another client update for the update made by that update! Phew that was a mouthful.

Anyway, that was it…Hope proved helpful…as always, would welcome any comments.