I remember when I first got involved in SDE or Magic, as it was known back in 2002, the ability to build business rules based on events that resulted in, amongst other things, emails being sent to support staff blew me away! I had a wail of a time the following month building LOTS of business rules that sent emails all over the place. Shortly after that, Support Staff fell into two categories: those that we pleased at the level of communication / audit that the service desk software could provide and those that hated my guts! Not everyone likes having their Inbox filled it would appear.
Recently I was asked to develop a way that Support Staff could opt in to receiving emails such that, in the event of a ticket being assigned to a group, only those staff in that group who have opted in, receive an email notification. This is one of those requests that when you know how to do it it is not too bad…figuring it out on your own if you don’t know what Service Desk Express is capable of is a whole different story. So let me walk you through how you would go about this based on some previous posts and then, in subsequent posts, I show you how you can use this to create an email driven service desk.
Identifying the opted in staff
So the first requirement is to identify the opted in staff. For that, the simplest solution is to create a new field in the Support Staff module as follows:
| Setting | Value |
|---|---|
| Name | Send Email |
| Type | Numeric – Boolean |
| Default | False |
Add this field to your Support Staff form and you now have a way to identify them…
A little SQL function…
The next issue is that we need a way to use this field in a normal business rule to build a list a of those support staff who want to receive notifications. For that we need a little SQL function created as described in this earlier post. This new function will be called GET_OPTED_IN_SUPPORT_STAFF:
USE SDE GO CREATE FUNCTION "_SMDBA_"."GET_OPTED_IN_SUPPORT_STAFF" ( @GROUP 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_"."_PERSONNEL_" P JOIN "_SMDBA_"."_GROUPDET_" GD ON GD."_MEMBER_" = P."SEQUENCE" WHERE GD."_GROUP_" = @GROUP AND P."SEND_EMAIL" = 1 AND P."_INACTIVE_:" = 0 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 IF LEN(@ReturnString) > 0 BEGIN SELECT @ReturnString = SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1) END RETURN @ReturnString END
UPDATED: If you look in the comments you will see a comment from Raymond Beijerling with an updated version of the SQL function that will improve performance and resilience.
Once we have created this function, it is important that we grant execute permissions to it:
GRANT EXECUTE ON [_SMDBA_].[GET_OPTED_IN_SUPPORT_STAFF] TO PUBLIC
Finally we just need a simple business rule that, on change of group assignment for example, sends an email to all opted in support staff:
HD – Notify Opted In Group Members When Incident Assigned To Group
Conditions
when a Incident Create Update occurs with
| Expression 1 | Comparison Operator | Expression 2 | Logical Operator |
|---|---|---|---|
| {DB,Incident,Group Name,”Incident #” = {TR,Incident #}} | NotEqual | {CUR,Group Name} | And |
| {DB,Incident,Group Name,”Incident #” = {TR,Incident #}} | NotEqual | And | |
| {MATH,(SELECT LEN(_SMDBA_.GET_OPTED_IN_SUPPORT_STAFF({TR,Seq.Group})))} | GreaterThan | 0 |
Actions Action 1
| Method | Module | Form |
|---|---|---|
| Create | Notifications | Multi-Notifications |
| Field Name | Value |
|---|---|
| Subject | Service Desk Notification – Incident Assigned To Your Group |
| Body | <HTML><HEAD><TITLE>Service Desk Notification – Incident Assigned To Your Group</TITLE></HEAD><BODY><P>Add some nice text here</P></BODY></HTML> |
| Notification | SMTP Email |
| Address Staff Multiple | {MATH,(SELECT _SMDBA_.GET_OPTED_IN_SUPPORT_STAFF({TR,Seq.Group}))} |
Summary
That’s pretty much it…Now no matter what group a support staff member is a member of, if they have their Send Email flag set then they will receive emails when an incident is assigned to their group. As always I hoped this post proved useful. Feedback, positive or negative, is always hugely appreciated.
JOATIT 
A perfect example why SDE is so powerful and flexible. Thanks for sharing!
Hi Alan,
Very nice of you to share this one.
I would like to add some comments, to make the sql a little more bulletproof.
I would use nvarchar(max) as the type for @ReturnString, so there is no limit of characters.
You could leave the cursor out and increase performance by using:
IF ISNULL(@GROUP,0) 0 –- check if groupseq is passed
BEGIN
SELECT @ReturnString = ISNULL(@ReturnString +’; ‘, ” ) + [CODE]
FROM “_SMDBA_”.”_PERSONNEL_” P
JOIN “_SMDBA_”.”_GROUPDET_” GD
ON GD.”_MEMBER_” = P.”SEQUENCE”
WHERE GD.”_GROUP_” = @GROUP
AND P.”SEND_EMAIL” = 1
AND P.”_INACTIVE_:” = 0
END
And finally for custom functions I prefer them to have the same security as other SDE objects so:
GRANT EXECUTE ON [_SMDBA_].[GET_OPTED_IN_SUPPORT_STAFF] TO SMSYSPUBLIC
This way they can only be executed by SDE USERS
Hope this helps.
Raymond Beijerling
Sr. Technology Consultant
@Raymond,
Many thanks for posting this. Always happy to take improvements – very cool. I will leave my post as is with the exception of adding a little reference to the comments section. That way the post and the comments make more sense.
Regards,
Alan
Thanks Alan, but I am a little confused how this differs from the Staff Availability window. Staff can disable email checkboxes when they change their password.
@faylesworth,
Really valid point particularly with the scenario I have described. However, what this post will give you is an ability for the BUSINESS to control who gets what emails overriding user availability. I remember when I first starting using Magic 7.5, some of this functionality existed then (i.e. User Availability) but, unless I am mistaken, there was never an option to be Available but not receive emails – I might be wrong about that though.
Regards,
Alan
Alan,
Many thanks for this! It will be very helpful. So glad to see you are posting again! 🙂 Your site has been a valuable resource!
Regards,
AJ