Introduction
In many business processes there are opportunities to automate meeting requests (aka appointment scheduling). Might it be appointments for a group of service technicians, scheduling a fleet of trucks or confirmation of a meeting made by a customer call center.
With Boomerang Notification Framework you may with a few easy SQL statements request, cancel and track responses for a meeting according to the iCalendar file format standard (RFC 5545).
Currently the core function VCALENDAR is supported which allows you to schedule (METHOD:REQUEST) and cancel (METHOD:CANCEL) meetings in most calendar applications like Google Calendar, Microsoft Outlook and IBM Lotus Notes.
Automate Meeting Requests in 3 Easy Steps
The meeting scheduling feature in Boomerang Notification Framework is an addition to the Email Out feature. The specifics of of the meeting, for example the start date and time, end date and time, location, meeting subject, will be sent to a email recipient as an attachment with the file extension ”.icf”.
These types of file attachments are recognized by most calendar applications and will be presented (rendered) as a meeting request (appointment).
Create Event | Step 1
In the first step we create a new task record. This is done by inserting a new row in the table EVENT_MASTER. The only required field in EVENT_MASTER is the gKey (uniqueidentifier). This record key will tie output jobs together as we will see in further steps. There are several columns in EVENT_MASTER that you may use to help you organize and manage different tasks. In this example we will use Str1 and use it to describe what kind of event this is.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- declare @gKey uniqueidentifier, @jKey uniqueidentifier, @aKey uniqueidentifier set @gKey = NEWID() set @jKey = NEWID() insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Automate Meeting Requests') --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Create Email Job | Step 2
Since the appointment details are delivered as a .ics attachment to an email we are going to first create a new Email Out job. We do this by inserting a new record into the OUT_EMAIL table.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- insert OUT_EMAIL (gKey, jKey, Subject) values (@gKey, @jKey, 'Meeting Request') --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Schedule Meeting Request | Step 3
To schedule the actual appointment we are going to use the built in stored procedure called sp_Add_vCalander. This stored procedure makes the scheduling of the appointment easy. We just need to submit appropriate parameters including the required EVENT_MASTER.gKey and OUT_EMAIL.jKey (@gKey, @jKey).
In this example we specify start and end date (@start, @end), from who the meeting request is from (@from), email address for the person how is going to receive the meeting request (@to). We also specify what the meeting is about (@summary and @descrip). The step is ended by updating the EVENT_MASTER.Status field indicating that Boomerang can start processing the appointment schedule.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'whatcanboomerangdo@fuel9.com' ,@to = 'bob.slydell@tps.com' ,@summary = 'This is meeting about....' ,@descrip = 'Discuss the following topics:' -- Release event to be processed ---------------- update EVENT_MASTER set Status = 0 where gKey = @gKey --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Result:
Additional Meeting Information
There are a number of additional fields supported by the VCALENDAR standard that you may use. For example RSVP (@rsvp), method (@method), categories (@categories), class (@class) and location (@location). All these fields (stored procedure input parameter) have a default value and are therefore not necessarily needed.
These fields in particular but all fields in general may be treated slightly differently depending on the receiving mail/calendar client. I.e. you may see different result in, for example, Google Calendar compared to Microsoft Outlook.
Fore more information on all available options and default values see page sp_Add_vCalendar.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'whatcanboomerangdo@fuel9.com' ,@to = 'bob.slydell@tps.com' ,@summary = 'This is meeting about....' ,@descrip = 'Discuss the following topics:' ,@rsvp = 0 ,@method = 'REQUEST' ,@categories = 'APPOINTMENT' ,@class = 'CONFIDENTIAL' ,@location = 'Conference Room 123' --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Adding Attendees
To add more people to a meeting request you use sp_Add_vCalendar_Attendee stored procedure. This stored procedure will add individuals to the meeting request in accordance to the iCalendar standard.
The stored procedure have 3 input parameters, To (@to) i.e. email address to the person being invited. RSVP (@rsvp) i.e. request for response. (0 = No, 1=Yes). Identifier (@aKey) i.e. the unique id that links meeting attendees to the meeting request.
We may use the @aKey Output parameter from sp_Add_vCalendar to pass the @aKey Input parameter in sp_Add_vCalendar_Attendee.
Attendees will automatically added to OUT_EMAIL_RECIPIENT table.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@aKey = @aKey out -- aKey parameter used by sp_Add_vCalendar_Attendee below ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'whatcanboomerangdo@fuel9.com' ,@to = 'bob.slydell@tps.com' ,@summary = 'This is meeting about....' ,@descrip = 'Discuss the following topics:' ,@rsvp = 1 ,@method = 'REQUEST' ,@categories = 'APPOINTMENT' ,@class = 'CONFIDENTIAL' ,@location = 'Conference Room 123' exec sp_Add_vCalendar_Attendee @aKey = @aKey -- Output parameter from sp_Add_vCalendar ,@to = 'bob.parker@tps.com' ,@rsvp = 1 --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Result:
File Attachments
To add one or more file attachments to a meeting request insert a record into EVENT_CONTENT for each attachment. The attached may be a SSRS report, Stream or a specific file on a network share. See EVENT_CONTENT.Source for more information.
In this example we add a SSRS report called “Test_Report” found in directory “Boomerang_Demo”. If the SSRS report requires parameters they will have to be inserted into CONTENT_PARAMETERS.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, IsAttachment, Format) values (@gKey, @jKey, NEWID(), 2, 'Boomerang_Demo/Test_Report', 1, 'PDF') --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Result:
Cancel Meeting Requests
When canceling a meeting request the aKey used in the original meeting request needs to be referenced. Subsequently the original aKey needs be stored somewhere in order for it to be retrieved if a meeting needs to be cancel. One option is to use the user defined fields in EVENT_MASTER. We do this by adding the @aKey output parameter when releasing the job.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- update EVENT_MASTER set Status = 0, Str1 = 'My Original Meeting Request', Uid1 = @aKey where gKey = @gKey --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Result:
Status Str1 Uid1 ----------- ------------------------------------- ------------------------------------ 0 My Original Meeting Request AC3B3D6C-2803-4FAC-99C4-86C61AAD7AB8 (1 row(s) affected) |
To cancel the meeting request we simply refer to the original @aKey and change the @method to “CANCEL” from “REQUEST” (default).
--- Automate Appointment Scheduling | Boomerang Notification Services for SQL --------------------- exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@aKey = 'AC3B3D6C-2803-4FAC-99C4-86C61AAD7AB8' ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'whatcanboomerangdo@fuel9.com' ,@to = 'bob.slydell@tps.com' ,@summary = 'This is meeting about....' ,@method = 'CANCEL' --- Automate Appointment Scheduling | Boomerang Notification Services for SQL --------------------- |
Result:
Track Meeting Request Response
There are several ways to handle responses to meeting requests. The first option would be to send the meeting request on behalf of an individual. All responses will then be sent to the individual in question. In this example Bob Slydell will receive any response from Bob Parker.
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'bob.slydell@tps.com' ,@to = 'bob.parker@tps.com' ,@summary = 'This is meeting about....' --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
The second approach would be to let Boomerang receive any response. This requires that the Email In features has been configured. If so any meeting request response will end up in the IN_EMAIL table with a reference (IN_EMAIL.jKey) to the meeting request.
To enable response tracking on meeting request the OUT_EMAIL.IncludeKey should be set to 1 (Tracking Code + Meeting Summary) or 2 (Meeting Summary + Tracking Code).
--- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- declare @gKey uniqueidentifier, @jKey uniqueidentifier, @aKey uniqueidentifier set @gKey = NEWID() set @jKey = NEWID() insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Automate Meeting Requests') insert OUT_EMAIL (gKey, jKey, Subject, IncludeKey) values (@gKey, @jKey, 'Meeting Request', 2) exec sp_Add_vCalendar @gKey = @gKey ,@jKey = @jKey ,@start = '2012-09-21 14:00:00.000' ,@end = '2012-09-21 15:00:00.000' ,@from = 'whatcanboomerangdo@fuel9.com' ,@to = 'bob.slydell@tps.com' ,@summary = 'This is meeting about....' ,@descrip = 'Discuss the following topics:' update EVENT_MASTER set Status = 0 where gKey = @gKey --- Automate Meeting Requests | Boomerang Notification Services for SQL --------------------- |
Result:
Once the meeting request has been responded to the the response will be found in the IN_EMAIL table and since response tracking was turned on the response email will be linked to the original meeting request email by the IN_EMAIL.jKey.
There are differences between email/calender applications in how they are handling meeting request responses but all that we come across will but the the response message i.e. accepted, tentative or declined in the subject line i.e. IN_EMAIL.Subject field.
--- Select from IN_EMAIL --------------- select jKey, Key, From_Email, Subject from IN_EMAIL |
Result:
jKey rKey From_Email Subject ------------------------------------ ------------------------------------ -------------------- ----------------------------- C42C228D-BF77-4046-90D9-091E2425826C 2F992853-7260-410B-83A2-7195173A1008 bob.slydell@tps.com Accepted: This is meeting.... (1 row(s) affected) |
Once the message has been received and parsed sp_On_Email_In trigger will be fired. This trigger can easily be used to evaluate responses from meeting requests and to further process the response e.g. to update a CRM system or other scheduling and planning application.
Conclusion
Boomerang takes away the chore of writing complex code to interface with notification infrastructure like e-mail, printers, file and fax servers. By means of the Boomerang Notification Framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code. As demonstrated above automating meeting request, cancellations and tracking responses can be done with just a few T-SQL statements.
About Boomerang Notification Framework
Boomerang is a collection of notification services for creating applications that generate and send, as well as receiving notifications. Using the Boomerang notification framework, you can quickly create applications to generate and send notifications to customers, suppliers, partners and employees.
The application interface of Boomerang is entirely based on SQL Server (MS) meaning that each notification service has a corresponding table object that represents the service in question. For example to send and email out you would insert a record into the OUT_EMAIL table. To send a fax you would insert a record into the OUT_FAX table.
