Understanding the Purchase Request fields and how they are calculated

Before using the Purchase Request module it is important to understand some of the more obscure fields and how the totals are calculated. So the purpose of this post is to do just that:

Sub Total

The Sub Total is surprisingly enough quite straightforward. The code for it is:

(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."_INACTIVE_:" = 0)

So essentially, all that is happening here is that we are summing all the prices of the active Purchasing Items for this Purchase Request.

Total

The simplicity of the Sub Total field is not however mirrored in the Total field. The code for it is:

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

So what is going on here? Well the key lies in the CI Type module (formerly known as Inventory Catalog). Each Purchasing Item (that we are summing) is of a certain CI Type and each CI Type has a flag that determines if that CI Type is Taxable or not. Now depending on whether the Purchasing Item is of a taxable CI Type or not determines whether the Tax field (in the Purchase Request module) gets added on or not. So let’s say you have a Purchase Request with two line items each costing $100. One of the line items is taxable (as determined by the CI Type) and the other is not, and you have set a tax value of 9%. The Total would be:

(100 * 1.09) + (100) = $209 + Shipping (Not Taxable)

This is rather clever but if you don’t know about it you can have a lot of head scratching trying to understand why your Totals don’t match what you think they should.

There is a significant gotcha with this system. Namely, this field is Nullable which means that if you happen to remove the field from the CI Type form then when you save a CI Type, that field is left as Null. Now, when the Purchase Request Total field attempts to sum values and returns a Null for Taxable, the entire Purchase Total becomes 0 – so whoever made that decision should be shot!

Summary

With an understanding of how these fields are calculated and the little gotchas that go along the way you should hopefully be in a better position when you are implementing/understanding Purchasing in Service Desk Express. I have included so links to other relevant articles on Purchasing below. As always, hope this has been helpful. Any comments, positive or negative, are incredibly welcome.

Related Posts:

Advertisements

Forward Schedule of Change V2.0

Version 2.0 of the Forward Schedule of Change application is nearing completion and should be available before the end of the month. Some of the changes that have been incorporated include:

  • Full screen change calendar with change details on separate page.
  • Change details now include Change History, Change Assessments and Change Approvals (visibility of which can be toggled on and off by the application administrator).
  • Links to update changes (visibility of which can be toggled on and off by the application administrator).
  • New search functionality allowing the searching of selected fields.

The new layout, removing the change details panel on the right hand side of the application, will not be to everyone’s liking and as such V1.0 will still be available if organisations prefer the old style.

As always, feedback (positive or negative) is always welcome.

HTML Emails in SDE

A lot of people ask about sending email notifications from SDE in HTML format.  This is a great way to make your notifications consistent and easy to read for your users.  It also works very well for including links in the email for your users to click.  There aren’t too many email clients that won’t display HTML emails these days, although I have in the past had slight problems with Lotus Notes where tables don’t display exactly how you expect.  It is not a show-stopper and usually can be remedied with some minor tweaking specific to the way Notes handles HTML.  Also, certain versions of BlackBerry devices do not display HTML at all, or not very well – especially HTML links.

Business Rules Templates used in conjunction with some simple HTML and Cascading Style Sheets will make the notifications look very professional and consistent.   At the end of this post I will provide some downloadable Business Rules Templates that you can use in your own rules and tweak as needed.  Let’s dive right in and get started.

The basic requirement for having emails sent by SDE display in HTML format is to include the <HTML> and </html> tags around the body of the notification.  HTML formatting in email subjects is not supported by email clients, however.  Here is an example of a simple Incident notification in HTML format:

<HTML>
Incident #{TR,Incident #} has been created for you.<BR>
<BR>
<B>Open Date & Time:</b> {TR,Open Date & Time}<BR>
<B>Category:</b> {TR,Subject Description}<BR>
<B>Urgency:</b> {TR,Urgency ID:}
</html>

Remember to always use the HTML line break tag <BR> rather than just pressing enter in the notification.

The above is a very basic example of how to use HTML in notifications.  However, what if you have multiple Incident notifications and want to use the same format in all of them?  You could put the HTML into each notification body, but then if you ever want to make changes you are stuck modifying multiple rules.   This is where Business Rules Templates come into play.  Here is the template I use at the beginning of all of my notifications – I name it _HTML_HEAD:

<HTML>
<HEAD>
<STYLE type=”text/css”>
body,table,td,th{
font-family:arial,helvetica;
font-size:10pt;
}
table,td,th{
border:1px solid #000;
border-collapse:collapse;
margin:0;
padding:0;
text-align:left;
}
td,th{
padding:.2em .5em;
vertical-align:top;
font-weight:normal;
}
tbody td{
background:#ccc;
}
tbody th{
background:#aaa;
font-size:9pt;
font-weight:bold;
}
pre{
font-family:arial,helvetica;
}
</style>
</head>
<BODY>

This provides very nice formatting for including information in a table format.  I create an _HTML_FOOT template also:

<BR><CENTER><HR width=”80%” size=”1″ noshade>
<FONT color=”silver” size=”1″>{EVENTMODULE} {EVENTMETHOD} / {RULENAME}</font>
</body>
</html>

Putting this at the end of every notification will provide you with information for troubleshooting purposes, including information about the event and Business Rule that generated the notification.  Note that the module you choose for the HTML templates does not matter.  Since no actual fields from any module are used in them, you can use them for notifications based on Incidents, Work Orders, Change Requests, and any other module.

Here is the standard Incident template I use for notifications, which I name _INCIDENT:

<TABLE width=”80%”>
<TR>
<TH width=”150″>Incident #</th>
<TD>{TR,Incident #}</td>
</tr>
<TR>
<TH>Opened</th>
<TD>{TR,Open Date & Time}</td>
</tr>
<TR>
<TH>Client</th>
<TD>{TR,Client ID} – {TR,First Name} {TR,Last Name}</td>
</tr>
<TR>
<TH>Phone</th>
<TD>{TR,Cl.Phone #}</td>
</tr>
<TR>
<TH>Email</th>
<TD>{EXTRACT,{},{TR,Client Email}}</td>
</tr>
<TR>
<TH>Company</th>
<TD>{TR,Company Name}</td>
</tr>
<TR>
<TH>Department</th>
<TD>{TR,Department Name}</td>
</tr>
<TR>
<TH>Category</th>
<TD>{TR,Subject Description}</td>
</tr>
<TR>
<TH>Urgency</th>
<TD>{TR,Urgency ID:}</td>
</tr>
<TR>
<TH>Assigned Staff</th>
<TD>{TR,Login ID Assigned To} – {TR,First Name Assigned To} {TR,Last Name Assigned To}</td>
</tr>
<TR>
<TH>Assigned Group</th>
<TD>{TR,Group Name}</td>
</tr>
<TR>
<TH colspan=”2″>Description</th>
</tr>
<TR>
<TD colspan=”2″><PRE>{TR,Incident Description}</pre></td>
</tr>
</table>

Notice I use the <TH> and <TD> table tags in conjunction with the style sheet to control the formatting.  Putting these templates together into a notification is very simple.  I usually include descriptive text unique to each rule so that the person receiving the email will understand why it was sent to them:

Incident Notification with HTML and templates

Here is how the notification looks to the person receiving it:

Incident Notification Result

With SDE’s ability to generate a link that takes a Staff member to the application and opens a particular record, it is common to include the links in email notifications.  However, you don’t want the Clients seeing these links because they won’t work.  Because of this, I create separate templates for the links.  Here is the template I use for Incident links, appropriately named _INCIDENT_LINKS:

<A HREF=”{LINK,Incident,{TR,Incident #},}”>Click here</a> to view this Incident in SDE.

This is so simple it seems like using a template for this is overkill.  Again, remember that you may include this template in many rules and if you ever want to make a change to it you’ll only have to make that change to the template.  My template _WORKORDER_LINKS also includes a link to the parent Incident (if one exists).  This is an example of the fact that MATH statements, and of course DB Lookups, can be used in templates as well.

<A HREF=”{LINK,Work Orders,{TR,Work Order #},}”>Click here</a> to view the Work Order in SDE.<BR>
{MATH,(CASE WHEN ‘{TR,Seq.HD #}’ <> ” THEN ‘<A HREF=”{LINK,Incident,{TR,Seq.HD #},}”>Click here</a> to view the parent Incident in SDE.’ ELSE ” END)}

The above template will always show a link to the Work Order, but the MATH statement will only display the link to the parent Incident if there is one.

The last aspect of templates I want to discuss is the context of the Business Rule and template.  This is very important.  As an example, suppose you want to trigger a Business Rule from the creation of an Incident Detail with a particular Action ID.  If you want to include the Incident template in a notification from this rule the problem is that you can’t just use {TR} fields from the Incident – the context of the rule is Incident Details, not Incidents.  For this reason, I create a template named _INCIDENT_LOOKUPS:

<TABLE width=”80%”>
<TR>
<TH width=”150″>Incident #</th>
<TD>{TR,Incident #}</td>
</tr>
<TR>
<TH>Opened</th>
<TD>{DB,Incident,Open Date & Time,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Client</th>
<TD>{DB,Incident,Client ID,”Incident #” = {TR,Incident #}} – {DB,Incident,First Name,”Incident #” = {TR,Incident #}} {DB,Incident,Last Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Phone</th>
<TD>{DB,Incident,Cl.Phone #,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Email</th>
<TD>{EXTRACT,{},{DB,Incident,Client Email,”Incident #” = {TR,Incident #}}}</td>
</tr>
<TR>
<TH>Company</th>
<TD>{DB,Incident,Company Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Department</th>
<TD>{DB,Incident,Department Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Category</th>
<TD>{DB,Incident,Subject Description,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Urgency</th>
<TD>{DB,Incident,Urgency ID:,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Assigned Staff</th>
<TD>{DB,Incident,Login ID Assigned To,”Incident #” = {TR,Incident #}} – {DB,Incident,First Name Assigned To,”Incident #” = {TR,Incident #}} {DB,Incident,Last Name Assigned To,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Assigned Group</th>
<TD>{DB,Incident,Group Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH colspan=”2″>Description</th>
</tr>
<TR>
<TD colspan=”2″><PRE>{DB,Incident,Incident Description,”Incident #” = {TR,Incident #}}</pre></td>
</tr>
</table>

As you can see, this template uses the Incident # to lookup all the Incident fields.  It is simply a coincidence that the foreign key in the Incident Details module is called Incident #.  This is, however, a useful coincidence.  If you are doing time-based rules where the notification is delayed, you always want to use DB Lookups to get the most current data when the notification is sent.  Since {TR,Incident #} is a valid field in the Incident module, you can use this lookup template in those notifications.

Finally, here is a link to a set of text files that contain the basic templates I like to use.   You can modify them to suit your needs, of course.  Keep in mind that in the Change Approval template I have used some fields that aren’t available in the module by default.  You’ll either have to remove them from the template, change them to DB Lookups, or add them as virtual fields to the Change Approval module (which is what I do).

Downloadable Business Rules Templates

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

In part 1 of this series (http://www.joatit.com/wordpress/?p=539) I described the relationships and use of the modules associated with the CI Assemblies/Configurations module. I also described how I would implement a real-world example using these modules and eluded to the need to automate some functionality through business rules and be aware of certain issues when it comes to reporting.

In this post, I want to cover off those points and hopefully provide some additional resources to help you get the most out of the product

Business Process Automation

In the first post in this series I suggested it would be a sensible idea to have a CI Assembly/Configuration for each client to manage their mobile assets. Fine, but what a pain in the proverbial if for every client you create you have to manually create a CI Assembly/Configuration and then link it to that client! At this point I would normally suggest a business rule but unfortunately we have a problem – namely Service Desk Express does not allow you to create business rules based on the Assembly Clients module which would mean we could create the CI Assembly/Configuration but couldn’t link it to the client. So we need an alternative – enter the Integration Console:

CC – Create and Link CI Assemblies to Clients (click here to download).

What we need to do to achieve this is create a package that contains a step and a substep:

cc-create-and-link-ci-assemblies-to-clients-01

Initiator:

We will set the Initiator to be a schedule running every 15 minutes (as shown below) such that within 15 minutes of creating a new account your CI Assembly/Configuration will be created and linked.

cc-create-and-link-ci-assemblies-to-clients-02cc-create-and-link-ci-assemblies-to-clients-03

Source:

The key to this package is the source for the Create CI Assemblies Step. We need to run a SQL query to only return the clients that don’t have a matching CI Assembly/Configuration so we need to use the ODBC adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-031cc-create-and-link-ci-assemblies-to-clients-04

using the SQL query:

SELECT C1.[CLIENT], C1.[COMPANY], C1.[DEPT], C1.[BILL TO], C1.[BLDNG], C1.[OFFICE] FROM [_SMBDA_].[_CUSTOMER_] C1 LEFT JOIN [_SMDBA_].[_CONFIG_] C2 ON C1.[CLIENT] = C2.[CONFIG] WHERE C2.[CONFIG] IS NULL AND C1.[_INACTIVE_:] = 0

Target:

We then map the target to the CI Assembly view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-05cc-create-and-link-ci-assemblies-to-clients-06cc-create-and-link-ci-assemblies-to-clients-07

So that creates the CI Assemblies/Configurations for the Clients – now we need the sub step to link them to each other.

Initiator:

As can be seen below we only want the ub step to run if the parent succeeds:

cc-create-and-link-ci-assemblies-to-clients-08

Source:

None/Not used.

Target:

Finally we map the target to the Assembly Clients view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-09cc-create-and-link-ci-assemblies-to-clients-10cc-create-and-link-ci-assemblies-to-clients-11

This will create and link a CI Assembly for every client that does not already have one based on matching the Client ID with CI Assembly ID  as mentioned in my previous post.

Orphaned Configurations and CSBRs

I wrote a post a while back on how to warn users if the CI Assembly/Configuration they are saving is NOT linked to a client. I would also suggest that you implement this business rule as well.

Reporting

Now if you have implemented something similar to what I suggested in the previous post there are a couple of issues relating to reporting that you need to be aware of. Specifically, if management ask for a list of all assets and their users you need to be very careful how you handle the many to many relationship between Clients and CI Assemblies otherwise you could end up with duplicate assets in the cases when an asset is used by multiple clients. So what are your options:

Exclude duplicates

Your first option is to exclude these circumstances altogether. Let me explain:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description], CC1.[Client ID], CC1.[Configuration ID]
FROM [_SMDBA_].[Inventory Items] II LEFT JOIN [_SMDBA_].[Configurations] C ON C.[Sequence] = II.[Seq.Configuration]
LEFT JOIN [_SMDBA_].[Configurations Clients] CC1 ON CC1.[Seq.Configuration] = C.[Sequence]
WHERE II.[InActive:] = 0 AND (SELECT COUNT(*) FROM  [_SMDBA_].[Configurations Clients] CC2 WHERE CC2.[Seq.Configuration] = CC1.[Seq.Configuration]) < 2

The advantage of this is that your report is accurate in terms of assets as long as you appreciate that your assets do not include shared assets. The disadvantages are pretty obvious in that gaining a holistic view of your assets using this method is almost impossible and as such I would not recommend it.

Show first user only

An alternative solution is to only show the asset once and assume that the first person sharing an asset is the person responsible for it. This is done using an embedded SELECT statement in your report:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description],
(SELECT TOP 1 CC.[Client ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Client ID',
(SELECT TOP 1 CC.[Configuration ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Configuration ID'
FROM [_SMDBA_].[Inventory Items] II
WHERE II.[InActive:] = 0

The big advantages here are that there is no customisation required and critically the correct number of assets are shown on report (including shared assets). The big disadvantage is that it makes a very large and often incorrect assumption – i.e. that the first person sharing an asset is the person responsible for it. This simple isn’t true in many circumstances and if you are using this information for billing then you’re potentially opening up a whole can of worms. Again, not my preferred option.

Add Person Responsible field and CSBR

My preferred option is to add a new field (Seq.Responsible) to the Inventory Item/Configuration Item module – a foreign key to the Client module (obviously with its relevant virtual fields) that is used to store the Person Responsible for the asset as shown below:

responsibleforeignkey

Add this field on to your Inventory Item/Configuration Item form and you can then create a simple CSBR that on change of Configuration/CI Assembly, prefill the Person Responsible with the first person linked to that Configuration/CI Assembly. The massive advantage here is that although the person is prefilled you have the ability to overrule the suggestion. What is more you can even select a departmental manager who does not use the shared asset in question. Now you have the ability to report directly off the Inventory Item/Configuration Item module giving you accurate asset information that CAN be used for billing without arguement.

CSBR: CI – Prefill Responsible Client On Change Of CI Assembly

When On Data Change occurs for field CI Assembly ID where {TR,CI Assembly ID} NotEqual <Blank>
Populate Current Form - JQS - Configuration Item (the new form with the Seq.Responsible field added) setting Seq.Responsible = {DB,Assembly Clients,Seq.Client,"Seq.CIAssembly" = {TR,Seq.CIAssembly} DB}
Assigned To JQS - Configuration Item

Summary

CI Assemblies provide a highly flexible solution to the management of assets in Service Desk Express but it is essential that robust business processes are put in place to combat the risk of poor data. As always comments, positive or negative, are always welcome.