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

• calculated fields
• Service Desk Express