Calculated fields using Plugin

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.

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 &amp;&amp;
                     a.Item2.ToString() == localcontext.PluginExecutionContext.MessageName &amp;&amp;
                     (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.
Advertisements

Chart Error in Dashboard

Today I encountered a strange issue, that took little longer than expected to solve. In the dashboard, I have multiple charts. All charts display correct data without any issue.

When you click the table icon on the chart, to look at the records that were the source for this chart, an error is displayed.
The user can run the saved view from Advanced Find, without any issue, and hence this is not a lack of privilege for this entity.

When I checked the Event Log in the CRM Server, I noticed an error message about the missing Privilege {8437FA7C-3681-4FC7-BFD8-53A23FDECD65}.

I then queried the Privilege view in the MSCRM database for the Organisation, and found this is prvReadUserSetting.

When I opened the security role for this user, read privilege was missing all together.
Granting Organisation wide read permission for this role, resolved this issue.

Understanding subgrid buttons

CRM2013’s subgrid behavior is a little different from the behaviour of CRM2011. This can cause some confusion, if you don’t know the reason for this behaviour. There are two behaviours that I know the reason for

  1. You click the “+” button in the subgrid, and a new popup window appears to create a new record. ┬áThis means that the parent entity lookup is a mandatory field on the child entity.
  2. You click the “+” button in the subgrid and an inline lookup appears. This means the parent entity is NOT a mandatory field in the child entity.

Today I noticed a third behaviour for which I didn’t know the reason for. The table button does not appear next to the “+”, for the entitlements subgrid, but does appear for the Contacts subgrid in the Account form.

Clicking the table button takes you to the associated view.

When the child entity is not a side navigation item, this behaviour is triggered.

Here we can see that Entitlement relationship does not appear in the left hand side navigation on the Account form, hence the Entitlement subgrid does not contain the table button for the associated view.