Calculated fields will eventually make its way to Dynamics CRM. In the mean time, you can use ISV products like north52 Formula Manager, to achieve this functionality. If you are a developer and would like to implement a light weight version of calculated fields, this can be done using Plugins.
First we need a configuration entity to store the fetchxml that will be used for the calculation and the entity/attribute details that will use this on the Retrieve message.
In this case, the account field exp_invoicetotal will use the fetchxml result's totalworthofinvoices. This is an aliased field. Note that we use {0}, that will be replaced by the primary key of the entity that the plugin executes on.
Here is how the plugin is registered in the Plugin Registration tool. It executes on Retrieve of account.
Here is the Plugin base class.
I have used Gayan Perera's CRM Code Generator to generate the entity classes using the CSharp.tt template, which I have used in the plugin. This plugin inserts the result of the fetchxml in the calculated field configuration entity, as an attribute in the BusinessEntity, which is accessible from the OutputParameter in the plugin. BusinessEntity is one of the output parameters in the Retrieve response.
Here is the how the entity form looks with the calculated field, which I have made as readonly.
First we need a configuration entity to store the fetchxml that will be used for the calculation and the entity/attribute details that will use this on the Retrieve message.
Here is how the plugin is registered in the Plugin Registration tool. It executes on Retrieve of account.
Now the sourcecode for the actual plugin.
using CalculateOnRetrievePlugin.Entities;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;
namespace Experiments.Plugins
{
public class CalculateOnRetrievePlugin : Plugin
{
/// <summary>
/// Initializes a new instance of the <see cref="CalculateOnRetrievePlugin"/> class.
/// </summary>
public CalculateOnRetrievePlugin()
: base(typeof(CalculateOnRetrievePlugin))
{
//Third Param is primary entity name
base.RegisteredEvents.Add(new Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>(PipelinePhase.PostOperation, Message.Retrieve, "account", ExecuteCalculateOnRetrievePlugin));
// Note : you can register for more events here if this plugin is not specific to an individual entity and message combination.
// You may also need to update your RegisterFile.crmregister plug-in registration file to reflect any change.
}
/// <summary>
/// Executes the plug-in.
/// </summary>
/// <param name="localContext">The <see cref="LocalPluginContext"/> which contains the
/// <see cref="IPluginExecutionContext"/>,
/// <see cref="IOrganizationService"/>
/// and <see cref="ITracingService"/>
/// </param>
/// <remarks>
/// For improved performance, Microsoft Dynamics CRM caches plug-in instances.
/// The plug-in's Execute method should be written to be stateless as the constructor
/// is not called for every invocation of the plug-in. Also, multiple system threads
/// could execute the plug-in at the same time. All per invocation state information
/// is stored in the context. This means that you should not use global variables in plug-ins.
/// </remarks>
protected void ExecuteCalculateOnRetrievePlugin(LocalPluginContext localContext)
{
var targetEntity = (Entity)localContext.PluginExecutionContext.OutputParameters["BusinessEntity"];
var queryConfiguration = new QueryExpression(ExpCalculatedfieldconfiguration.Fields.SchemaName)
{
ColumnSet = new ColumnSet(
ExpCalculatedfieldconfiguration.Fields.ExpName,
ExpCalculatedfieldconfiguration.Fields.ExpResultField,
ExpCalculatedfieldconfiguration.Fields.ExpQuery,
ExpCalculatedfieldconfiguration.Fields.ExpFieldName,
ExpCalculatedfieldconfiguration.Fields.ExpIsAliasedField)
};
queryConfiguration.Criteria.AddCondition(new ConditionExpression(
ExpCalculatedfieldconfiguration.Fields.ExpName, ConditionOperator.Equal,
localContext.PluginExecutionContext.PrimaryEntityName));
queryConfiguration.Criteria.AddCondition(new ConditionExpression(
ExpCalculatedfieldconfiguration.Fields.Statecode, ConditionOperator.Equal, 0));
var calcConfigRecords = localContext.OrganizationService.RetrieveMultiple(queryConfiguration).Entities;
foreach (var calcConfigRecord in calcConfigRecords)
{
var isAliased = (bool)calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpIsAliasedField];
var calculatedQueryResults = localContext.OrganizationService.RetrieveMultiple(
new FetchExpression(
string.Format(calcConfigRecord[
ExpCalculatedfieldconfiguration.Fields.ExpQuery].ToString(),
localContext.PluginExecutionContext.PrimaryEntityId)
)).Entities;
foreach (var calculatedQueryResult in calculatedQueryResults)
{
var targetFieldName =
calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpFieldName].ToString();
var resultFieldName =
calcConfigRecord[ExpCalculatedfieldconfiguration.Fields.ExpResultField].ToString();
var fieldValue = calculatedQueryResult[resultFieldName] as AliasedValue;
if (fieldValue != null)
{
targetEntity[targetFieldName] = ExtractString(fieldValue.Value);
}
else
{
targetEntity[targetFieldName] =
ExtractString(calculatedQueryResult[resultFieldName]);
}
}
}
}
private string ExtractString(object attributeValue)
{
var attributeType = attributeValue.GetType();
var result = string.Empty;
if (attributeType == typeof (EntityReference))
{
result = ((EntityReference) attributeValue).Name;
}
else if (attributeType == typeof(OptionSetValue))
{
result = ((OptionSetValue)attributeValue).Value.ToString();
}
else if (attributeType == typeof(Money))
{
result = ((Money)attributeValue).Value.ToString("C");
}
else
{
return attributeValue.ToString();
}
return result;
}
}
}
Here is the Plugin base class.
using ExceptionMgmtServices;
namespace Experiments.Plugins
{
using System;
using System.Collections.ObjectModel;
using System.Globalization;
using System.Linq;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
/// <summary>
/// Base class for all Plugins.
/// </summary>
public class Plugin : IPlugin
{
protected class LocalPluginContext
{
internal IServiceProvider ServiceProvider
{
get;
private set;
}
internal IOrganizationService OrganizationService
{
get;
private set;
}
internal IPluginExecutionContext PluginExecutionContext
{
get;
private set;
}
internal ITracingService TracingService
{
get;
private set;
}
private LocalPluginContext()
{
}
internal LocalPluginContext(IServiceProvider serviceProvider)
{
if (serviceProvider == null)
{
throw new ArgumentNullException("serviceProvider");
}
// Obtain the execution context service from the service provider.
this.PluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
// Obtain the tracing service from the service provider.
this.TracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
// Obtain the Organization Service factory service from the service provider
IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
// Use the factory to generate the Organization Service.
this.OrganizationService = factory.CreateOrganizationService(this.PluginExecutionContext.UserId);
}
internal void Trace(string message)
{
if (string.IsNullOrWhiteSpace(message) || this.TracingService == null)
{
return;
}
if (this.PluginExecutionContext == null)
{
this.TracingService.Trace(message);
}
else
{
this.TracingService.Trace(
"{0}, Correlation Id: {1}, Initiating User: {2}",
message,
this.PluginExecutionContext.CorrelationId,
this.PluginExecutionContext.InitiatingUserId);
}
}
}
private Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>> registeredEvents;
/// <summary>
/// Gets the List of events that the plug-in should fire for. Each List
/// Item is a <see cref="System.Tuple"/> containing the Pipeline Stage, Message and (optionally) the Primary Entity.
/// In addition, the fourth parameter provide the delegate to invoke on a matching registration.
/// </summary>
protected Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>> RegisteredEvents
{
get
{
if (this.registeredEvents == null)
{
this.registeredEvents = new Collection<Tuple<PipelinePhase, Message, string, Action<LocalPluginContext>>>();
}
return this.registeredEvents;
}
}
/// <summary>
/// Gets or sets the name of the child class.
/// </summary>
/// <value>The name of the child class.</value>
protected string ChildClassName
{
get;
private set;
}
/// <summary>
/// Initializes a new instance of the <see cref="Plugin"/> class.
/// </summary>
/// <param name="childClassName">The <see cref=" cred="Type"/> of the derived class.</param>
internal Plugin(Type childClassName)
{
this.ChildClassName = childClassName.ToString();
}
/// <summary>
/// Executes the plug-in.
/// </summary>
/// <param name="serviceProvider">The service provider.</param>
/// <remarks>
/// For improved performance, Microsoft Dynamics CRM caches plug-in instances.
/// The plug-in's Execute method should be written to be stateless as the constructor
/// is not called for every invocation of the plug-in. Also, multiple system threads
/// could execute the plug-in at the same time. All per invocation state information
/// is stored in the context. This means that you should not use global variables in plug-ins.
/// </remarks>
public void Execute(IServiceProvider serviceProvider)
{
if (serviceProvider == null)
{
throw new ArgumentNullException("serviceProvider");
}
// Construct the Local plug-in context.
LocalPluginContext localcontext = new LocalPluginContext(serviceProvider);
localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Entered {0}.Execute()", this.ChildClassName));
try
{
// Iterate over all of the expected registered events to ensure that the plugin
// has been invoked by an expected event
// For any given plug-in event at an instance in time, we would expect at most 1 result to match.
Action<LocalPluginContext> entityAction =
(from a in this.RegisteredEvents
where (
(int)a.Item1 == localcontext.PluginExecutionContext.Stage &&
a.Item2.ToString() == localcontext.PluginExecutionContext.MessageName &&
(string.IsNullOrWhiteSpace(a.Item3) || a.Item3 == localcontext.PluginExecutionContext.PrimaryEntityName)
)
select a.Item4).FirstOrDefault();
if (entityAction != null)
{
localcontext.Trace(string.Format(
CultureInfo.InvariantCulture,
"{0} is firing for Entity: {1}, Message: {2}",
this.ChildClassName,
localcontext.PluginExecutionContext.PrimaryEntityName,
localcontext.PluginExecutionContext.MessageName));
entityAction.Invoke(localcontext);
// now exit - if the derived plug-in has incorrectly registered overlapping event registrations,
// guard against multiple executions.
return;
}
}
catch (Exception e)
{
localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Exception: {0}", e.ToString()));
//string friendlyMessage = ExceptionManagement.LogException(e, PriorityLevel.HIGH, localcontext.OrganizationService);
// Handle the exception.
//throw new Exception(friendlyMessage);
throw;
}
finally
{
localcontext.Trace(string.Format(CultureInfo.InvariantCulture, "Exiting {0}.Execute()", this.ChildClassName));
}
}
public enum PipelinePhase
{
PreValidation = 10,
PreOperation = 20,
MainOperation = 30,
PostOperation = 40
}
public enum Message
{
AddListMembers,
AddMember,
AddMembers,
AddPrivileges,
AddProductToKit,
AddRecurrence,
AddToQueue,
AddUserToRecordTeam,
Assign,
AssignUserRoles,
Associate,
BackgroundSend,
Book,
Cancel,
CheckIncoming,
CheckPromote,
Clone,
Close,
CopyDynamicListToStatic,
CopySystemForm,
Create,
CreateException,
CreateInstance,
Delete,
DeleteOpenInstances,
DeliverIncoming,
DeliverPromote,
DetachFromQueue,
Disassociate,
Execute,
ExecuteById,
Export,
ExportAll,
ExportCompressed,
ExportCompressedAll,
GrantAccess,
Handle,
Import,
ImportAll,
ImportCompressedAll,
ImportCompressedWithProgress,
ImportWithProgress,
LockInvoicePricing,
LockSalesOrderPricing,
Lose,
Merge,
ModifyAccess,
Publish,
PublishAll,
QualifyLead,
Recalculate,
RemoveItem,
RemoveMember,
RemoveMembers,
RemovePrivilege,
RemoveProductFromKit,
RemoveRelated,
RemoveUserFromRecordTeam,
RemoveUserRoles,
ReplacePrivileges,
Reschedule,
Retrieve,
RetrieveExchangeRate,
RetrieveFilteredForms,
RetrieveMultiple,
RetrievePersonalWall,
RetrievePrincipalAccess,
RetrieveRecordWall,
RetrieveSharedPrincipalsAndAccess,
RetrieveUnpublished,
RetrieveUnpublishedMultiple,
RevokeAccess,
Route,
Send,
SendFromTemplate,
SetRelated,
SetState,
SetStateDynamicEntity,
TriggerServiceEndpointCheck,
UnlockInvoicePricing,
UnlockSalesOrderPricing,
Update,
ValidateRecurrenceRule,
Win
}
}
}
I have used Gayan Perera's CRM Code Generator to generate the entity classes using the CSharp.tt template, which I have used in the plugin. This plugin inserts the result of the fetchxml in the calculated field configuration entity, as an attribute in the BusinessEntity, which is accessible from the OutputParameter in the plugin. BusinessEntity is one of the output parameters in the Retrieve response.
Here is the how the entity form looks with the calculated field, which I have made as readonly.
Number of cases, Number of Contacts and Invoice Total have all been calculated by the plugin using the configuration entity and hence can be viewed when the user opens the account form. Since this plugin is registered only on the Retrieve message this will not work when you do a RetrieveMultiple or execute a Saved Query or User Query. If additional calculated fields are required on a different entity, a new plugin step has to be registered for the retrieve message of that entity. A new calculated field configuration record also has to be created for this entity.
The things I like about this approach is that it is very easily to manipulate the fetchxml and display the result in a field and I find it very useful for aggregating the child records to the parent. I have done this a quick proof of concept, to visualise the idea.
