Press "Enter" to skip to content

Dynamic Email Templates in K2 Workflow (SQL Based)

0

Making dynamic Email Templates is a frequent requirement from many customers. The idea is to be able to change the email subject and body without redeploying the workflow. There are a lot of different ways how you can implement this. In this post I will show how dynamic email templating functionality can be achieved with the help of several tables and a stored procedures in SQL. I deem this approach as a very useful one for those applications/projects, where most of the data is stored in the SQL Database. If your email data is stored not in SQL, you can read my another article Email Template ServiceBroker, where I explain, how dynamically retrieve data from any SmartObject. So let’s start.

1. Create a Table for Email Templates

First of all, we need to create a data structure where our dynamic email templates will be stored. Since in most cases you send 1 Email per Activity, the following table schema can be used:

CREATE TABLE [dbo].[EmailTemplate]
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [WorkflowName] NVARCHAR(250) NOT NULL,
  [ActivityName] NVARCHAR(250) NOT NULL,
  [CustomFilter] NVARCHAR(250) NULL,
  [EmailSubject] NVARCHAR(250) NULL,
  [EmailBody] NVARCHAR(max) NULL,
  CONSTRAINT [PK_ApplicationEmailTemplate] PRIMARY KEY CLUSTERED ([Id] ASC)
)

As you might have noticed, the table contains one additional property, i.e. CustomFilter. It can be used in those cases, where you need to send more than 1 email in the same workflow activity OR your templates can vary depending on the application and/or workflow logic. But for the demo purpose this is enough.

2. Create a Table for Placeholders

The second table is used to contain the list of placeholders, which later on will be replaced by the application data in your email templates by the workflow. I will use this table to store placeholder names as well as short scripts. Below I will explain what kind of scripts I am talking about. The structure of the Placeholder table is the following one:

CREATE TABLE [dbo].[Placeholder]
(
   [Name] NVARCHAR(100) NOT NULL,
   [Script] NVARCHAR(max) NULL,
   CONSTRAINT [PK_Placeholder] PRIMARY KEY CLUSTERED ([Name] ASC)
)

Example of the placeholder table, the values of which are obtained during execution:

Image for Email templates table


3. Create some Stored Procedure for the templates update

Most of the data, which is needed in the email subject/body, can be obtained with the help of the main object ‘ItemId‘, processed by the workflow. Usually, you start the workflow sending this ItemId, thus giving your workflow instance the context which needs to be used. This can be RequestId or ProjectId etc. Also there can be some workflow specific values, which cannot be obtained from the database, e.g. Worklist Item Link. For this purpose you can create additional input properties in your stored procedure and fill them in from the workflow.

The idea is to replace such text:

Approval of Request No.%RequestId%%RequestName%%Country%

with

Approval of Request No. 5Purchase OrderGermany

The Stored Procedure, shown below, simply does the following:

  • receives the data from the workflow;
  • finds the relevant Email Template;
  • obtains all the values for all the placeholders;
  • replaces placeholders in the template with the values;
  • returns the updated Email Subject and Body.
CREATE PROCEDURE [dbo].[spGetEmailWithValues]
  @RequestId int = 0, --Application Object Id, processed by the workflow
  @WorkflowName NVARCHAR(250),--WF Name if you need to reuse it
  @ActivityName NVARCHAR(250),--Activity in the workflow, where the email is sent
  @CustomFilter NVARCHAR(50) = NULL,--Additional Filter option - can remain empty
  @WorklistLink NVARCHAR(2000) = NULL,--Worklist Item Link
  @EmailSubject NVARCHAR(MAX) = NULL OUTPUT, --Updated Email Subject
  @EmailBody NVARCHAR(MAX) = NULL OUTPUT --Updated Email Body
AS
BEGIN
SET NOCOUNT ON;
--    1. Getting Email Subject and Email Body
SELECT
  @EmailSubject = [EmailSubject],
  @EmailBody = [EmailBody]
FROM [dbo].[EmailTemplate]
WHERE [WorkflowName] = @WorkflowName
      AND [ActivityName] = @ActivityName
      AND ([CustomFilter] = @CustomFilter OR @CustomFilter IS NULL)
    
-- 2. Getting the placeholders
DECLARE @tblPlaceholders TABLE ([Name] NVARCHAR(100), [Script] NVARCHAR(max));
INSERT INTO @tblPlaceholders (Name, Script)
SELECT Name, Script FROM [dbo].[Placeholder]
-- 3. Getting the values for every placeholder dynamically
DECLARE @tblPlaceholderWithValues TABLE ([Name] NVARCHAR(100), [Value] NVARCHAR(MAX));
DECLARE @pName NVARCHAR(50);
DECLARE @pScript NVARCHAR(MAX);
DECLARE @pValue NVARCHAR(MAX);
WHILE EXISTS(SELECT * FROM @tblPlaceholders)
BEGIN
  SELECT TOP 1
   @pName = [Name],
   @pScript = [Script]
  FROM @tblPlaceholders ORDER BY [Name]
  IF (@pScript IS NOT NULL)
  BEGIN
   --Dynamically executing the script, associated with a placeholder
   EXECUTE sp_executesql @pScript, N'@reqId INT, @val nvarchar(max) OUTPUT', @reqId = @RequestId, @val = @pValue OUTPUT
  END
  --Inserting the placeholder with values into another table
  INSERT INTO @tblPlaceholderWithValues(Name,Value)
  VALUES (@pName,@pValue)
  --Deleting the first selected item for looping purposes
  DELETE FROM @tblPlaceholders
  WHERE [Name] = @pName
END
-- 4. Adding placeholders with values, received from the workflow
INSERT INTO @tblPlaceholderWithValues (Name, Value)
VALUES (N'WorklistLink', @WorklistLink)
-- 5. Replacing the placeholders in the template with values
--5.1. Replacing Subject
DECLARE @tblSubjectPlaceholders TABLE ([Name] NVARCHAR(100), [Value] NVARCHAR(MAX));
INSERT INTO @tblSubjectPlaceholders (Name, Value)
SELECT Name, Value FROM @tblPlaceholderWithValues
DECLARE @UpdatedSubject NVARCHAR(MAX) = @EmailSubject;

WHILE EXISTS(SELECT * FROM @tblSubjectPlaceholders)
BEGIN
  SELECT TOP 1
    @pName = [Name],
    @pValue = [Value]
  FROM @tblSubjectPlaceholders ORDER BY [Name];
  SET @UpdatedSubject = (SELECT REPLACE(@UpdatedSubject, N'%' + @pName + '%', ISNULL(@pValue, N'')));
  DELETE FROM @tblSubjectPlaceholders
  WHERE [Name] = @pName
END
SET @EmailSubject = @UpdatedSubject;
--5.2. Replacing Body
DECLARE @tblBodyPlaceholders TABLE ([Name] NVARCHAR(100), [Value] NVARCHAR(MAX));
INSERT INTO @tblBodyPlaceholders (Name, Value)
SELECT Name, Value FROM @tblPlaceholderWithValues
DECLARE @UpdatedBody NVARCHAR(MAX) = @EmailBody;

WHILE EXISTS(SELECT * FROM @tblBodyPlaceholders)
BEGIN
  SELECT TOP 1
    @pName = [Name],
    @pValue = [Value]
  FROM @tblBodyPlaceholders ORDER BY [Name];
  SET @UpdatedBody = (SELECT REPLACE(@UpdatedBody, N'%' + @pName + '%', ISNULL(@pValue, N'')));
  DELETE FROM @tblBodyPlaceholders
  WHERE [Name] = @pName
END
SET @EmailBody = @UpdatedBody;
END

DEMO: Creating a sample Workflow

1. For the demo purposes I will create the following Request table and fill it with dummy values:

Users table

2. Out of all the tables and stored procedures I will create the smartobjects to be used in the workflow:

SmartObjects

3. I fill in the Placeholder table with the following values, thus allowing me to add 3 placeholders from SQL + 1 from the workflow (Worklist Item Link – inside the Stored Procedure):

Placeholders table

4. Then I can create and configure a test workflow with a sample Default Client event:

  • Subject:
setup subject
  • Body:
Setup body

The final workflow looks in the following way and has a name ‘Demo.Approval’:

Workflow example

5. Create an Email Template for the above mentioned Approval activity:

Define template

6. Start workflow with various RequestIds and see how this will produce different Email Bodies and Subjects:

Final example

DEMO: Adding a new Placeholder without redeploying the workflow.

Let’s imagine the user requested to add “CreatedDate” field to the Email Body. For this you need do the following:

1. Add the Name and Script to the Placeholder table:

Add placeholder image

2. Update the template:

Update template

3. Run the workflow again:

Final example

I hope that Dynamic Email Templates can help you to achieve certain flexibility level and avoid redeployments of the workflow, that can be very annoying, depending on the customers’ internal procedures. Even though I used K2 4.7 for this article, it still can be used to help you create dynamic email templates for K2 Five workflow.

Leave a Reply

Your email address will not be published. Required fields are marked *

8 + thirteen =