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

Operator 'ge' incompatible with operand types 'System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' and 'System.String' at position 10.

Usually the error comes when there is a syntax error in the query we designed and dealing with date time filters in Microsoft Dynamic CRM.

I have explained the details in my blog here

A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

Exception Details

Type1 A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThanOrEqual'

Type2 Operator 'ge' incompatible with operand types 'System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]' and 'System.String' at position 10.

Usually the error comes when there is a syntax error in the query we designed and dealing with date time filters in Microsoft Dynamic CRM .

Now how to use Date Filter in Web API or O Data Query in Microsoft Dynamic CRM ?

Let’s consider a case where we want to query all sales order with booked date in year 2015.

SQL select query for this would be

Select *

from SalesOrder

Where new_BookedDate >= '2015-01=01' And new_BookedDate <= '2015-12-31'

Equivalent Fetch Xml would be

    <fetch mapping="logical" version="1.0">
      <entity name="SalesOrder">
        <all-attributes />
        <filter>
          <condition attribute="new_BookedDate" operator="ge" value="2015-01=01" />
          <condition attribute="new_BookedDate" operator="le" value="2015-12-31" />
        </filter>
      </entity>
    </fetch>

     

OData Syntax for Query would be

// Getting Server Context

var serverUrl = Xrm.Page.context.getClientUrl();

// Defining the OData complete Url

serverUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/" ;

// Name of the entity

serverUrl = serverUrl + "SalesOrderSet?";  

// Filter Criteria with Date

serverUrl = serverUrl + "$filter=new_BookedDate ge datetime'2015-01-01' and new_BookedDate 

Web API Syntax for Query would be

// Getting Server Context

var serverUrl = Xrm.Page.context.getClientUrl();

serverUrl = serverUrl + "/api/data/v8.1/" ; // Defining the OData complete Url

serverUrl = serverUrl + "salesorders?";    // Name of the entity

// Filter Criteria with Date

serverUrl = serverUrl + "$filter=new_BookedDate ge 2015-01-01 and new_BookedDate le 2015-12-31"; 

I hope the information is useful !!!

Crm Exception: Message: 'Entity' metadata entity doesn't contain metadata attribute with Name

Crm Exception: Message: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'., ErrorCode: -2147217149

The specified field does not exist in Microsoft Dynamics CRM

Exception Details

MSCRM Error Report:

--------------------------------------------------------------------------------------------------------

Error: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'.

Error Number: 0x80041103

Error Message: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'.

Error Details: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'.

Source File: Not available

Line Number: Not available

Request URL:

Stack Trace Info: [CrmException: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'.]

   at Microsoft.Crm.Application.Platform.ServiceCommands.PlatformCommand.XrmExecuteInternal()

   at Microsoft.Crm.Application.Platform.ServiceCommands.ImportSolutionCommand.Execute()

   at Microsoft.Crm.Application.Platform.DataSource.ImportSolution(Byte[] customizationFile, Boolean overwriteUnmanagedCustomizations, Boolean publishWorkflows, Guid importJobId, Boolean convertToManaged, Boolean skipProductUpdateDependencies, Boolean holdingSolution, IOrganizationContext context)

   at Microsoft.Crm.Web.Tools.Solution.SolutionImportProcessPage.ImportSolution()

   at Microsoft.Crm.Web.Tools.Solution.SolutionImportProcessPage.ProcessRequestData()

Exception Detailed Trace 

--------------------------------------------------------------------------------------------------------

Web Service Plug-in failed in SdkMessageProcessingStepId: {1B830950-E106-4EE1-B3FD-D348CB65DC8D}; EntityName: none; Stage: 30; MessageName: ImportSolution; AssemblyName: Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35; ClassName: Microsoft.Crm.Extensibility.InternalOperationPlugin; Exception: Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)

   at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)

   at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider)

   at Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)

   at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)

Inner Exception: Microsoft.Crm.Metadata.MetadataAttributeMetadataNotFoundException: 'Entity' metadata entity doesn't contain metadata attribute with Name = 'isslaenabled'.

   at Microsoft.Crm.Tools.ImportExportPublish.ImportXml.RunImport(String[] ImportEntities)

   at Microsoft.Crm.Tools.ImportExportPublish.ImportXml.RunImport()

   at Microsoft.Crm.WebServices.ImportXmlService.ImportSolutionSkipCapable(Boolean overwriteUnmanagedCustomizations, Boolean publishWorkflows, Byte[] customizationFile, Guid importJobId, Boolean convertToManaged, Boolean skipProductUpdateDependencies, Boolean holdingSolution, ExecutionContext context)

Cause of an Exception

1) This error gets generated usually when doing import of a solution.

2) When you are trying to create any field in Microsoft Dynamic CRM.

Resolution

Solution being exported should match the version where it is being imported of a CRM instance

1) Check your solution version, if it is

     - Version 8.0  (denote MS CRM 2016)

      OR

     - Version 8.1  (denote MS CRM 2016 with Service Pack 1)

2)  Check your destination CRM instance version where you are trying to import your                  solution to.

3) Try to match your solution version with CRM instance version.

Once you apply a Service Pack 1 to Microsoft Dynamic CRM, don't forget to update an organization from Deployment Manager

1) Open deployment manager.

2) Select your organization.

3) Right click an organization and select update.

That's it, import of a solution should be working then.

How to visualize or presents Microsoft Dynamic CRM Records in a better way

Is there a better way of presenting Microsoft Dynamic CRM Record using colors ?

I am sure you came across categorizing your records in Microsoft Dynamic CRM. Wonder what if we can show it by defining colors to various category we choose.

Let’s consider a scenario where we wanted a more visual presentation of which support plan our customer belongs to. Also if a case is logged for a customer, then upon customer support plan opted, a case should also reflect specified color to denote customer support plan.

Assume that Customer have different Support Plan

1.     Critical

2.     High

3.     Non-Critical

Now, based on customer get selected on a case form we should have something like this

A blue bar may denote that customer support plan is of “High” Importance.

  

Steps to achieve our goal

1)      We need to create a color bar that would get displayed on the form (One color = One HTML Web resource)

a.       White Color web Resource to denote Customer has not choose any support plan.

b.      RED COLOR to denote Customer choose critical support plan

c.       BLUE COLOR to denote Customer choose high importance support plan

d.      YELLOW COLOR  to denote customer choose non-critical support plan.

2)      Let’s create a White Color Web Resource

Here is the code which you require to create HTML Web Resource.

<html>

<body style="word-wrap: break-word;">

    <div style="background-color: white; width: 100%; height: 20px;"></div>

</body>

</html>

  

3)      Similarly we can create other 3 web resources, just by changing background color,

Name and Code for other web resource would be

https://kalunge.crm4.dynamics.com//WebResources/new_red_CustomerHighImportance

<html>

<body style="word-wrap: break-word;">

    <div style="background-color: red; width: 100%; height: 20px;"></div>

</body>

</html>

https://kalunge.crm4.dynamics.com//WebResources/new_blue_CustomerHighImportance

<html>

<body style="word-wrap: break-word;">

    <div style="background-color: blue; width: 100%; height: 20px;"></div>

</body>

</html>

https://kalunge.crm4.dynamics.com//WebResources/new_yellow_CustomerNonCritical

<html>

<body style="word-wrap: break-word;">

    <div style="background-color: yellow; width: 100%; height: 20px;"></div>

</body>

</html>

4)      Next step is to add HTML Web Resource to the Case Form within an Iframe.

a.       Open the form editor of case form and insert an Iframe

   

b.      Input the Url of White Color specifically so that user won’t see default color changed to any specific color.

c.       Choose Not to Visible By Default

d.      Specify the number of Rows to 1

5)      Next Step is to create a JavaScript Web Resource to Set the Iframe Src attribute dynamically based on the customer selected and its support plan.

Note : I am using Microsoft Dynamic CRM 2016 Web API to fetch customer information

// Register this event to Form OnLoad Event of Case

function caseOnLoad()

{

    window.setTimeout(getcontrolMember(),2000);

}

// Register this event to Customer field onChange Event

function customerOnChange()

{

    window.setTimeout(getcontrolMember(), 2000);

}

// Validate if Customer is selected on Case Form

function getcontrolMember()

{

    var primaryCustomer = Xrm.Page.getAttribute('customerid').getValue();

    if (primaryCustomer != null)

    {

        var primaryCustomerId = primaryCustomer[0].id;

        retrieveCustomer(primaryCustomerId.substring(1, primaryCustomerId.length - 1));

    }

    else

    {

        var iframeObject = Xrm.Page.getControl("IFRAME_Color");

        iframeObject.setVisible(false);

    }

} 

// Method to Retreive Customer Information using CRM Web API

function retrieveCustomer(accountid)

{

    var serverUrl = Xrm.Page.context.getClientUrl();

    var oDataQuery = serverUrl + "/api/data/v8.1/";

    oDataQuery = oDataQuery + "accounts(" + accountid + ")";

    oDataQuery = oDataQuery + "?$select=new_remotesupportplan";

    $.ajax(

    {

        type: "GET",

        contentType: "application/json; charset=utf-8",

        datatype: "json",

        url: oDataQuery,

        beforeSend: function (XMLHttpRequest) {

            XMLHttpRequest.setRequestHeader("Accept", "application/json");

        },

        success: function (data, textStatus, XmlHttpRequest)

        {

            if (data != null && data.new_remotesupportplan != null) {

                setiFrameUrl(data.new_remotesupportplan);

            }

            else

            {

                setiFrameUrl(0);

            }

        },

        error: function (XmlHttpRequest, textStatus, errorThrown) {

            // ONLY USE THIS SECTION FOR ERRORS

        }

    }

    );

}

// Method to set IFrame Src attribute based on Customer Support Plan

function setiFrameUrl(remotesupportplan)

{

    var iframeObject = Xrm.Page.getControl("IFRAME_Color");

    var serverUrl = Xrm.Page.context.getClientUrl() + '/WebResources/';

    iframeObject.setVisible(true);

    switch (remotesupportplan)

    {

        case 100000001:           

            iframeObject.setSrc(serverUrl + 'new_green');

            break;

        case 100000000:

            iframeObject.setSrc(serverUrl + 'new_blue');

            break;

        default:

            iframeObject.setSrc(serverUrl + 'new_HTMLColorToCRMField');

            iframeObject.setVisible(false);

    }

}

Note : We are using window.setTimeout() specifically to delay loading of Iframe as sometime Iframe load itself and ignore the page to load.

6)      Last step would be to register the events on Case Entity.

That’s it Guys. Just give a Publish All Customization to CRM.

Here is my testing after I put it all together.

1)      Account, where Support Plan is selected to “Critical”

2)      Now if we create a case and select customer as “Mr. Critical (RED)”

3)      Or If we select a customer as "Mr. Non-Critical (Yellow)"