Allowing Staff To Opt In To Emails

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.

Advertisements

6 thoughts on “Allowing Staff To Opt In To Emails

  1. 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

  2. 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

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s