SQL Expressions and SQL Scripts in E-mail Notification Parameters

Alloy Navigator supports parameters in E-mail Notifications, and dynamically calculated values in E-mail. Parameters open the way for a more flexible and effective workflow.

INFO: For details on E-mail Notifications, see Creating E-mail Notifications.

The following scenario demonstrates an example of using expressions in E-mail Notification parameters.

Suppose that you want every technician who has ever worked on an Incident to be notified on its resolution. This information changes dynamically over time and therefore you can't use the ticket's Assignee field because it contains only the last technician assigned to that Ticket. We would need to obtain that information from the Incident's Activity Log because every activity would be logged there, along with the ID of technicians involved in those activities. This is where SQL expressions will come in handy. Let’s outline the steps required to get the list of Primary E-mail addresses belonging to the technicians involved in solving a specific issue, i.e. all technicians from the Incident’s Activity Log.

1. Go to E-mail Notifications for Incidents (Workflow and Business Logic > Service Support > Incidents > Workflow > Components > E-mail Notifications) and create an E-mail Notification as follows:

   1. Click New.

   2. On the Parameters tab, add String parameter which will hold a list of technician E-mail addresses (for example, To_AllTechnicians).

   3. On the General tab, click the ellipsis button next to the To field and select the parameter that you added, as illustrated below. At runtime, this parameter will receive e-mail addresses of technicians involved in solving the Incident.

   4. Enter the message subject and body. You can combine static text with placeholders for dynamic values.

   5. Save the E-mail Notification.

      

2. Go to Step Actions for Incidents (Workflow and Business Logic > Service Desk > Incidents > Workflow > Actions > Step Actions) and create a new Step Action for resolving Incidents. Obviously, you will need to add an Update Field operation changing the Incident Status to “Resolved” and create an Action Form.

   Alternatively, you can integrate your E-mail Notification into an existing Step. For example, you can modify the Resolve Incident #247 Step from the default workflow.

3. Add your E-mail Notification to the Step:

   1. On the Programming tab, click New > E-mail Notification and select the notification that you prepared in the first step.

   2. In the E-mail Notification dialog box, you will see a list of available parameters. Double-click the one you designed to hold the list of E-mail addresses of the technicians (To_AllAassignees).

   3. In the Assign Parameter dialog box, click the Build Expression button next to the Value field to bring up the Build Expression dialog box.

   4. Under Expression Type, click SQL script.

   5. Paste the following SQL code into the text field below:

      declare @emails nvarchar(2048)

      select @emails = (select distinct p.[Primary_Email] + ', ' as 'text()' from [Activity] a inner join [Persons] p on a.[Created_by_ID] = p.[ID]
where a.[Object_ID] = %[DBF ID]% and p.[Technician] = 1 and p.[Primary_Email] is not null

      for xml path(''))

      select left(@emails,len(@emails)-1)

   

   NOTE: SQL script expressions do not limit you to a single SQL statement. You are free to use whatever SQL code fits your needs. Just keep in mind that the value will only be returned to the workflow when there is a SELECT statement.

4. Save the Action.

At this point, you will have an Action Step that will not only resolve an Incident but also notify all technicians involved in solving it.