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 