Ever wondered how to communicate with all assessors and/or approvers of a change request that the status of a change has changed? If the answer is “Yes” you will, probably before now, have realised that this is not as straightforward as it seems.
What I hope to achieve in this post is to walk you through how you would achieve this. The problem lies in Service Desk Express’s lack of functionality to “iterate” through a dataset and complete an action for each record it finds.
Solution
The solution is courtesy of a little SQL magic – we are going to create a user-defined function that iterates through each of the assessors and builds a string of their Login IDs such that we can give this to a Service Desk Express business rule to send an email:
CREATE FUNCTION [_SMDBA_].[GET_CHANGE_ASSESSORS]
(
@ChangeNo int
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)
SET @ReturnString = ''
DECLARE @LoginID nvarchar(30)
DECLARE cur CURSOR
FOR SELECT DISTINCT P.CODE FROM _SMDBA_._CHANGEASMT_ CA JOIN _SMDBA_._PERSONNEL_ P ON P.SEQUENCE = CA.ASSESSOR WHERE CA.[_INACTIVE_:] = 0 AND P.[_INACTIVE_:] = 0 AND CA.[CHANGE] = @ChangeNo
OPEN cur
FETCH NEXT FROM cur INTO
@LoginID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @ReturnString = @ReturnString + @LoginID + ';'
END
FETCH NEXT FROM cur INTO
@LoginID
END
CLOSE cur
DEALLOCATE cur
RETURN SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1)
END
It is important that we grant execute permissions to this function:
GRANT EXECUTE ON [_SMDBA_].[GET_CHANGE_ASSESSORS] TO PUBLIC
Then we just need a simple business rule that, on change of status for example, sends and email to all assessors:
Change Request -Notification of Change of Status To Assessors
Conditions
when a Change Request
Update occurs with
| Expression 1 | Comparison Operator | Expression 2 | Logical Operator |
|---|---|---|---|
| {CUR,Status ID} | NotEqual | {TR,Status ID} | None |
Actions
Action 1
| Method | Module | Form |
|---|---|---|
| Create | Notifications | Multi-Notifications |
| Field Name | Value |
|---|---|
| Subject | Service Desk Notification – Change Request Status Update |
| Body | <HTML> <HEAD> <TITLE>Service Desk Notification – Change Request Status Update</TITLE> </HEAD> <BODY> <P>Add some nice text here</P> </BODY> </HTML> |
| Notification Module Descr | SMTP Email |
| Address Staff Multiple | {MATH,(SELECT _SMDBA_.GET_CHANGE_ASSESSORS({TR,Sequence}))} |
Summary
User-defined functions can be a goldmine of pieces of functionality that is missing from Service Desk Express. So before you despair…think UDF! As always I hoped this post proved useful. Feedback, positive or negative, is always hugely appreciated.
JOATIT 
Great site. The IT group I’m in started using SDE in March and we’re working our way through customizing it as we go along.
One thing I wish we could do is lauch differing forms from a quickview on the same module…based on the Work Type field.
There is a preventative work order and a normal work order. They have different forms, but a quickview that shows them both lauches the default work order form…
Hi R3,
Many thanks for the compliments. Much appreciated. I agree that it would be good to have a similar type functionality to Incident Types with Work Orders and other modules. I think there is a vendor who has done some custom work with that but can’t remember who.
The closest you can get out of the box is to have a single Work Order form and then use CSBR’s to change the look of the form based on the value of the Work Type.
Best regards,
Alan
Been trying to create a UDF based on your example here but could not do it. I need to notify members of a group that a particular incident has been picked up by one of their members (Assign to Myself). Could you give me ideas on how to go about this? Thank you.
Hi Eric,
I don’t think you need a UDF to do this as all the people you want to notify are in the same Group which is supported out of the box. Create a business rule that on Update of an Incident with {TR,Assigned To Login ID} NotEqual {CUR,Assigned To Login ID} AND {TR,GroupName} Equals {MAGICUSERSGROUP}; Send Notification to {MAGICUSERSGROUP} which will be the group name of the logged on user’s group.
I think that should cover it. Let me know how you get along.
Regards,
Alan
Thank you Alan.
Doing what you have suggested will also notify the staff who assigns the incident to himself. I was looking into implement a UDF that will exclude the assigned to staff from being sent a notification.