How to dynamically filter a popup list in Service Desk Express

If you have a table that is reused in different circumstances, e.g. the Vendors table which is used to store both manufacturers and suppliers, or if you have significant amounts of legacy data that you still need to track, you may need to filter your popup lists to make it more manageable for your support staff.

Imagine a scenario where you have inherited a legacy asset base and since then have introduced initiatives such as strategic sourcing and managed desktops. So in the past you may have had to create asset records for inkjet printers and CRT monitors and, as those assets are probably still within your enterprise, they still need to exist in the configuration items and configuration item type tables but going forward, the assets you will be adding will be a much more standardised. Add to this, whilst you still need to maintain a large vendor list for legacy reasons, the actual vendors that you will be using in the future will be significantly less.

So, if this sounds familiar what options are available to you to handle this? The first and easiest option is simply to inactivate all the records that you don’t want to see. My issue with this approach is that a) inactive to me symbolises deleted without loss of history e.g. asset has been disposed of, and b) there are certain features of the application that won’t work properly if an record is inactive – e.g. the booking in of purchase requests where the configuration item type is inactive.

A more flexible option is to use the built feature to filter what appears in the popup and when combined with a little database administration work provides, in my opinion, an entirely better solution.

Filtering the vendors

Using the DB Admin Tool I can create an additional field called “Is Manufacturer” in the vendors module as shown below:

I am then going to create another field, exactly the same as the one above except that the default will be “No”, called “Is Supplier” like this:

Now, after logging back into Service Desk Express, I can use the Form Customisation tool to create a copy of the CI Type form. I want to filter the Sales and Service Vendor ID fields to only show vendors where the “Is Supplier” checkbox is ticked and the Manufacturer Name field to only show vendors where the “Is Manufacturer” checkbox is ticked. All I need to do is double-click on the Sales Vendor ID field, select the Filter tab at the top of the screen and enter the code as shown below:

…and then repeat for the Service Vendor field and the Manufacturer field not forgetting to change the code for this field to:

Is Manufacturer = 1

Obviously, you need to add the “Is Supplier” and “Is Manufacturer” checkboxes to your Vendor form but other than that that is it. That should make you Procurement team’s life a little easier.

Filtering the configuration item types (or inventory catalogue)

Now what I want to do is make the engineers’ life a little easier. When they have to create an asset/configuration item, I want them to have a filtered list of CI Types where a) the CI Type is approved and b) the CI Type is flagged to either track asset or track serial.

All I need to do is modify the Configuration Item form and and modify the CI Type field adding a filter as shown below:

Conclusion

The beauty of using this method is that you can create other versions of the same forms that don’t filter the list i.e. for Administrator’s use, and enhance to the user’s experience of Service Desk Express.

Auto-Refresh An ASPX Page Programmatically

I wanted to be able to allow a user of one of my applications to specify an auto-refresh rate for a summary page. There are a whole bunch of ways of doing this – some discussed below – each having their own strengths and weaknesses as techniques. Given my experience, I thought I would share a) another way of doing it and b) some of the challenges I came across when trying to use other techniques.

Introduction

Before I begin I think it would help to show, in a little more detail, what I was trying to accomplish. The screenshot below is from my TechMagic project that essentially allows users a readonly view of a bunch of common tables in Service Desk Express as a series of datagrids housed in an accordian control.

I wanted users to be able to set their own refresh rates that persisted sessions without storing anything in a database and as such chose to make use of cookies. That was the easy bit – automatically refreshing the page became a little more challenging.

Standard Methods of Auto-Refresh

HTTP-EQUIV Method

The HTTP-EQUIV method of auto-refreshing a page is very simple. Essentially between the <head></head> tags of the HTML you simply include a line: <meta http-equiv=”refresh” content=”5″ />. This would cause the page to refresh every 5 seconds. To make this dynamic such that a user can enter a value all we do is change the line slightly to allow access to it programatically:

<meta id="autoRefresh" runat="server" >

Now in the code-behind we can right some code in the Page_Load event that looks something like:

autoRefresh.HttpEquiv = "refresh";
autoRefresh.Content = txtRefreshRate.Text;

JavaScript Method

The JavaScript method is also very easy to implement. We just create a function that puts a timeout on the page:

<head>
<script type="text/javascript" language="javascript">
function refreshPage()
{
window.location = unescape(window.location);
}
function setTimeOut()
{
window.setTimeout(refreshPage, document.getElementById("txtRefreshRate").value * 1000);
}
</script>
</head>

<body onload="setTimeOut()">

A slightly different approach

Both these methods essentially do the same thing and work perfectly well. The problem is that if my users had applied sorting to any of the datagrids or collapsed/expanded different panels, all these settings were forgotten after the page refreshed. I rapidly realised what I ACTUALLY wanted to do was a programatic postback rather than a refresh. So here is how I did it.

Essentially, I used the JavaScript method above but instead of setting the window.location in the refreshPage function, I simply click the Update button that you can see in the screenshot:

function refreshPage()
{
var btn = document.getElementById("btnUpdate");
if (btn) btn.click();
}

This works perfectly but seems, at least to me, a very clumsy approach so as always, I welcome any feedback (positive or negative).

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.