Providing a common look and feel to all Service Desk Express forms

OK – so before anyone gets too excited and thinks that I (or BMC for that matter) have provided something like a template that all forms can inherit from (wouldn’t that be useful) – let me stop you there. I haven’t! However, as I got extremely bored of building the same forms and laying them all out slightly different, it became quickly apparent that some sort of template was needed. Apart from anything else, when others create forms on “my” systems it can quickly end up looking very disjointed/uncontrolled.

So, what follows is a template of numbers that will at least cause every form to have a similar look and feel and, although not perfect, is a fairly good match to the out of the box forms provided by BMC.

Control FG Colour BG Colour X Pos Width Y Pos Height
“Main Form” #FFFFFF
“Button Box” #DDDDDD 0 735 0 60
“Separator Line” #000000 0 735 20 0
“Open Button” 1 25 1 25
“Save Button” 36 25 1 25
“Copy Button” 51 25 1 25
“Clear Button” 76 25 1 25
“Delete Button” 101 25 1 25
“InActive Field” 663 20 5 20
“Previous Button” 663 Leave Blank 38 Leave Blank
“Next Button” 712 Leave Blank 38 Leave Blank
“Form Title Text” Dark Blue – Bold 7 100 33 20

Clearly, there is a whole bunch of other stuff that you could do as well – one thing I would also suggest is that you add the Sequence field next to the “Form Title Text” field as it makes integration/database queries much easier.

Anyway, hope this helps – as always comments (positive or negative) always welcome.

Self Service Authentication – Switching Between Authentication Options

Before Service Desk Express, when “Windows” authentication was not an option for the core product, the installer for Magic (whilst woeful in comparison to nowadays) used to have an option to have “Windows” authentication for Self-ServiceClient Services. Nowadays, the installer gives you the choice between “Windows” for the core product and for Self-Service or just the core product – ah, progress!

Anyway, for those organisations that want “Windows” for Self-Service but not for the core product, I thought I would post a quick “How-To:”

So, log onto the application server using, for example, remote desktop. Open IIS Manager by clicking on Start > All Programs > Administrative Tools > Internet Information Services (IIS) Manager. Assuming that you have installed the product in the Default Web Site, expand the Default Web Site, right-click on the Self-Service virtual directory (called HelpDesk by default) and select Properties for the popup menu that appears. Click on the Directory Security tab and click the button to edit Authentication and access control. Uncheck Enable Anonymous Access and check Integrated Windows authentication. Click OK to exit that window and OK to apply your changes. You will be presented with a screen that looks like the image below:

Inheritance Overrides

*** IMPORTANT ***

You MUST click the button to Select All and then press OK. You can close IIS Manager and log-off the application server.

One final thing to do – tell Service Desk Express that you want to use “Windows” authentication for Self-Service. To do this you need to run the following script against the SDE database:

UPDATE dbo.SMSYSFLAGS SET VALUE = '1' WHERE NAME = 'featUseIntegratedLoginSSHD'

And that is it. To reverse the process run the script above replacing the ‘1’ with ‘0’, check the Enable Anonymous Access and uncheck Integrated Windows authentication. Again, do NOT forget to click the Select All on the Inheritance Overrides window that appears.

Calculated field error in purchase request module

A little gotcha that I came across awhile ago is there is an error in the Total Cost calculated field in the Purchase Requests module that forgets to take into account if a Purchasing Item is active or not. As many organisations prefer to use the InActive flag as opposed to allowing users to delete records, this can lead to erroneous reporting. Thankfully, it is easily fixed…

Open DB Admin and expand the Purchase Requests module. Right-click on the Total Cost field and select Modify Field from the popup menu. Change the SQL from:

(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 1) * (1+(ISNULL(BASE."TAX_%",0)*0.01) ))+(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 0)+ISNULL(BASE."SHIPPING_COST",0))

to:

(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 1 and CHILD."_INACTIVE_:" = 0) * (1+(ISNULL(BASE."TAX_%",0)*0.01) ))+(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 0 and CHILD."_INACTIVE_:" = 0)+ISNULL(BASE."SHIPPING_COST",0))

Save the field after testing it and you are good to go. Happy reporting.

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