3 + 1 methods to mandate user input

Frequently we need to be able to mandate that a field is completed before a record can be saved. In this post, as the title perhaps eludes to, we are going to discuss essentially three methods of mandating user input (plus a variation on the third). We will look at the pros and cons where applicable and give examples of how to implement these methods. Hope you enjoy.

“Belt and Braces” – “Not Null”

So the first method we are going to look at is what I call the belt and braces method – using the database administration tool we can specify that the field cannot contain nulls as shown below:

Not null field

As you might expect with any belt and braces method this method has some serious pros and cons. On the pro side, this is the only method that also works if data is being added/updated in the database using an external method e.g. the Integration Engine or SQL Scripts. On the downside, this is about as inflexible as you can get. If you decide that actually there could a circumstance where it could be null then you need to take down the system and remove the null constraint using db admin. The other issue with using this method the error message returned is not really customisable and tends to be a bit, how does one say, unfriendly.

Error message if Not Null field is not placed on form

 

Error message if Not Null field IS placed on form

Where this method really scores is, for example, in many to many relationship tables and/or foreign key columns where the child records must have a parent e.g. Incident Details Incident # field etc.

“The Old Way” – “Required If On Form”

Back in the days of Magic 7.5 CSBRs didn’t exist and so if you didn’t want to force the column not to accept nulls then the only other choice you had was, again using db admin, to specify that a field was “Required if on form” as shown below:

Required if on form field

Here the advantage is when you add said field to a form, the application will automatically require that it is filled in. If it is not added then it won’t cause you any issues. It doesn’t enforce database integrity in the same way as the not null above which gives you a bit more flexibility but has its drawbacks as well. The big disadvantage is precisely what it says on the tin – if it is on the form then it is required. So you can’t have two forms, both with the field on it, but on one form the field is required and on the other it isn’t. Personally, I don’t make use of this method anymore as I think it has been superseeded by the flexibility of CSBRs.

“The New Way” – “Client Side Business Rules”

Since Magic 7.52 we have had a new toy to play with – the Client-Side Business Rule (CSBR). The CSBR essentially allows the Administrator a GUI to build JavaScript functions that can do a whole bunch of functions. One of the functions it can achieve is that of mandating user input. Below is a simple example of a CSBR to mandate that an incident cannot be saved if the Incident Description field is not populated:

  

The massive advantage you get with CSBRs is flexibility. CSBRs can be added (or removed for that matter) without any need to take down the system; can be applied to certain forms and not others allowing the mandating of user input on one form but allowing nulls on another; allow Administrators to create friendly (or not so friendly) error messages; and allow for complex rules such that if field (a) and field (b) are blank popup a message. The disadvantages are largely that a) you actually have to build CSBRs to do this functionality and b) adding too many CSBRs to forms can cause issues of CSBRs not firing or producing unexpected results.

A slightly different approach

Personally, I use a combination of Not Null and CSBRs. However, if you were to follow the approach I detailed above for the use of CSBRs you could end up with a massive number of CSBRs for a single form (if you had a lot of mandatory fields). As such, I make use of a slightly different approach where I create a single CSBR called Mandatory Fields in each module as follows:

  

The key to this approach lies in how you design the form. I try, within the confines of the application, to make use of standard usability techniques – namely the use of asterisks to mark mandatory fields as shown below:

Usability Incident Form

Summary

So all techniques have their place – their advantages and disadvantages which I hope I have given you a feel of. As always, your feedback (positive and negative) is always welcome.

Theme Woes!

Recently, I have been experimenting with other themes that will provide a more usable site for the displaying of my technical type posts. However, after receiving feedback I realised that I had rendered the site almost unusable in IE6 which was obviously unacceptable. Please accept my apologies for the unstable nature of the appearance of the site. This WILL be resolved very shortly.

Service Desk Express API – Part 1

The more I play with the Integration Engine that ships with Service Desk Express, the more excited I get about the possibilities of using it. By far the most exciting piece of functionality in it for me is the XML Initiator – essentially a method of accepting inbound pre formatted XML messages and inserting and/or updating resultant records – more on this later.

What the Integration Engine doesn’t provide you with however, is a method of selecting data. This means that, if you wanted to build an external application that interfaced with Service Desk Express, even if you made use of the Integration Engine to insert/update records, you would still need to access the database directly to view records.

What I propose to demonstrate in a series of posts is a method of creating an “adapter” or API for the Service Desk Express Integration Engine that allows developers in your organisation to interface with Service Desk Express through a single “portal”. That “portal” will be a web service that provides the building blocks to select insert or update any record (or group of records) without a single change to the database schema or application code. What is more, as the inserting and updating of records will be done via the Integration Engine (using the aforementioned XML initiator), business rules/logic will be completely supported.

Solution Overview

SDE API Overview

Essentially then, what we are going to build is an interface layer. Developers will be able to make a single reference to this interface layer and call “services” that, at their most basic level, allow them to retrieve a single string value or a dataset and, insert or update records without the need to understand how to invoke the XML Initiator functionality of the Integration Engine.

XML Message

So in this first part, I want to walkthrough the process of creating an Integration Engine package that is initiated by an xml message, and inserts/updates an Incident appropriately (depending on whether an Incident # is passed). I also want to explain some of the pitfalls I fell into when doing this and how to get around them!

To do that I need to explain VERY BRIEFLY what OUR xml message looks like that is going to cause this package to run. Hopefully, you will have noticed the capitalisation in the previous sentence – this is NOT a lesson in xml!

<?xml version="1.0"?>
<request>
<incidentSequence>3</incidentSequence>
<clientID>ABARBER</clientID>
<configurationID>S_01_B_03_R_332</configurationID>
<serviceName>DESKTOP</serviceName>
<supportSubjectID>HWCD</supportSubjectID>
<incidentDescription>My CD-ROM just exploded!</incidentDescription>
<inventoryItemSequence>34442</inventoryItemSequence>
<incidentResolution></incidentResolution>
<userDefinedStatusID>OPEN</userDefinedStatusID>
</request>

The first line is simply a declaration specifying the version of xml we are using. After that we have a single parent element called “request”. Technically, it doesn’t matter what you call this element but if you call it “request” you will have a lot less work to do in future parts, if you are following along. This parent element translates to a table in the Integration Engine. Inside this request element are a number of child elements. Each of these child elements (e.g. <clientID></clientID>) translates to a column inside the parent table. Between the opening element name (<clientID>) and the closing element name (</clientID>) is the value that is to be inserted into that column.

XML Initiator

So let’s build a package that accepts the above xml message as an initiator.

BTW, as with all my Integration Engine posts the packages are provided at the bottom of the post to be imported into your solution if appropriate. That said, I want to walkthrough the steps as hopefully it will assist in creating different or more complicated packages/integrations.

We begin by logging into the Integration Engine (http://appservername/integrationconsole by default) and creating a new package called “Incident_InsertUpdate”. Click the <Package Editor> button.

Incident_InsertUpdate 01

As we are only going to use a single step I have used the same name, Incident_InsertUpdate, for the step name. Click the <Step Editor> button.

Incident InsertUpdate 02

Select the XML Initiator Type and click the XML Source tab.

Incident InsertUpdate 03

In the Enter URL text box enter Incident_InsertUpdate.post. Enter XmlData as the Post Variable Name and paste our xml message above into the Sample XML Data removing, I would suggest, my sample data!

Before we carry on let’s slow down and understand what this screen is about. In IIS, under your Service Desk Express website are a bunch of virtual directories as shown below (don’t worry if you don’t have this many as there are a few of my own):

Incident InsertUpdate 06

Critically, there is one called XMLPostHandler. The XML Initiator monitors this virtual directory for post files (*.post) and when it receives one, it checks its list of packages for a matching name – in our case Incident_InsertUpdate.post. That is what you are specifying in the Enter URL text box and it is this that allows the Integration Engine to know which package to invoke based on which xml message.

The Post Variable Name is the variable name in the file that contains the xml message. Don’t worry too much about this as we are going to handle this in a latter post so developers don’t have to.

The Sample XML Data simply allows you to complete the rest of the package with a certain amount of intellisense such that in latter steps your fields are available for selection.

Click the Select Table tab.

Incident InsertIpdate 04

Notice that the Select Table Name is prefilled with the word “request”. This is parent element name from our sample xml file. Click the Data Fields tab.

Incident InsertIpdate 05

That’s the Initiator configured. Click the Source question mark.

Incident InsertUpdate 07

This one is really easy. Select None/Not Used as the Adapter Type. This is because the Initiator is the source as well as the initiator. Click the Target question mark.

Incident InsertUpdate 08

Select SDE as the Adapter Type and click the SDE Connection tab.

Incident InsertUpdate 09

Select SDE as the DSN, enter any account that has read/write access to the SDE database in the User ID textbox and enter it’s password in the Password textbox. Enter your _SMSYSADMIN_ password in the _SMSYSADMIN_ Password textbox. Click the SDE Details tab. If you got it correct then this will populate – if not you’ll get an error here.

Incident InsertUpdate 10

Select SYSTEM ADMINISTRATION as the Group Name, Incident as the Module Name, and Insert/Update and the Insert/Update Mode. Check the box to Use Business Rules. Click the SDE Sub Details.

Incident InsertUpdate 11

Given that we are updating as well as inserting, the Integration Engine needs to know the unique key to update based on – in this case Incident #. Click the Data Field tab.

Incident InsertUpdate 12

That’s the Target specified. Now click the Mapping icon to finish off.

Incident InsertUpdate 13

Go through the fields highlighting the target and the source columns and clicking Quick Map to enter them such that for example, Incident # in the target column matches incidentSequence in the source column. You should end up with something that looks like the above. Click the Save button.

Now here is where the wheels first fell off the bus so to speak. If you leave the package like that it will work BUT (and it is a pretty big but) only if all the fields are populated every time. As we plan on handling inserts (when we don’t know what the Incident # will be) this is pretty useless. Don’t panic – help is at hand. Click the Advanced tab!

Incident InsertUpdate 14

What you are looking at is what you just created but in VBScript as opposed to a nice GUI. We are going to edit this VBScript! Click the Edit Script checkbox.

Replace the mapData() sub provided with the code below:

Sub mapData()
'targetRow("TargetColumn") = sourceRow("SourceColumn")
If (initiatorSourceRow("incidentSequence") <> "") Then targetRow("Incident #") = initiatorSourceRow("incidentSequence")
If (initiatorSourceRow("clientID") <> "") Then targetRow("Client ID") = initiatorSourceRow("clientID")
If (initiatorSourceRow("configurationID") <> "") Then targetRow("CI Assembly ID") = initiatorSourceRow("configurationID")
If (initiatorSourceRow("serviceName") <> "") Then targetRow("Service Name") = initiatorSourceRow("serviceName")
If (initiatorSourceRow("supportSubjectID") <> "") Then targetRow("Subject ID") = initiatorSourceRow("supportSubjectID")
targetRow("Incident Description") = initiatorSourceRow("incidentDescription")
If (initiatorSourceRow("inventoryItemSequence") <> "") Then targetRow("Seq.Configuration Item") = initiatorSourceRow("inventoryItemSequence")
targetRow("Incident Resolution") = initiatorSourceRow("incidentResolution")
If (initiatorSourceRow("userDefinedStatusID") <> "") Then targetRow("Status ID:") = initiatorSourceRow("userDefinedStatusID")
End Sub

Don’t panic! All we are doing is telling the Integration Engine, “Don’t both doing a mapping if the source data is blank.” Hence the bunch of VBScript If statements.

Click the Save button and your done.

Now the cool thing here is that you can test this package WITHOUT a client. All you need to do is go back to the Initiator section and click the XML Source tab. If you specify valid dummy data in the Sample XML Data (e.g. <clientID>ABARBER</clientID> as opposed to <clientID></clientID>) then you can go ahead and click the Execute Package button and the package will execute using your valid dummy data. If for some reason it doesn’t work take a look at my previous “Debugging Integration Engine Packages” post for help or drop me a mail (alan@14j.co.uk).

Integration Console Package: Incident Insert/Update Integration Console Package

So that’s it for this post. Feedback always welcome. In a future post we’ll take a look at the elements of our interface layer and build on what we’ve done in this post.

Debugging Integration Engine Packages

Recently I have been doing a lot of playing around with Integration Engine packages and have consequently, unfortunately, found the need to debug them. So I thought I would write a quick post (I know unusual as they are normally somewhat verbose!) describing one method of Debugging Integration Engine Packages. This post doesn’t explain how to fix your packages; simply how to obtain the information that allows you to fix them.

DebugView

If you haven’t already downloaded DebugView, do so now (there is a link on my blogrole) as you have no idea what you are missing! It is a free tool that was written by SysInternals who were later bought by Microsoft and it is just superb for debugging anything to do with Service Desk Express. Once you have downloaded it, you can run it either from a client pointing at your Service Desk Express application server or actually on the application server itself.

Debugging IE Packages 01

Enabling Trace Package Logging

In order to get this to work and work really well you need to enable the Trace level Package Logging on your faulty package and tell the Integration Engine to output the results to DebugView.

Open your package in the Integration Engine by selecting it and clicking the button. Click the Logging tab.

Debugging IE Packages 02

Specify Trace as the Log level, and select the Both radio button for Package log output. Click Save.

Go back to Package Manager and click the button. Now take a look at the DebugView window you left open.

Debugging IE Packages 03

Debugging IE Packages 04

It captures a whole host of data including the error that occurred.

Fix up the error, execute the package again and take another look at the DebugView window.

Debugging IE Packages 05

Hope it helps. As always all feedback, positive and negative, always welcome.