Client Configurations popup in Self-Service

Client Configurations is Magic/SDE’s way of assigning clients to configurations (usually used as locations or personal configurations) and consequently to assets (inventory items). Whilst there is clearly a benefit to the client of the service desk knowing what assets the client has and where they are located, when logging a call via Self-Service the vast majority of clients just want to give the minimum information possible.

That said most clients understand the necessity of completing certain information that may assist the service desk in fixing their call as long as they understand what they are being asked for. Select from Client Configurations doesn’t exactly make much sense to the average client. Furthermore, this popup cannot be customised and consequently appears to be an annoying popup with no purpose to the average client.

Awhile ago I posted a hack in the Yahoo MagicSolutions group that prevented a user from getting the Select from Client Configurations popup in Self Service when they had more than one configuration assigned to them. As specific posts can be a bit difficult to find in the Yahoo group I thought it might be beneficial to post the hack here:

Open CommonScript.js in notepad (by default this file lives in C:Program FilesRemedyMagic Service DeskSelf Service DeskScripts. Scroll down until you find the function PopulateDependants(VColName) which starts:

function PopulateDependants(VColName)
{
bDataChanged = true

Add the following line of code above the bDataChanged = true

if (VColName=="Client Config") return

so it ends up looking like…

function PopulateDependants(VColName)
{
if (VColName=="Client Config") return
bDataChanged = true

… and that should be you sorted for both Incidents and Work Order (Service Requests).

Using UDF’s in Business Rules

In my last post I created a User-Defined Function (UDF) to determine if a given datetime fell within or outside working hours (as defined by a given work schedule). This post will discuss how to use a function such as this in business rules.

Ok so lets say you want to check in a business rule (doesn’t matter whether normal or client side) if the current time is within the work schedule. For the sake of argument we will assume you have a work schedule called NORMAL.

All you need to do is add the following Condition:

{MATH, (SELECT "_SMDBA_"."IsWorkTime"('NORMAL', GETDATE()))} EQUAL TO 1

Consequently the business rule you are creating will only fire if the current time falls within the selected work schedule.

Hope this helps.

Function to check if datetime is in work time

A couple of posts recently in the Yahoo MagicSolutions forum made me realise that there was no easy way of checking if a given datetime was during or outside working hours (as defined by the work schedule). Consequently I set about writing a UDF that would accept a work schedule name and datetime as an input and return 1 if the time fell within the given work schedule and 0 if it did not.

Because of the way things get formatted using WordPress I have saved the function as a text document available here: IsWorkTime Function

Depending on where you want to use UDF’s in Service Desk Express you need certain permissions to do so. Personally, whenever I create a function like this (assuming it is not returning any privaleged information) I grant execute permissions to everyone. To do this simply run the following SQL:

GRANT EXECUTE ON [_SMDBA_].[IsWorkTime] TO Public

In my next post I’ll discuss how to use UDF’s in business rules…

Showing images in Service Desk Express

A colleague of mine recently asked me to take a look at a request a customer had that involved wanting to be able to view an image of the client on the Incident screen when the Client ID was selected.

My immediate suggestion was to create a calculated field in the clients module that was a concatenation of a URL and the Client ID field and then make that calculated field available in the Incident module via the Clients foreign key and use the Display Link functionality to view the image. The advantage of this scenario is that a) it is completely supported by BMC and b) the image is only downloaded when the service desk agent wants to see the client’s mugshot.

Unfortunately, this was not what the client wanted. They wanted the image to actually show on the incident screen. This is how I managed it…

Using the customisation wizard I added a standard image to Incident form, saved the form and then viewed it in Service Desk Express (SDE). I then viewed the source code produced by SDE for this form and noticed that it gave my new image the ID of IMAGE2. I needed a directory where I could save the clients images so I created a subdirectory of the Images folder called Clients and saved each client’s image as a jpg using their Client ID such that mine would be ABARBER.jpg. What I now needed was a couple of simple Javascript functions that would swap my image with the client’s image:

function ImageSwap(sClientID)
{
var clientImage = document.getElementById('IMAGE2');
if (clientImage)
{
clientImage.onerror = ImageSwapError;
clientImage.src="images/Clients/" + sClientID + ".jpg";
}
}

function ImageSwapError()
{
this.src="images/NoImage.gif";
}

All the ImageSwap function does is check if IMAGE2 exists on the form (as you may have a form where it does not exist) and swaps the image appropriately. If the client’s image does not exist in the directory then it will throw an error and hence call the ImageSwapError function that just replaces the image with simple NoImage.gif. Notice that the NoImage.gif is to be located in the images folder NOT the images/Clients folder. This allows you to use that image on the form by default.

That was the easy bit. Now I needed to know where to put this function and also how to call it to make it do something. The answer came from the commonscript.js file located (by default) in D:Program FilesBMCApplication ServerScripts. I added my little functions just before another function that I was going to need – function FieldTabOut(). Now if you scroll all the way to the bottom of this function FieldTabOut(), after the …

//END - #Bug 30044

… line but before the closing }, I added my call to my ImageSwap function:

if(CommonModuleObj.ViewName == "24") ImageSwap(document.all("36").value)

Ok, so what does that do. Well basically it says, if the form is an incident form (that’s the ViewName == “24” bit) call my ImageSwap function passing the value of the Client ID textbox (that’s the document.all(“36”).value bit). Perfect, or so I thought. I saved the commonscript.js file and gave my server an IISRESET just in case and tested my “amazing” solution. It worked – sort of…

The problem was that it only worked when I tabbed out, not when I clicked on the popup button to find the client. Another hunt around commonscript.js provided my answer. In the top third of the file there is a function called function OpenPopup(). Once again I scrolled right to the bottom of this function, after the …

//38223 - End
}
strAddWhereClause = ""

…line and before the closing }, I added my call to my ImageSwap function again:

if(CommonModuleObj.ViewName == "24") ImageSwap(document.all("36").value)

I saved the file and gave my server another unnecessary IISRESET and it worked. I guess it goes without saying that this is a completely unsupported hack but it did achieve the desired effect.

Calculated fields – Intro

Calculated fields are fields that appear in a SQL or Oracle view but that are not real fields in the underlying tables. Some examples of calculated fields include:

• Purchasing Items – Amount: A calculation of quantity ordered x unit price
• Support Staff – Full Name: A concatenation of the support staff’s first name and last name

I remember when I first started working with calculated fields in Magic, it was an experience I would choose to avoid at all costs. The reason was nothing to do with the application but my level of understanding of them.

To get the most out of calculated fields you have to have a good understanding of Transact-SQL (T-SQL) or at least have someone on hand who does. This article does not attempt to help you with T-SQL, but will aim to:

1. Bridge the gap between pure T-SQL (e.g. using Query Analyser or SQL Server Management Studio 2005) and Magic/Service Desk Express (SDE) calculated fields.
2. Provide a series of useful (well at least I think so) examples of calculated fields

Ok, so without further ado, let’s discuss the gap between pure T-SQL and SDE calculated fields.

For the purposes of the post let’s choose an example to work with. Let’s say we want a way to be able to see, from the incident screen, the total amount of all active purchase requests relating to that incident. If you wanted to write a query in T-SQL to retrieve this information it would look something like:

SELECT SUM("Total Cost") AS "PR Total" FROM "_SMDBA_"."Purchase Requests" WHERE "HD#" = x

where x is the incident number in question. That’s great for getting a single value back, but we want to do is to make it dependent on whatever incident we open. The key lies in converting it from a plain select statement into a statement that SDE can convert into a field in the view.

The first thing we do is remove the AS “PR Total” from statement as SDE will add that bit for us when save the new calculated field called “PR Total”. So now we are left with:

SELECT SUM("Total Cost") FROM "_SMDBA_"."Purchase Requests" WHERE "HD#" = x

Now the issue is that because this field is being created in the incident module, the field HD# does not exist! So what you need to do is tell SDE where to find HD#. This is achieved simply by prefixing it with the view where it can find it.

SELECT SUM("Total Cost") FROM "_SMDBA_"."Purchase Requests" WHERE "_SMDBA_"."Purchase Requests"."HD#" = x

We are almost there. All we now need to do is tell SDE how to get the value for x automatically depending on which incident you open. The key lies in the keyword “BASE”. “BASE” tells SDE to look in the table it is currently in. This is important – it is the table and NOT the view, so consequently, as we are looking for the incident number, it is the SEQUENCE column we are after NOT the “Incident #”. So combining this with “BASE” keyword we end up with:

(SELECT SUM("Total Cost") FROM "_SMDBA_"."Purchase Requests" WHERE "_SMDBA_"."Purchase Requests"."HD#" = "BASE"."SEQUENCE")

And that is it. Click the Test button and, in this particular case, because we a looking for an amount we select a type of Double and click Save. Drop the field on the incident for using Form Customisation and you are there.

A well-respected consultant, David Sullivan from Nashco Solutions, made a very valid point about calculated fields that should be shared. Be very careful about adding calculated fields to popups because, as the popup by definition returns multiple rows, the calculations can cause the application to come to a grinding halt! This can usually be resolved by placing indexes on the fields that are used in the calculated field. Thanks David for that tip. 

I hope this has been useful. Below is a link to a resources page with a series of standard calculated fields that I think are useful which, all being well, you can just cut and paste from this blog into SDE.

Link: Useful calculated fields

Welcome…

Welcome to JOATIT (Jack of all things IT)…

I am an IT professional working in the service sector since 1999. I am fascinated by technology and have developed skills in a huge range of technologies ranging in level from master to dabling depending on the technology.

This is my site, my experiences. I am a Jack of all things IT – whether I am a master of any skill will be for you, my guests, to decide. Thank you once again for your visit.