How to find email template being used in workflows in Microsoft Dynamic CRM

In Microsoft Dynamic CRM, we use workflow to automate business processes.

We often use Send Email as a step in workflow to send out any notification or communication.

We have two option here to Send an Email.

1)   Create Email Message

This option creates an email template specific to workflow and no other workflow in a CRM system can use this.

2)   Use Template

Here we use existing template to send our communication.

When we go for second option that is “Use Template”, then how do we know if this email template is being used by any other workflow in the system ?

Now what if someone edit this template, it would then be reflected in all existing workflows using this template.

How to identify the template that is being used in the workflow so I know which one to edit.

Solution in brief

There is no direct and out of the box way to find all references of email template being used in any of the workflow in Microsoft Dynamic CRM.

However, there are indirect ways to find out all references of an email template.

A)     Using SQL Statements

B)     Using MS CRM API and building custom application.

A)   Solution in Details – Using SQL Statement

Here is a SQL Query statement which can be run under MS CRM database to get the name of workflow using given Email template.

  Select ActiveWorkflowIdName,PrimaryEntity,*

  from Workflow 

  Where WorkflowId in (  

        Select ObjectId

        From DependencyNode

        Where DependencyNodeId In (

               SELECT DependentComponentNodeId

               FROM [DependencyBase]

               Where RequiredComponentNodeId In (    

                  SELECT DependencyNodeId

                  FROM DependencyNode  

                  Where ObjectId = '00000000-000000000-0000-000000000000')))

Note : In Order to use this query we need to replace '00000000-000000000-0000-000000000000' with actual email template Guid.

Now how to find the email template Guid ?

Here are  the steps to find Guid for a given email Template.

Step 1) Go to Setting -> Template -> Email Templates.

 

Step 2) Open you email template as in below image and copy the complete url as highlighted in image

 

Step 3) If Url is not displayed, then open template in another browser window using short cut key Ctrl + N.

Step 4) Consider below url is following example.

https://kalunge.crm4.dynamics.com/tools/emailtemplateeditor/emailtemplateeditor.aspx?id=%7b62E5E1EE-9F60-E611-80E4-5065F38B3531%7d

Within %7b ---  and %7d is url template unique guid for internal CRM reference.

You need this guid to replace with above guid as in SQL Query.

B) Solution in Details – Using MS CRM Web API Calls

The SQL solution is a quick one, but it has got its limitations

1)    Its suits only a developer who understand technical aspects.

2)    It can only work in MS CRM (On-Premise) and ADFS.

3)    MS CRM Online does not support SQL Statements.

There is a class in MS CRM which find out dependent component of a given object.

In our scenario, template is being an object and workflows are dependent components.

The name of the class is : RetrieveDependentComponentsRequest Class

You can refer this MSDN article for further reference.

https://msdn.microsoft.com/en-us/library/microsoft.crm.sdk.messages.retrievedependentcomponentsrequest.aspx

We can built a custom application using such classes and MS CRM SDK and make it available directly for business user to check email template references.

Happy Learning J