How to count the number of occurrences of something in an Excel column

Recently a colleague produced a spreadsheet that was essentially acting as a planner something like the screen shot shown below:

My colleague then wanted to know how many days each member of staff had been allocated in the plan and how many staff members had been allocated to each week – essentially wanting to know how to dynamically count the number of x’s in each column/row. The answer lies in the Excel COUNTIF function:

=COUNTIF(range,criteria)

where range is the range of cells you want to search in whilst criteria is what you are looking for.

So simply by clicking in the cell N2 and entering the formula =COUNTIF(B2:M2,”x”) as shown in the screen shot below, the formula returns a 1 which is correct:

This formula is simply saying move through each of the cells B2, C2, D2 etc. to M2 and count how many times x appears. Notice that the COUNTIF function is case-insensitive but DOES require that strings are enclosed in quotation marks (“”). Simply by cutting and pasting we can fill in all the other totals for the employees as shown below:

Finally, we just need to repeat for the weeks by clicking in cell B22 and entering the formula =COUNTIF(B2:B21,”x”) as shown in the screen shot below:

Again, a simple cut and paste exercise along the row and you now have a dynamic count of the number of x’s in the rows/columns:

Hope this helps – as always all comments (positive or negative) are always welcome.

CI Assemblies/Configurations – How To Use Them – Part 1

CI Assemblies, or Configurations as they used to be called prior to Service Desk Express 9.6, are the basis for grouping Configuration Items, or Inventory Items prior to 9.6, together by location. What I thought I would write about in this post are my views on best practice for using them and some of the tricks/tips you might want to employ/watch out for in your own organisation.

A little imagination required…

I am going to try and explain the use of CI Assemblies through three, hopefully relevant, examples relating to a single, hopefully relevant, organisation. So for the purposes of this post, imagine an organisation spread over a number of sites, with assets and users in a variety of different buildings and rooms. Imagine that this organisation has a mix of laptop and desktop based users, using shared printing facilities and, to make it a bit more complex, using roaming profiles and hot desks. Finally, imagine that this organisation wants to be able to bill departments based on the assets assigned to them/their users.

So how could you implement this?

The first thing to understand is the database structure relating to CI Assemblies:

As you can see from the figure above, Configuration Items are linked to CI Assemblies on a one to many relationship such that a single configuration item can only be linked to a single CI Assembly but a single CI Assembly can have many configuration items assigned to it. Slightly harder to understand, a single CI Assembly can be linked to multiple clients AND a single client can have multiple CI Assemblies i.e. a many to many relationship. This is what provides us the flexibility to handle the example above but at the same time it provides an added level of complexity that needs to be very carefully handled, particularly when it comes to writing reports. More on that later.

So back to the question – how could you implement this?

Solution

The first thing I would suggest you do is add one or more modules to provide a definitive list of locations. Out of the box Service Desk Express doesn’t understand the concept of sites using CI Assemblies to manage locations. The building and room fields in the database are free text which can lead to data inaccuracies as a result of misspelled or abbreviated building names. I would suggest you build three new modules for Sites, Buildings and Locations as per the schema below:

So now each CI Assembly can/shall have a definitive location.

It is critical that your organisation agrees on a naming convention for CI Assemblies. I would suggest that you have a different naming convention based on the type of CI Assembly for reasons that will become apparent.

Now you can create a single CI Assembly for each desk in each location using the following naming convention for the CI Assembly ID: SSS_BBB_LLL_DDD where:

  • SSS is a three letter site code
  • BBB is a three letter building code
  • LLL is a three letter location code and
  • DDD is a three letter desk code.

This sounds like a lot of work and it is. The good news is that generally this information is relatively static data so once entered/imported the hard work is done.

Next we can assign all the fixed assets – desktop PCs, monitors, docking stations to these locations. For those users that are fixed to a given desktop PC I would then assign that CI Assembly to that Client. Hot Desks are frequently billed by department so I would link the hot desk CI Assemblies to the department manager.

OK, so that sorts out the hot desks and fixed desks. What about the mobile users.

Create a single CI Assembly for each user with a CI Assembly ID equal to their Client ID as a naming convention. Into this CI Assembly would go each user’s laptop, mobile phone, PDA etc. and potentially, depending on how you manage software licenses, each user’s software licenses. When a user logs a ticket with the Service Desk, the Service Desk is able to check where they are say and consequently link that CI Assembly ID to that user giving them a pick list of assets based at that location. That will sort out the mobile users.

So why does the system need to allow multiple users to own/use a single CI Assembly? Amongst other reasons – printers! We need to create a CI Assembly for each shared networked printer using an appropriate  naming convention (I’ll leave this one up to you). Assign the relevant configuration item (aka the printer) to it and then link each user that uses that printer to it. WHY????

Think for a moment about the Service Desk Agent – takes a call from Joe/Jane Public and is now able to say, “What is the issue?” and now has an appropriate configuration item to link to EASILY pretty much no matter what the answer is. This results in amazing data for analysis and take problem and change management to an exciting new level.

In the next post in this series I will look at some of the ways (read business rules etc.) that can be included to reduce the manual burden associated with the solution I have proposed above. Additionally, we will look at some of the issues/techniques for reporting on this solution.

As always, hope this has proved informative. Any comments, positive or negative always welcome. Thanks for reading.

Email Conversation and Notifications

I can’t imagine any Service Desk Express implementation that doesn’t involve a business rule that sends an email to a client when a ticket is logged in their name and I remember back in 2003 my first ever business rule did exactly that. The thing was that I was also asked to create an Incident Detail that recorded the fact that the email had been sent along with the content of that email.

So what’s the point. Roll forward five years and we now have Service Desk Express complete with Email Conversation. Email Conversation out of the box, allows support staff to click a button from inside a ticket and send an email using a web based interface. Out of the box, the fact that the email has been sent is recorded as an Incident Detail complete with the content. Out of the box, if a client replies to said email, an Incident Detail is automatically created containing the content of the reply and an email is sent to the support staff the call is assigned to. The point is – you can programmatically create an Email Conversation record via a business rule and consequently you automatically get all this functionality – out of the box.

If you want to use Email Conversation programmatically then you need to create a new Email Conversation form through Form Customisation. The important fields to include on this new form are:

  • Module # – This field determines which module the email conversation refers to e.g. Incident, Work Order, Purchase Request etc. It is important to understand that you need to use the Sequence number of the module concerned NOT the module name. In the case of Incidents, the Sequence number is 24. You can find out the Sequence number for other modules using:
    • SELECT SEQUENCE, VIEWNAME FROM _SMDBA_._MODULE_ ORDER BY VIEWNAME
  • Record # – This field determines which record in the above module this email conversation is linked to. So in our case this will be linked to the Incident # of the incident raised.

An example new email conversation form is shown below:

Once you have done that you can go ahead and create your very simple business rule.

Shameless Plug
If you haven’t downloaded a copy of Nashco’s Business Rule Print Preview do so. You can get it from here. It is a very cool little executable that adds additional functionality to the Business Rules Manager window and Business Rule Customisation window allowing for the printing/documentation of business rules. It also works with Client-Side Business Rules too.

The simple business rule required is shown below thanks to the above Nashco Business Rule Print Preview application:

With a rule like this replacing you standard notifications, the client essentially ends up with the same email but you automatically get all the added functionality mentioned above for free.

As always, hope this helps. Keep your feedback coming (positive or negative) – I really value it.

Service Desk Express API – Part 2

In my first post in this series (Service Desk Express API – Part 1) I discussed the XML Initiator and demonstrated how to build an Integration Engine package that would accept an inbound XML message and raise an incident. In this post I want to take this a step further and discuss how to build the XML message (in C#) to send to the Integration Engine. To do this I have built a fairly simple web service that will provide our development interface to Service Desk Express, which during this post I will explain how it can be configured to provide any function. You can download the web service here.

You will need to create a new virtual directory in IIS called APIMagic pointing at the contents of the APIMagic directory in this zip file, with “Scripts Only” execute permissions and configured to use the .NET Framework 2.0 as shown in the screen shots below:

Before we start configuring our functions (or webmethods) that our developers will use we need to configure the web service to talk to Service Desk Express.

Configuration Settings

As discussed in the first post in this series, one of the objectives we are trying to achieve is to provide a way of retrieving data from the Service Desk Express database. To do that we need to provide details of how to connect to the database in the form of a connectionString entered in the web.config. Open the web.config file and modify the section shown below to point to your DB server using whatever SQL username and password you like:

<connectionStrings>
<add name="SDE" connectionString="Data Source=DBServerName;Initial Catalog=SDE;User ID=APIMagic;Password=APIMagicPassword;" providerName="System.Data.SqlClient" />
</connectionStrings>

The account shown here APIMagic only needs db_reader permissions to the Service Desk Express database.

The other piece of information we need to provide is where the XmlPostHandler service is located. As discussed in the first post in this series this is normally: http://appServerName/XmlPostHandler and this value needs to be entered in web.config as an applicationSetting as shown below:

<appSettings>
<add key="XmlPostHandlerURL" value="http://appServerName/XmlPostHandler/" />
</appSettings>

So once you have set these two configuration settings, save the file and you are ready to start building functions…

Before we do however, I wanted to just explain a couple of features of the webservice and how it works.

Structure and Generic Functions

Essentially, the most important thing to realise is that your developers will connect to the Service.asmx file which will in turn provide access to the Service.cs file in the App_Code directory. All of your custom functions will be written in the Service.cs file and this is the only file (other than the web.config file we modified earlier) that you will need to work with.

Supporting the Service.cs file is a number of other files (or classes): Data_Access.cs, Utility.cs, and XML_Parameter.cs. You don’t need to worry about these files but you do need to understand the generic functions that are available to you and how to use them.

Data Access Class

The Data_Access class handles all the “complex” stuff with regards retrieving data from the Service Desk Express database and building our XML documents and sending them to the Integration Engine for parsing.

Essentially it provides three functions:

  • Return_Single_Value: This function will return a single string of data from the database as the result of a Select statement, e.g. SELECT CLIENT FROM _SMDBA_._CUSTOMER_ WHERE SEQUENCE = 1001.
  • Return_DataSet: This function will return a dataset of data (essentially a table) from the database as the result of a Select statement, e.g. SELECT * FROM _SMDBA_._CUSTOMER_
  • PostXML: This function, in conjunction with the XML_Parameter class will allow you to create and send XML messages or posts to the Integration Engine for processing.

XML Parameter Class

Fundamentally, the XML Initiator in the Integration Engine parses the nodes of the XML document posted to it. The function of the XML_Parameter class is to provide a reusable business object that allows us to define those XML nodes for any given record (e.g. an Incident).

The class has three properties: Name, Value, and Parent. To explain these properties lets consider a node from our example XML document shown in the first post in this series.

<request>
<clientID>ABARBER</clientID>

</request>

So to build this node our properties would be:

  • Name: clientID
  • Value: ABARBER
  • Parent: request

So for each node of our XML document we will instantiate a new instance of the XML_Parameter class and pass in the appropriate values. More on this later so don’t panic.

So let’s build something useful

So, by way of an example, we will create three functions to demonstrate how the service is used:

  • Incidents_SelectCountByClientID: A function that simply returns the number of Open/Closed/Both incidents for a given Client ID.
  • Incidents_SelectByClientID: A function that returns a dataset of incidents for a given Client ID.
  • Incident_InsertUpdate: A function that creates/updates an incident.

These three functions are the same three functions that are already coded in the Service.cs file and commented out.

Incidents_SelectCountByClientID

The purpose of this function is to demonstrate the Return_Single_Value function described earlier. I doesn’t really matter what the SQL query is as long as it returns a single value.

[WebMethod(Description="Function to return the count of open/closed/both incidents for a given Client ID.")]
public string Incidents_SelectCountByClientID(string sClientID, string sState)
{
try
{
string _Value = "";
string _SQL = "SELECT Count(*) FROM [_SMDBA_].[Incident] WHERE [Client ID] = '" + sClientID + "' AND [InActive:] = 0";
if (sState.ToUpper() != "B")
{
_SQL = _SQL + " AND [State:] = '" + sState + "'";
}
_Value = _da.Return_Single_Value(_SQL);
return _Value;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_Single_Value", ex);
}
}

As you can hopefully see, it wouldn’t be that complicated to change the function or create a new one for selecting the number of work orders or purchase requests for a given Client ID. All you need to do is copy and paste the above code and change the _SQL statement.

Incidents_SelectByClientID

The purpose of this function is to demonstrate the Return_DataSet function described earlier.

[WebMethod(Description = "A function that returns a dataset of incidents for a given Client ID.")]
public DataSet Incidents_SelectByClientID(string sClientID)
{
try
{
DataSet _ds = new DataSet("Results");
string _SQL = "SELECT * FROM [_SMDBA_].[Incident] WHERE [InActive:] = 0 AND [Client ID] = '" + sClientID + "'";
_ds = _da.Return_DataSet(_SQL);
return _ds;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_DataSet", ex);
}
}

Again, if we wanted a function to return all Work Orders we would simply copy and paste this function, change it’s name, and change the _SQL string to select from work orders instead.

Incident_InsertUpdate

The purpose of this function is to demonstrate the PostXML function described earlier, and for me, this is the most exciting of the functions. To post any XML file to the Integration Console all you need to do is create a list of XML Parameters and call this function as shown below:

[WebMethod(Description = "Function to either update or insert an incident record into the SDE database.")]
public bool Incident_InsertUpdate(string iIncidentNo, string sClientID, string sConfigurationID, string sServiceName,
string sSupportSubjectID, string sIncidentDescription, string iInventoryItemSequence,
string sIncidentResolution, string sUserDefinedStatusID)
{
bool _isSuccess = false;
try
{
List<XML_Parameter> _xmlParameterCollection = new List<XML_Parameter>();
_xmlParameterCollection.Add(new XML_Parameter("incidentSequence", iIncidentNo, "request"));
_xmlParameterCollection.Add(new XML_Parameter("clientID", sClientID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("configurationID", sConfigurationID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("serviceName", sServiceName, "request"));
_xmlParameterCollection.Add(new XML_Parameter("supportSubjectID", sSupportSubjectID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentDescription", sIncidentDescription, "request"));
_xmlParameterCollection.Add(new XML_Parameter("inventoryItemSequence", iInventoryItemSequence, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentResolution", sIncidentResolution, "request"));
_xmlParameterCollection.Add(new XML_Parameter("userDefinedStatusID", sUserDefinedStatusID, "request"));
_da.Post_XML_Request("Incident_InsertUpdate.post", _xmlParameterCollection);
_isSuccess = true;
return _isSuccess;
}
catch (Exception ex)
{
throw _u.Error_Handler("Incident_InsertUpdate", ex);
}
}

Let’s take a look at how this function works. Essentially all that is happening is that for each variable that is being passed to the function (i.e. sIncidentNo, sClientID etc.), a new instance of the XML_Parameter class is being instantiated and passed the appropriate values to create an XML node. The parent node is called “request” hence why, at least in my example, all the nodes are being passed the value “request” as their parent value.

So what would happen if you wanted to pass a new variable. Well you would simply create a new variable in the function declaration and then add another XML Parameter:

e.g. _xmlParameterCollection.Add(new XML_Parameter("problemNo", sProblemNo, "request"));

What about if you wanted to create/update work orders instead. Assuming that you have already created a new Integration Engine package, copy and paste the function, change it’s name and, and this is the important bit, change the post value to something sensible that you have specified in your Integration Engine package:

e.g. _da.Post_XML_Request("WorkOrder_InsertUpdate.post", _xmlParameterCollection);

Testing

To test any of the three function you have built (or uncommented perhaps :-)) simply open a browser (ON THE SERVER WHERE THE WEBSERVICE IS INSTALLED) and type http://localhost/APIMagic/Service.asmx and you’ll be greeted with the Service.asmx webpage as shown below:

Click on any of the links and enter appropriate values and you should find it all works as shown below:

Summary

It may not be much at the moment but, simply but copying and pasting the WebMethods and changing a small amount of code (obviously coupled with some appropriate Integration Engine packages) you should be in a position to provide a genuine API to Service Desk Express.

The truth is, I have found this post very hard to write as I didn’t quite know what to explain in detail and what to glance over. Between now and when BMC decide to release their new Web Services module for Service Desk Express, I will keep updating this web service with new functions. I would really welcome any feedback a) in general and/or b) specific to this post/project whether positive or negative. I hope this has proved useful.

How To Default The Start And End Date of Any Month in SQL Reporting Services

It seems, from experience, that most of the Microsoft SQL Reporting Services reports I write these days, have a requirement for a date range set via parameters. Whilst you can just let your users select the dates they want the report to run, you can also provide default values that are then changeable or not. So I thought I would explain in this very short post how to default a StartDate parameter to be the 1st of the current month and an EndDate parameter to be the last day of the current month. In Visual Studio select Report > Report Parameters.

StartDate

Click Add and enter the following:

Name: StartDate
Data type: DateTime
Prompt: Start Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=CDate(“01/” & Month(Now) & “/” & Year(Now))

EndDate

Click Add and enter the following:

Name: EndDate
Data type: DateTime
Prompt: End Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, Parameters!StartDate.Value))

Click OK.

All we are doing here is taking the previously calculated StartDate parameter (now, for example, 01/10/2008) and adding a month to it, before subtracting one day. This allows us to get the last day of the month irrespective of how long the month is or whether it is a leap year or not.

Final Word Of Caution Re Defaults

Default parameters make life a lot easier for end users but they must be used very carefully in SQL Reporting Services because, a report will automatically execute if all the required parameters are defaulted. As such if the users generally are not accepting your defaults then they should be remove as it is placing an extra query against the database for no reason. Alternatively, add another parameter that is not defaulted so that, whilst the dates pre-populate, the report wont run until that parameter is set.

Hope this was of help. As always, feedback (positive or negative) is always welcome.

How To Make Sure Two Divs Are The Same Height

I recently needed to make sure that two DIV tags always stay the same height even though both have variable content, driven from a database. I thought I would share the solution I used although would welcome any alternative ways of doing the same thing.

HTML

The basic HTML I started with is shown below:

<html>
<head>
<title>Maintaining DIV Heights</title>
<style type="text/css">
#divLeft
{
float:left;
width:49%;
border:1px solid #000;
}
#divRight
{
float:right;
width:49%;
border:1px solid #000;
}
</style>
</head>
<body>
<div id="divLeft">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
<div id="divRight">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
</body>
</html>

So it’s a rather basic file and the idea is that no matter how much text you type into either column, the heights will always remain the same.

Solution

The solution I used involved a little JavaScript that essentially:

  1. Finds the height of the two columns and stores those values in two variables. I have had to check for IE as IE and Firefox/Safari interpret offsetHeight and clientHeight differently.
  2. Decides which is taller – the left or the right.
  3. Makes the shorter column the same height as the taller column.

So I added the function below into the <head></head> area…

<script type="text/javascript" language="javascript">
function setDivHeights() {
if (document.all) {
var iLeftHeight = document.getElementById("divLeft").offsetHeight;
var iRightHeight = document.getElementById("divRight").offsetHeight;
}
else {
var iLeftHeight = document.getElementById("divLeft").clientHeight;
var iRightHeight = document.getElementById("divRight").clientHeight;
}
if (iLeftHeight > iRightHeight) {
document.getElementById("divRight").style.height = iLeftHeight + "px";
}
else {
document.getElementById("divLeft").style.height = iRightHeight + "px";
}
}
</script>

…and then added this piece of JavaScript to call the function in the <body> tag…

onLoad="setDivHeights()"

…such that the end result looked like:

<html>
<head>
<title>Maintaining DIV Heights</title>
<style type="text/css">
#divLeft
{
float:left;
width:49%;
border:1px solid #000;
}
#divRight
{
float:right;
width:49%;
border:1px solid #000;
}
</style>
<script type="text/javascript" language="javascript">
function setDivHeights() {
if (document.all) {
var iLeftHeight = document.getElementById("divLeft").offsetHeight;
var iRightHeight = document.getElementById("divRight").offsetHeight;
}
else {
var iLeftHeight = document.getElementById("divLeft").clientHeight;
var iRightHeight = document.getElementById("divRight").clientHeight;
}
if (iLeftHeight > iRightHeight) {
document.getElementById("divRight").style.height = iLeftHeight + "px";
}
else {
document.getElementById("divLeft").style.height = iRightHeight + "px";
}
}
</script>
</head>
<body onLoad="setDivHeights()">
<div id="divLeft">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
<div id="divRight">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
</body>
</html>

An example of the solution can be found here. If you have padding as part of the divs then you need to do some jiggerypokery to get the heights to balance again by subtracting the top and bottom padding from the Firefox/Safari side.

For example:

If the top and bottom padding were 10px you would need to subtract 20.

var iLeftHeight = document.getElementById(“divLeft”).clientHeight – 20;
var iRightHeight = document.getElementById(“divRight”).clientHeight – 20;

If anyone has any better solutions than this then I would be very open to it. Anyway, hope this helps. As always, any feedback (positive or negative) is always appreciated.

Default Groups when Logging Into Service Desk Express 9.6

Network Associates/BMC have never supported the concept of users having a default group when they log into Magic/Service Desk Express. Judging by the comments in the forums lately I would suggest that this could be a feature for a future release. There were some rather elegant solutions posted in the forums that I think would do rather well, but in the meantime I set about finding a “workaround” to the issue. What I propose to discuss here is an alternative solution that you can implement yourself that will “remember” the user’s last logged on group between browser sessions by storing a cookie on the user’s machine.

PLEASE TAKE A BACKUP COPY OF THE TWO FILES BEFORE MODIFYING THEM AS THIS IS AN UNSUPPORTED HACK!

Also please be careful that when cutting and pasting from this blog to notepad that “” are not accidentally replaced with a different type of quotes.

Storing the Cookie

So the first step is modifying a file that will allow us to store which group the user has logged on as. There are a bunch of files you could do this but I choose options_nailogo.aspx using a little JavaScript. Open options_nailogo.aspx (C:Program FilesBMCService Desk ExpressApplication Server by default) in Notepad or, more preferably, Notepad ++ (an absolutely brilliant editor that I now use for pretty much everything). Near the bottom of the file you will find code that looks like:

<script Language="JavaScript">
//alert(""+strBaseURL);
window.status =  sUserName + "; " + sGroupName;
window.defaultStatus = sUserName + "; " + sGroupName;
document.all("marqWBNotice").trueSpeed=true
</script>
<script language="JavaScript">clmGetText(document)</script>

We are going to insert a little function between these two functions that stores the cookie on the user’s machine such that code looks like:

<script Language="JavaScript">
//alert(""+strBaseURL);
window.status =  sUserName + "; " + sGroupName;
window.defaultStatus = sUserName + "; " + sGroupName;
document.all("marqWBNotice").trueSpeed=true
</script>
<script Language="JavaScript">
var expiry = new Date();
expiry.setTime(exp.getTime() + (1000 * 60 * 60 * 24 * 30));
document.cookie = "SelectedGroup=" + escape(sGroupName) + "; expires=" + expiry.toGMTString() + "; path=/";
</script>
<script language="JavaScript">clmGetText(document)</script>

Save the file.

Retrieving the Cookie

When a user tabs out of the UserName textbox of the Login screen of Service Desk Express a C# function is called that dynamically builds the HTML of the dropdown list based on the list of groups that the user is a member of. So essentially all we are going to do is modify this function to grab the cookie we placed in the function above (if it exists) and use it to set the selected option of the Group downdown list. This function lives in Login.cs. Open Login.cs (C:Program FilesBMCService Desk ExpressApplication Serverincludes by default) in any text editor and replace the function GetUserGroups with the function below:

string GetUserGroups(string sUsrName)
{
string strGRPName, strGRPSeq, strOutPut;
strOutPut = "";
string strDefaultGRPName = "";
HttpCookie cookie = Request.Cookies["SelectedGroup"];
if (null != cookie)
{
strDefaultGRPName = cookie.Value.ToString();
strDefaultGRPName = strDefaultGRPName.Replace("%20", " ");
}
if (MetaData != null)
{
System.Diagnostics.Trace.WriteLine("[Login] Metadata available.");
NAMMETADATALib.IMUser objUser = (MetaData.Users as NAMMETADATALib.IMUsers).GetUserByName(sUsrName) as NAMMETADATALib.IMUser;
if (objUser != null)
{
for (int i = 0; i < objUser.GroupCount; i++)
{
NAMMETADATALib.IMGroup objGroup = objUser.GetGroupByIndex(i) as NAMMETADATALib.IMGroup;
if (objGroup.IsActive == true)
{
strGRPName = objGroup.name;
strGRPSeq = objGroup.Sequence.ToString();
if (strDefaultGRPName != "")
{
if (strGRPName == strDefaultGRPName)
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + " SELECTED="True">" + strGRPName + "</OPTION>";
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
}
}
else
{
strOutPut = "NOTVALIDUSER";
}
}
else
{
System.Collections.SortedList oUsers=null;
try
{
oUsers = (System.Collections.SortedList)FeatureManager.GetUserGroups(sUsrName);
}
catch(Exception e)
{
string strResponse="<DATA>";
strResponse=strResponse+ "<ERROR>"+ e.Message +"</ERROR>";
strResponse=strResponse+"</DATA>";
strOutPut=strResponse;
return strOutPut;
}
if (oUsers != null)
{
if (oUsers.Count == 0)
{
strOutPut = "NOTVALIDUSER";
}
for (int i = 0; i < oUsers.Count; i++)
{
strGRPSeq = oUsers.GetKey(i).ToString();
strGRPName = oUsers.GetByIndex(i).ToString();
if (strDefaultGRPName != "")
{
if (strGRPName == strDefaultGRPName)
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + " SELECTED="True">" + strGRPName + "</OPTION>";
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
}
}
return strOutPut;
}

Save the file and that’s it. You may need to clear your browser’s cache and it is probably worth an IISRESET as well.

As always, hope this helps, and any feedback (positive or negative) is always appreciated.

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

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 not placed on form

 
Error message if Not Null field IS 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

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:

CSBR Condition CSBR Actions CSBR Action Details

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:

Mandated Fields Conditions CSBR Actions Mandated Fields Details

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

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

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

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

Incident InsertUpdate 02

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

Incident InsertUpdate 03

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

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

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

Incident InsertIpdate 05

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

Incident InsertUpdate 07

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

Incident InsertUpdate 08

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

Incident InsertUpdate 09

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

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

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

Incident InsertUpdate 12

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

Incident InsertUpdate 13

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

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.