This week, I get a task to update Email Content that is being sent from Power Automate. The existing logic was retrieving data from Dataverse > then populating the data manually (if you need to get lookup data, then you need to have Parse JSON action) > then setting the content with the correct variable 1-by-1. I was overwhelmed by the effort needed to do it and decided to make it easier using Custom API. The idea was to set up an Email Template (from Dataverse) and match it with the FetchXML needed. Once the API run, it will return the correct Subject + Body result.
Create Custom API’s Code
Below is the business logic needed for the Custom API (I’m using Niam.Xrm.Framework for Development as we now have an official way to use third-party lib 😎):
using System;
using System.Collections.Generic;
using System.Linq;
using Insurgo.Custom.Api.Extensions;
using Insurgo.Custom.Entities;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Niam.XRM.Framework;
using Niam.XRM.Framework.Data;
using Niam.XRM.Framework.Interfaces.Plugin;
using Niam.XRM.Framework.Plugin;
namespace Insurgo.Custom.Api.Business
{
public class GetEmailContent : OperationBase
{
public const string FetchXmlParameter = "FetchXmlParameter";
public const string TemplateIdParameter = "TemplateIdParameter";
public const string OutputParameter = "OutputParameter";
public class GetEmailContentResult
{
public string Subject { get; set; }
public string Body { get; set; }
}
public GetEmailContent(ITransactionContext<Entity> context) : base(context)
{
}
protected override void HandleExecute()
{
var fetchXml = Context.PluginExecutionContext.InputParameters[FetchXmlParameter].ToString();
if (string.IsNullOrEmpty(fetchXml)) throw new ArgumentNullException(nameof(FetchXmlParameter));
var templateId = Context.PluginExecutionContext.InputParameters[TemplateIdParameter].ToString();
if (string.IsNullOrEmpty(templateId)) throw new ArgumentNullException(nameof(TemplateIdParameter));
var data = Service.RetrieveMultiple(new FetchExpression(fetchXml));
var currentTemplate = Service.Retrieve(Template.EntityLogicalName, new Guid(templateId),
new ColumnSet<Template>(e => e.Subject, e => e.Body))
.ToEntity<Template>();
var mainEntity = data.Entities.FirstOrDefault();
if (mainEntity == null)
{
Context.PluginExecutionContext.OutputParameters[OutputParameter] = new GetEmailContentResult().ToJson();
return;
}
var subject = ReplaceText(currentTemplate.Get(e => e.Subject).GetTemplateContent(),
mainEntity);
var bodyText = ProcessList(currentTemplate.Get(e => e.Body).GetTemplateContent(),
data.Entities.ToArray());
var body = ReplaceText(bodyText, mainEntity);
Context.PluginExecutionContext.OutputParameters[OutputParameter] = new GetEmailContentResult
{
Body = body,
Subject = subject.StripHtml()
}.ToJson();
}
private static string ProcessList(string text, Entity[] entities)
{
var currentIndex = text.IndexOf("<list>", StringComparison.Ordinal);
var lastIndex = text.IndexOf("</list>", StringComparison.Ordinal);
var valid = currentIndex > -1 && lastIndex > -1;
if (!valid) return text;
currentIndex += 6;
var originalText = text.Substring(currentIndex, lastIndex - currentIndex);
var result = new List<string>();
foreach (var entity in entities)
{
var tempText = string.Copy(originalText);
result.Add(ReplaceText(tempText, entity));
}
return text.Replace(originalText, string.Join(" ", result))
.Replace("<list>", "")
.Replace("</list>", "");
}
private static string ReplaceText(string text, Entity source)
{
foreach (var attribute in source.Attributes)
{
var findText = "{" + attribute.Key;
var index = text.IndexOf(findText, StringComparison.Ordinal);
if (index == -1) continue;
var closeIndex = FindCloseIndex(text, index + findText.Length);
if (closeIndex == -1) continue;
var key = text.Substring(index, closeIndex - index);
var formatString = GetFormatString(key);
var value = source.FormattedValues.Contains(key) ? source.FormattedValues[key] :
(attribute.Value is AliasedValue aliasedValue ? ToString(aliasedValue.Value, formatString) : ToString(attribute.Value, formatString));
text = text.Replace(key, value);
}
return text;
}
private static string ToString(object value, string format)
{
return string.IsNullOrEmpty(format) ? value.ToString() : string.Format("{0:" + format + "}", value);
}
private static string GetFormatString(string key)
{
var list = key.Replace("}", "").Split(':').ToArray();
return list.Length > 1 ? list.LastOrDefault() : string.Empty;
}
private static int FindCloseIndex(string text, int index)
{
for (var i = index; i < text.Length; i++)
{
var current = text[i];
if (current == '}') return i + 1;
}
return -1;
}
}
}
In the above code, we will be dependent on two parameters. The Fetch XML string and the Template Id. The Fetch XML will be the logic to retrieve data needed to display the Email Template (template table). Eg, we want to send an email regarding Account Information with all the related Contacts. Then we need to retrieve the Account data (main entity) and the related Contacts with the help of the link-entity section in Fetch XML:
<fetch version='1.0'
output-format='xml-platform'
mapping='logical'
distinct='true'>
<entity name='account'>
<attribute name='name' />
<attribute name='primarycontactid' />
<attribute name='telephone1' />
<attribute name='accountid' />
<order attribute='name'
descending='false' />
<filter type='and'>
<condition attribute='accountid'
operator='eq'
value='7926d5ca-0226-ed11-9db1-002248210d56' />
</filter>
<link-entity name='contact'
from='parentcustomerid'
to='accountid'
link-type='inner'
alias='ab'>
<attribute name='firstname' />
<attribute name='lastname' />
</link-entity>
</entity>
</fetch>
When we want to display the Detail content, we need a way to flag the element. That is why in the code I’m using the “<list>” and “</list>” strings. The last part is just when we want to replace the content, we need to replace it with the correct value. If we have FormattedValues data for the attribute that we need, we will use it. Else just checked if it is the AliasedValue or normal CRM object (*this is just a prototype code. Got possibility to get an error/display wrong data as the code is very simple at this time).
And the most important thing, based on my observation from the DB level, the Email Template (template) actually will be saved in XSLT format:
That is why need an extension to get the HTML content only using this code:
using System;
using System.Text.RegularExpressions;
using System.Xml;
namespace Insurgo.Custom.Api.Extensions
{
public static class StringExtensions
{
public static string GetTemplateContent(this string text)
{
var valid = text.Contains("xml") && text.Contains("xsl:template match");
if (!valid) return text;
var document = new XmlDocument();
document.LoadXml(text);
var templates = document.GetElementsByTagName("xsl:template");
return templates.Count > 0 ? templates[0].InnerText: "";
}
public static string StripHtml(this string text)
{
return Regex.Replace(text, "<.*?>", String.Empty);
}
}
}
The next step is to create the Plugin:
using Insurgo.Custom.Api.Business;
using Microsoft.Xrm.Sdk;
using Niam.XRM.Framework.Interfaces.Plugin;
using Niam.XRM.Framework.Plugin;
namespace Insurgo.Custom.Api
{
public class PluginGetEmailContent : PluginBase, IPlugin
{
public PluginGetEmailContent(string unsecure, string secure) : base(unsecure, secure)
{
}
protected override void ExecuteCrmPlugin(IPluginContext<Entity> context)
{
new GetEmailContent(context).Execute();
}
}
}
Once you are finished, build + deploy the NuGet package generated to your Dataverse Instance:
Create Custom API
As always, I loved using David Rivard’s Dataverse Custom API Manager in XrmToolBox. Here is very simple. Just create Custom API + two Input Parameters + the Output Parameter:
Demo
For the demonstration I prepared the Flow below:
For the Email Template, here is the Template’s body:
<?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="text" indent="no"/>
<xsl:template match="/data">
<![CDATA[<div data-wrapper="true" style="font-family:'Segoe UI','Helvetica Neue',sans-serif; font-size:9pt">
<div>
<div data-wrapper="true" style="font-family:'Segoe UI','Helvetica Neue',sans-serif; font-size:9pt">Hey {name}!</div>
<div data-wrapper="true" style="font-family:'Segoe UI','Helvetica Neue',sans-serif; font-size:9pt"> </div>
<div data-wrapper="true" style="font-family:'Segoe UI','Helvetica Neue',sans-serif; font-size:9pt"><u><strong>This is sample template for Demo API!</strong></u></div>
<div data-wrapper="true" style="font-family:'Segoe UI','Helvetica Neue',sans-serif; font-size:9pt"> </div>
</div>
<div>
<table style="border-collapse:collapse; font-size:9pt; width:500px" cellspacing="0" cellpadding="1" border="1">
<tbody>
<tr>
<td width="120px">First Name</td>
<td width="120px">Last Name</td>
</tr>
<list>
<tr>
<td width="120px">{ab.firstname}</td>
<td width="120px">{ab.lastname}</td>
</tr>
</list>
</tbody>
</table>
</div>
</div>]]>
</xsl:template>
</xsl:stylesheet>Once got Approval > System will execute the Custom API that we created > Parse the JSON result > and we can directly use the string Subject + Body in the Email without so many steps needed.
Happy CRM-ing!
Temmy Wahyu Raharjo 