Monthly Archives: August 2007

Standard

Posted by

Posted on

August 30, 2007

Posted under

Featured, Service Desk Express

Comments

13 Comments

Making the purchasing module work for your organisation – Part 2

In the first part of this series I discussed the business processes, roles/groups, database and form customisations, and addition data required to enable the purchasing module to really work for my fictitious organisation. In this post I am going to continue the series to cover the business rules and training required to technology enable my business process and minimize the amount of manual intervention.

Business Rules

In order to write these business rules we are going to walk through the process described in the earlier post – at this stage I need to express a great deal of thanks to Nashco Solutions who have developed a very cool “Business Rules Print Preview” that gives you a very simple and effective view of your business rules, and who have let me trial this addon free of charge. Using this tool I can provide you (my readers – I am assuming there is more than 1!) with a printable version of each of the business rules involved.

The first business rule we need is the one that takes a ticket (incident) and assigns it to the Procurement Team if the Subject ID = “PR”.

Purchase Request – Ticket Logged

Conditions

when an Incident

Create
Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{CUR,Subject ID} NotEqual {TR,Subject ID} And
{TR,Subject ID} Equals PR None

Actions

Action 1

Method Module Form
Update Incident Incident (ITIL)
Field Name Value
Incident # {TR,Incident #}
Group Name PROCUREMENT

Not very exciting but it does the job. If you enable the Subject ID to be available from Self-Service then the rule will work automatically from there as well. You might want to add the Purchase Request tab to your Incident form (if you haven’t already) such that when Procurement receive this ticket they can just click the Purchase Request tab and right-click to create the purchase request – that way they are linked too.

OK – so Procurement do their thing of creating the Purchase Request and adding the appropriate line items. Once they are happy they are ready to get the request approved (or rejected for that matter) by the client’s line manager, they create a new Purchasing History > Add Action of type For Approval. This kicks off the second business rule:

Purchase Request – Request Approval

Conditions

when a Purchase Requests

Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{CUR,Status ID} NotEqual {TR,Status ID} And
{TR,Status ID} Equals FOR APPROVAL None

Actions

Action 1

Method Module Form
Create Notifications Notifications
Field Name Value
Subject Purchase Request # {TR,Req #} requires your approval decision
Body <HTML>
<HEAD>
<TITLE>Service Desk Express – Purchase Request Approval</TITLE>
</HEAD>
<BODY>
<P>Dear {TR,Manager First Name} {TR,Manager Last Name},</P>
<P>{TR,First Name} {TR,Last Name} has asked the Procurement Team to raise a purchase request (# {TR,Req #}) for the following items:</P>
<TABLE BORDER=”0″ WIDTH=”75%”>
<TR>
<TD COLSPAN=”4″ STYLE=”font-weight: bold; color: white; background-color: #3366ff; font-variant: small-caps”>Line Items</TD>
</TR>
{MATH, (SELECT “_SMDBA_”.”GET_PR_LINE_ITEMS_HTML”({TR,Req #}))}
</TABLE>
<P><STRONG><EM>Description</EM></STRONG>: {TR,Description}<BR /><STRONG><EM>Needed by</EM></STRONG>: {TR,Date Required By}</P>
<P>Please click the appropriate link below to either approve or reject this request:</P>
<P>Approve: <A href=”mailto:sde@14j.co.uk?Subject=[{TR,Req #}] Purchase Request Decision=*APPROVED*”>Click here to approve this request</A><BR />Reject: <A href=”mailto:sde@14j.co.uk?Subject=[{TR,Req #}] Purchase Request Decision=*REJECTED*”>Click here to reject this request</A></P>
<P>Many thanks,</P>
<P>Procurement Team.</P>
</BODY>
</HTML>
Notification Module Descr SMTP Email
Client ID {TR,Manager Client ID}

The business rule sends the line manager an email containing the details he/she needs (including the line items) and provides two links at the bottom of the email that allow them to either Approve or Reject the request.

Now, as you can see from the Body, this business rule makes use of a SQL Server User-Defined Function that does not come with Service Desk Express. To use this function see a previous post – Purchase Request Line Items in Email, and you will need the HTML version of the function (Function GET_PR_LINE_ITEMS_HTML).

So, the line manager clicks the appropriate link which will open a new email (preformatted as required) for them to make their decision. Whatever they write in the body of the email will be recorded with the decision. This leads us nicely onto our next business rule.

Purchase Request – Decision Made

Conditions

when a Mail Listen

Create occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{TR,Subject} Contains Purchase Request Decision None

Actions

Action 1

Method Module Form
Create Purchase History New Purchasing History
Field Name Value
Note {TR,Message}
Date Performed {DATE} {TIME}
SeqPR {EXTRACT,[],{TR,Subject}}
Description Purchase request {EXTRACT,**,{TR,Subject}} by {DB,Clients,First Name,”EMail Address” = ‘{TR,AddrFrom}’} {DB,Clients,Last Name,”EMail Address” = ‘{TR,AddrFrom}’}
Purchasing Action ID {EXTRACT,**,{TR,Subject}}
Login ID MAGIC

Action 2

Method Module Form
Update Purchase Requests New Purchase Requests
Field Name Value
Req # {EXTRACT,[],{TR,Subject}}
Status ID {EXTRACT,**,{TR,Subject}}

Action 3

Method Module Form
Create Notifications Notifications
Field Name Value
Subject Purchase Request # {EXTRACT,[],{TR,Subject}} has been {EXTRACT,**,{TR,Subject}}
Body {DB,Clients,First Name,”EMail Address” = ‘{TR,AddrFrom}’} {DB,Clients,Last Name,”EMail Address” = ‘{TR,AddrFrom}’} {EXTRACT,**,{TR,Subject}} your purchase request (# {EXTRACT,[],{TR,Subject}}) at {DATE} {TIME}.The decision he/she gave for this was:
{TR,Message}
Notification Module Descr SMTP Email
Client ID {DB,Purchase Requests,Client ID,”Req #” = {EXTRACT,[],{TR,Subject}} }

This is a slightly more busy and, for that matter, exciting business rule. The business rule does three things:

  1. Creates a Purchase History record, linked to the Purchase Request, showing what decision was made, by whom, and when. It also includes the body of the email as a potential reason as to why the decision was made.
  2. Updates the Purchase Request record, setting the status to be whatever the decision was.
  3. Sends an email notification to the requester, advising them of the decision made by the approver and whatever was in the body of the email as a reason.

Following on from that we have our fourth business rule which kicks in, only if the decision was APPROVED.

Purchase Request – Forwarded To Tech Group

Conditions

when a Purchase Requests

Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{TR,Status ID} NotEqual {CUR,Status ID} And
{DB,Purchase Requests,Status ID,”Req #” = {TR,Req #}} Equal APPROVED And
{DB,Purchase Requests,Group Code,”Req #” = {TR,Req #}} NotEqual TECH_SUPPORT None

Actions

Action 1

Method Module Form
Update Purchase Requests New Purchase Requests
Field Name Value
Req # {TR,Req #}
Group Name TECH_SUPPORT

Action 2

Method Module Form
Create Notifications Notifications
Field Name Value
Subject Purchase request # {TR,Req #} has been forward to your group
Body Purchase request # {TR,Req #} has been forward to your group. Please check the line items against current stock levels. Please delete any line items that can be provided from stock after the appropriate inventory item has been assigned to the user’s
configuration. If the remaining line items are to be provided by different vendors please select Actions > Split Purchase Request By Vendor (H) from the actions menu on the Purchase Request.Finally, create a Purchasing Action of ORDERED against each Purchase Request (one for each vendor) which will forward the request to the appropriate vendor.Thank you.Procurement Team
Notification Module Descr SMTP Email
Group Name TECH_SUPPORT

OK, so we are almost there. Our Technical Team have decided what items can be delivered from within stock without the need to re-order and have deleted those Purchasing Items from the Purchase Request. It is important however, at least in my ficticious organisation, that those deletions are recorded somewhere from an audit perspective. Roll-on our fifth business rule:

Purchase Request – Deleted Item

Conditions

when a Purchasing Items

Delete occurs withNo other conditionsActionsAction 1

Method Module Form
Create Purchase History New Purchasing History
Field Name Value
Note A purchasing line item was deleted by {MAGICUSER}:Part #: {CUR,Part #}
Description: {CUR,Description}
Qty Ord: {CUR,Qty Ord}
Unit Price: {CUR,Unit Price}
Amount: {CUR,Amount}
Date Performed {DATE} {TIME}
SeqPR {CUR,ReqN}
Description A purchasing item was deleted
Purchasing Action ID NOTE
Login ID {MAGICUSER}

Ok, so the final bit of functionality we need is to be able to forward the purchase request to the vendor, as a purchase order. I guess at this stage it makes sense to state the obvious here – this post is a demo of the art of the possible. I am not suggesting that what I am sending to the vendor in my example is a well thought through purchase order. That said, hopefully, it should be very easy to see how the examples could be adapted to include the necessary information that your vendors/organisation require e.g. delivery address etc.

So, once the technical team add the purchasing history action of ORDERED to the purchase request, the system will automatically send the request as a purchase order to the vendor for delivery.

Purchase Request – Forward To Vendor

Conditions

when a Purchase Requests

Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{TR,Status ID} NotEqual {CUR,Status ID} And
{TR,Status ID} Equal ORDERED None

Actions

Action 1

Method Module Form
Create Notifications Notifications
Field Name Value
Subject New Purchase Order # {TR,Req #}
Body <HTML>
<HEAD>
<TITLE>Service Desk Express – Vendor Purchase Order</TITLE>
</HEAD>
<BODY>
<P><STRONG><EM>Purchase Order #</EM></STRONG>: {TR,Req #}<BR /><STRONG><EM>Client</EM></STRONG>: {TR,First Name} {TR,Last Name}<BR /><STRONG><EM>Date Ordered</EM></STRONG>: {Date} {Time}</P>
<P>Please supply the following items:</P>
<TABLE BORDER=”1″ WIDTH=”75%”>
<TR>
<TD COLSPAN=”4″ STYLE=”font-weight: bold; color: white; background-color: #3366ff; font-variant: small-caps”>Line Items</TD>
</TR>
{MATH, (SELECT “_SMDBA_”.”GET_PR_LINE_ITEMS_HTML”({TR,Req #}))}
</TABLE>
<P>Many thanks,</P>
<P>Procurement Team.</P>
</BODY>
</HTML>
Notification Module Descr SMTP Email
Address {DB,Vendors,Vendor Email,”Sequence” = {TR,SeqVendor}}

So that is it for the business rules.

Training

Service Desk Express (like many other applications) is one of those applications that once you really know how to use it, seems really user-friendly – you just kind of learn to love to the little qwerks (either that or I have become a very sad person). Having said that, for those people who don’t know it and love it, in many ways it is not intuitive. The Purchasing Module is one of those areas that really needs to be trained. Making the likes of the Status ID field read only can be an excellent idea, but only if you have explained how Purchasing Actions work.

Anyway, I would suggest that your end-users (clients), including your approvers, probably don’t need much (if any) training. They already know how to ask for things!

Your Service Desk staff need to understand that by creating/updating an incident to a Subject ID of “PR” will cause a business process to take effect – you might even create a standard description for the Subject ID that provides the service desk team a template of information the procurement team will need.

Your Procurement Team need a whole bunch of training – the need to own the purchasing module! They need to understand how the Inventory Categories link to the Inventory Catalog etc. etc. Don’t skimp here. This team needs to be your biggest fan!

The Technical Team (in my example) will need a lot of loving! The last thing they want to be doing is creating purchase histories. They need to buy into why they are important in this process – one possible selling point is that they are acting as the gatekeepers of the network – no-one gets to buy stuff for the network without it going through them. This also means that users will ultimately get a better service as the technical team will be able to suggest compatible items etc.

Summary

So that is it – I hope it has proved helpful. Many people don’t realise the benefits of utilising the purchasing module when it comes to service delivery. Incorporated well, it can provide your organisation with a wealth of management information that can assist quality decision making. Clearly there is a whole bunch more stuff that we could do and indeed, stuff that we could do differently.

As always, comments are thoroughly encouraged – positive or negative.

Standard

Posted by

Posted on

August 22, 2007

Posted under

Service Desk Express

Comments

4 Comments

Making the purchasing module work for your organisation – Part 1

Like much in Service Desk Express/Magic (SDE), what you get out of the box with the Purchasing Module IS functional but lacks that certain “je ne sais quoi” that makes it really useful. What hopefully follows is a series of posts that will give those new to either SDE or the purchasing module some pointers on how to really get the most out of this module. Specifically, we will cover:

  • Business Processes
  • Roles/Groups
  • Database Customisations
  • Form Customisations
  • Data
  • Business Rules
  • and Training

Just to make this a little more challenging we will assume that your organisation does not have Client Services and consequently cannot use the Self-Service part of Purchasing. To be honest, in some respects that is a blessing (in my humble view of course) as it is anything but user-friendly!

Business Processes

Before attempting to use the Purchasing module, it is essential that you understand the business processes surrounding e-Procurement within your organisation in relation to Service Desk Express. As I said earlier, I am assuming that you don’t have Client Services and I want to make the business process complex enough to be real.

The process my ficticious organisation is going to follow is as follows:

  1. A user has a need for something and consequently, via Self-Service, telephone, or email, raises a request.
  2. That request is routed to the Procurement Team who check if the item is in the inventory catalog.
  3. If it isn’t then it needs to be sourced from an appropriate vendor who needs to be added to the system if not already in existence. Once sourced it can be added to the inventory catalog with its appropriate details including Sales Vendor ID.
  4. The procurement team will then raise the purchase request with the appropriate line items.
  5. Once complete the procurement team will send the request for authorisation from an appropriate manager in the business.
  6. If the manager rejects the request we will let the requester know and update the status of the purchase request to rejected. However, if the manager approves the request the status of the purchase request will be updated to approved and sent to the technical team for delivery.
  7. What is important here is that the technical team needs to decide if the requested item is already available in stores. If it is then the requested item is deleted from the request leaving any remaining items that need to be procured. A history entry needs to be added for any items that are deleted.
  8. The technical team will then send the purchase request to the vendor as a purchase order to deliver the remaining items and allocate any items from stores to the users configuration and delivered to them.
  9. Once the items have been delivered the items will be receipted and added to the user’s configuration before being delivered to them.

SDE Procurement Process 1

The above diagram shows a business process up to the point of procurement.

Roles/Groups

As can be seen from the above business process there are three roles/groups that need to be incorporated into the system:

  1. Procurement Group: It is important to control the catalog and related areas. As such this group should be the only group that has permissions to Insert/Update/Delete inventory categories, inventory catalog items, and vendors. In my organisation the procurement group is also responsible for raising the purchase requests themselves.
  2. The Approver: It is necessary to decide how you want to identify the approvers within your organisation. Generally, these people exist in the clients module of SDE but are identified as having additional powers! For example, it may be that you are simply given the status of approver and in which case there would be a boolean (Yes/No) field in the clients module that identifies these characters. Far too boring for my fictitous organisation – we are going to have a line manager per client and the request needs to go to the line manager of the requester for approval.
  3. The Tech Group: Assuming your organisation is such that they would like to re-use equipment as opposed to buying new all the time, you need someone who can assess the inventory against a given request and decide what can be sourced from stock. They then need to remove the purchasing items from the purchase request and assign the inventory item from stock to the requester. Theoretically, this could be your procurement group but I don’t recommend it for two reasons:
    1. If you operate off multiple sites there is no way a central procurement group would know if an asset was, as a result of another request, about to made unavailable. Consequently, a situation could arise where the item was deleted from the purchase request and then not available from stock.
    2. Secondly, one would hope that your technical group have technical skills! As such whilst a client might request a Dell D610 Laptop, a technician might be able to provide an alternative Dell C610 instead that we have in stock.

Database Customisations

There are very few database customisations required to make this work. The main one is to identify the approvers – so we need to add a new foreign key field in the clients module called Seq.Manager that references the clients module. Assuming that anyone following this already has a bunch of clients in the system you wont be able to prevent nulls so I would suggest that you should check “Required if on form” to all the additional fields specified.

Once that foreign key is created we need to create a virtual fields inside it referencing the Client ID, First Name, Last Name, and Email Address of the manager. I suggest you call these fields something like Manager Client ID, Manager First Name etc.

To make life easier we are also then going to add some new virtual fields to the existing Seq.Client foreign key in the purchase requests module. The fields we want to add are the new fields we just added to the client module i.e. Manager Client ID, Manager First Name etc.

An additional field I would suggest, although not necessary is to add a Cost Code field to the purchase request module – perhaps a string of 255 characters. Most organisations I have worked for want to assign costs of purchases to internal codes.

We also need to add an additional field into the vendor module called Vendor Email of type Misc > Email. We will use this, not surprisingly, to hold the Vendor’s email address so that we can send the purchase request directly to them.

Form Customisations

We need to create four new forms (or modify existing ones if you already have them):

  1. New Client: A new copy of the client form with our new fields added to them to allow the line manager for each of our clients to be selected.
  2. New Purchase Request: A new copy of the purchase request form where we can add our new cost code field and the additional Manager Client ID, Manager First Name etc. if you want. This form is not necessary if you didn’t add the cost code field.
  3. New Vendor: A new copy of the vendor form with our new Vendor Email address field added.
  4. New Purchasing History: A new copy of the purchasing history form adding the Note field that is included out of the box – all will become apparent later…

Data

In order to make this all work we need to add specific records of data. Don’t worry if you already have similar as you can substitute my values for yours:

  1. Purchase Statuses: We want to be able to control the business process and identify where in the process a given request is. To do this we need the following Purchase Statuses:
    1. Open, For Approval, Approved, Rejected, and Ordered. These are the minimum for my fictitious organisation. We will also create Cancelled, Received Part and Received Full for reasons that will become apparent later.
  2. Purchasing Actions: A little more complicated, but in order to lead our procurement and tech groups through the process with the minimum of training, these are essential:
    1. Find the existing Open action and update it to show the new status of Open.
    2. Create a new action of For Approval with an old status of Open and a new status of For Approval.
    3. Create a new action of Approved with an old status of For Approval and a new status of Approved.
    4. Create a new action of Rejected with an old status of For Approval and a new status of Rejected.
    5. Create a new action of Ordered with an old status of Approved and a new status of Ordered.
    6. Open the existing action Received Part and update the old status to Ordered and the new status to Received Part.
    7. Open the existing action Received Full and update the new status to Received Full.
    8. Create a new action of Cancelled without an old status and a new status of Cancelled.
    9. Finally, create a new action of Note with no old status and no new status. We are going to use this to log the deleted line items.
  3. Support Subjects (Categories): Create a new support subject for Purchase Request. For the purpose of my fictitious organisation the Subject ID will be “PR”.
  4. Groups: Create a new Procurement group and a new Tech group. Again these can be whatever work for your organisation.

So that is it for the business processes, groups and roles, database and form customisations, and required data records. In the next part of this series I will cover the remaining bits of business rules and training.

Standard

Posted by

Posted on

August 7, 2007

Posted under

Service Desk Express

Comments

Leave a comment

Validated fields – friend or foe?

Whenever you require a field on a form that has a definitive list of values to choose from, a Service Desk Express administrator is always faced with either creating a dedicated module and foreign key reference or a validated field.

So what is a validated field?

A validated field is a text field (or more accurately in database speak – a varchar field) of a size able to accommodate the largest option. The options, added through either the DB administration tool or the customisation wizard, are enforced using a check constraint on the column created. As such, even if you try to insert the data direct into the table using, for example SQL Enterprise Manager, you cannot insert a value other than those allowed.

Accessing the values available of a validated field?

There are times when a developer or dba might want to programmatically determine what the options are of a given validated field. This is remarkably easy once you know what table to look in for the information:

SELECT BVD.[SMTYPE] FROM dbo.SMSYSBASEVIEWDATA BVD JOIN dbo.SMSYSTABLES T ON T.[TBLSEQ] = BVD.[TBLSEQ] WHERE T.[SYSTBLNAME] = '_CONFIG_' AND BVD.[COLNAME] = 'NETWORK'

The query above, for example, will return all the options of the network field from the _SMDBA_._CONFIG_ table. You simply replace the tablename and the column name to get your options.

When to use a validated field vs. a dedicated module and foreign key relationship?

To answer the above question you need to understand some of the advantages and disadvantages of using validated field and dedicated modules:

  • Performance
    • Validated fields are significantly more efficient at reading data than foreign key relationships as a join is required to retrieve the information from the appropriate table in the case of the foreign key. Whilst the effect of this can normally be reduced using an INNER JOIN if the foreign key column is mandatory, Service Desk Express does not make use of these efficient joins in its views and consequently performance is affected.
  • Re-Use
    • Validated fields cannot be re-used as they are table specific. Consequently if, for example, you have a list of locations a validated field would probably be a bad choice as you may want to use this list in a variety of modules e.g. Incidents and Work Orders. In this example, a dedicated module with foreign keys from both the Incidents and Work Order modules would be a much better bet as you would only need to update the list of locations once and both foreign keys would be reading the new data.
  • Number of options
    • If you have a lot of options then a validated field is almost certainly a bad choice. Particularly with advances in Service Desk Express that allow you to show foreign key fields as drop down lists (emulating validated fields) using form customisation, validated fields are usually only sensible with less than 10 records.
    • Validated fields provide no way for the end-user to sort the results (only what the system administrator has decided the order should be)
    • Validated fields provide no way for the end-user to search for the value other than scrolling through the list.
  • Setting Up
    • Without a shadow of a doubt validated fields are much each to create and use QUICKLY than a dedicated module and foreign keys. When you create a dedicated module you need to
      • Create the form to maintain the records in the dedicated module
      • Modify the popup to allow retrieval of the records
      • Add the form to the appropriate navigator bars
      • Update any forms that use this module
  • Simplicity
    • Not that this should be a reason per se but, validated fields are easier for novice Service Desk Administrators to get their heads around and make use of.
  • Maintenance
    • Whilst maintaining the list of options in a validated field has become considerably easier using Service Desk Express, if your options are likely to be changing frequently a dedicated module is a much easier solution to maintain.

Summary 

So, in answer to the above question of which should you use:

  • Use a validated field if you have less than 10 options which are unlikely to change and will only ever be used in one module. Validated fields also offer a significant performance boost where the number of records in the table is very large.
  • Use a dedicated module if you have more than 10 options, or the options are likely to change, or the options will be needed in more than one module.

 As always, thanks for reading and I welcome any comments.

Standard

Posted by

Posted on

August 3, 2007

Posted under

Service Desk Express

Comments

4 Comments

Purchase Request Line Items in Email

One of the difficulties with the Purchasing Module that I have found is the inability to easily send the request to the Vendor (or for that matter the customer/approver) including the line items. Well everything is possible of course so I thought I would post a solution for comment/criticism:

You need to create a simple SQL function something like the one attached: Function GET_PR_LINE_ITEMS

Then grant permissions to Public to Execute this function.

Finally use the function in your business rule e.g. When PR is updated with a change of status from Approved to Ordered send email.

In the body of the email you would then have:

Line Items:
{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS"({TR,Req #}))}

What basically happens is that the function concatenates all the line items into a single string that can then be used in the body of an email.

I have also uploaded created a function that returns an HTML table row for every line item: Function GET_PR_LINE_ITEMS_HTML. To use this function you would, in the body of the notification you would have…

<table border="1"><tr><td colspan="4">Line Items</td></tr>{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS_HTML"({TR,Req #}))}</table>

Hope this maybe of use.