Empowering the business to modify business rules

Service Desk Express ships with a comprehensive, if somewhat clunky, business rules manager that allows the system administrator(s) to view, create, update and delete business rules based on data changes in the Service Desk Express database. Whilst this tool is fit for purpose only system administrators and group administrators are able to use the tool due to permissions and furthermore, require training to do so. This is generally a good idea but there are occasions when you might want someone else in the business to be able to change a setting in a rule such that, for example, an email goes somewhere else.

In these scenarios the risk associated with making this change is minimal and allowing the business to make this change would appear sensible. That said, we don’t want to make everyone a system or group administrator. So what’s the solution.

System Parameters

What we need is a way of storing system parameters that can be used in business rules, calculated fields, emails etc. and we need our support staff to be able to modify those system parameters they own. We can do this by creating a new module in Service Desk Express using the DB Admin Tool.

Example:

Table Name – TBL_PARAMS; Display Name – System Parameters

Property	Field 1	Field 2	Field 3	Field 4
Alias Name	Param Name	Param Value	Seq.Owner	Owner ID
Display Name	Param Name	Param Value	Seq.Owner	Owner ID
Column Name	PARAM_NAME	PARAM_VALUE	SEQ_OWNER	CODE
Status	Real	Real	Foreign Key (Support Staff) Restrict	Virtual (Support Staff)
Primary Key	No	No	No	No
Nullable	No	Yes	No	No
Index	Yes	No	Yes	No
Unique	Yes	No	No	No
Data Entry Type	Text	Text	Numeric	Text Upper Case
Database Type	nvarchar	nvarchar	int	nvarchar
Length	255	255	4	30
Default	<none>	<none>	<none>	<none>

So let’s say that we want to create a business rule that sends an email to a bunch of people whenever an incident goes past its due date and time. The rule might look something like:

Incident – Due Date Exceeded
Conditions
when an Incident

Create
Update occurs with

Expression 1	Comparison Operator	Expression 2	Logical Operator
{DB,Incident,State:,”Incident #” = {TR,Incident #}}	Equals	{TR,Incident #}	None

Actions
Action 1

Method	Module	Form
Create	Notifications	Notifications

Field Name	Value
Subject	Incident #{TR,Incident #} has exceeded its due date and time
Body	Incident #{TR,Incident #} has exceeded its due date and time. Please do something
Type	SMTP Email
Address	jbloggs@company.com;fflintstone@company.com

The problem here is that when someone wants to add or remove someone from the mailing you have to get a group or system administrator to do so. What we need to do is modify the rule as follows:

Incident – Due Date Exceeded
Conditions
when an Incident

Create
Update occurs with

Expression 1	Comparison Operator	Expression 2	Logical Operator
{DB,Incident,State:,”Incident #” = {TR,Incident #}}	Equals	{TR,Incident #}	None

Actions
Action 1

Method	Module	Form
Create	Notifications	Notifications

Field Name	Value
Subject	Incident #{TR,Incident #} has exceeded its due date and time
Body	Incident #{TR,Incident #} has exceeded its due date and time. Please do something
Type	SMTP Email
Address	{DB,System Parameters,Param Value,”Param Name” = ‘Incident Email Distribution List’}

Additionally, we need to open our new System Parameters form and add a parameter called Incident Email Distribution List with a value of jbloggs@company.com;fflintstone@company.com as per the figure below:

The result of doing this is absolutely nothing! The rule does exactly what it used to. The difference here however, is that you don’t have to be a group or system administrator to update the parameter value. What I would then suggest is that you create a client side business rule that says:

System Parameters – Control Updates
Conditions
On Save in module System Parameters

Expression 1	Comparison Operator	Expression 2	Logical Operator
{TR,Owner ID}	NotEqual	{MAGICUSER}	None

Actions
Action 1

Method	Module	Form
Display Message	System Parameters	<Current Form>

Field Name	Value
Message Box Type	OK
OK (True) Button Text	OK
Exit Rule if OK clicked	True
Window close button (x) should function as	Exit Rule
Message	You can’t update a record you don’t own.

Now you have a situation where, as a system administrator, you can grant every support staff user select and update permissions to this module; insert permissions to, for example, EXTERNAL_SUPPORT (on the grounds that there is no point in inserting a system parameter without some use for it and only system/group administrators will be creation functionality based on these parameters), and delete permissions to no-one. Add a version of the form to their navigator bar that has the Parameter Name and Owner ID as display only and your good to go.

In the next post I will show another use for this system parameters table when it comes to calculated fields…

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.

Forward schedule of change (for Self-Service)

One of the first projects I ever did was a forward schedule of change (FSC) for self-service. The organisation I was working for wanted to be able to display the FSC from within self-service. As this functionality wasn’t supported by Service Desk Express, I created a web application that enabled it.

Since then I have had a number of queries about how it was done (as I posted the pictures of it in the MagicSolutions Yahoo Group) and thought that it would be fun to rewrite with a bit of a spruce up, a little more functionality and, very importantly, a simple installer. For anyone interested, you will be pleased to know it is complete.

As with all JOATIT projects, it is available free of charge (as long as you don’t try to sell the application or the code (or any part of it) to anyone else). Additionally, the source code is available for anyone who wants to customise/reuse.

It is available from the Projects zone and as always, feedback is very welcome.