Show ALL related activities in a subgrid

Microsoft Dynamics CRM offers great capabilities for activity entities, both out of the box and custom activities.
This post will describe a way to extend those capabilities even more, using a quite simple plugin.

During eXtremeCRM in Warsaw last week, a fellow CRMian and frequent user of FetchXML Builder approached me to ask if I knew a way to create "dynamic" queries with some contextual awareness. He had used unsupported methods to inject FetchXML to subgrids, to be able to show all activities related to current record, not just those where the record is related through the Regarding field.

As I will show in this post, this can be accomplished with a simple plugin and a custom view.

Background

Activities handle e-mail, phonecalls, tasks, meetings, and even faxes and letters. Common custom activities are sms, transactions, and any other date-bound entity that involves participants of some kind.

Participants can be contacts, accounts, leads, users, and the activities can be regarding eny entity with the "Activities" option selected.

image

When looking at an associated view of a record to display associated activities, this can display activities where the current record is either recipient, sender, participant or regarding the activity. This is done with specialized views built into CRM, as this gives a useful overview of all activities concerning the party.

image

imageThe problem is when you want to have a similar view embedded on the form in a subgrid. In this case, you can only add activities related to the record through the Regarding lookup on the activities.

This will of course only show a fraction of all activities that may be related to the contact, account, or any other entity form the subgrid is placed on.

 

Solution – the customizations

To solve this, we need to modify the query that is executed by CRM. Instead of filtering activities based on the value in the Regarding field, we want to verify that the current record is any kind of "party" to the activity.

First, create a custom view for the activities, with a signature that can be identified by our plugin so that it does not trigger when we don't want it to.

image

Open the "All Activities" view, and select Save As to create your custom view.

Add a filter criteria to only show records where Activity does not contain data.

Save the view.

The view is now pretty unusable. It will never show any records as activity is required for the activitypointer pseudo entity.

image

 

Next, add a subgrid to the form where you want to show all activities, and select the new view as the default and only view.

The customizations are now in place, and when we view any record with this form, nothing will show in the added subgrid.

image

Solution – the plugin

To get the query we want to be passed to CRM, we will intercept the RetrieveMultiple message in the execution pipeline before it goes to CRM.

By analyzing the request we see that the query is formed like this, after conversion to FetchXML:

<fetch distinct='false' no-lock='true' mapping='logical' page='1' count='4' returntotalrecordcount='true' >
  <entity name='activitypointer' >
    <attribute name='subject' />
    ...
    <filter type='and' >
      <condition attribute='isregularactivity' operator='eq' value='1' />
      <condition attribute='activityid' operator='null' />
      <condition attribute='regardingobjectid' operator='eq' value='633929A2-F2E1-E511-8106-000D3A22EBB4' />
    </filter>
    <order attribute='scheduledend' descending='false' />
    <link-entity name='systemuser' to='owninguser' from='systemuserid' link-type='outer' alias='activitypointerowningusersystemusersystemuserid' >
      <attribute name='internalemailaddress' />
    </link-entity>
  </entity>
</fetch>

A bunch of included attributes have been excluded for readability

So, let's create a plugin that triggers Pre RetrieveMultiple of activitypointer, and investigate if it has our "signature".

        public void Execute(IServiceProvider serviceProvider)
        {
            ITracingService tracer = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            if (context.MessageName != "RetrieveMultiple" || context.Stage != 20 || context.Mode != 0 ||
                !context.InputParameters.Contains("Query") || !(context.InputParameters["Query"] is QueryExpression))
            {
                tracer.Trace("Not expected context");
                return;
            }
            if (ReplaceRegardingCondition(query, tracer))
            {
                context.InputParameters["Query"] = query;
            }
        }

This code is pretty self-explaining. The interesting part however, comes in the ReplaceRegardingCondition method…

        private static bool ReplaceRegardingCondition(QueryExpression query, ITracingService tracer)
        {
            if (query.EntityName != "activitypointer" || query.Criteria == null || query.Criteria.Conditions == null || query.Criteria.Conditions.Count < 2)
            {
                tracer.Trace("Not expected query");
                return false;
            }

            ConditionExpression nullCondition = null;
            ConditionExpression regardingCondition = null;

            tracer.Trace("Checking criteria for expected conditions");
            foreach (ConditionExpression cond in query.Criteria.Conditions)
            {
                if (cond.AttributeName == "activityid" && cond.Operator == ConditionOperator.Null)
                {
                    tracer.Trace("Found triggering null condition");
                    nullCondition = cond;
                }
                else if (cond.AttributeName == "regardingobjectid" && cond.Operator == ConditionOperator.Equal && cond.Values.Count == 1 && cond.Values[0] is Guid)
                {
                    tracer.Trace("Found condition for regardingobjectid");
                    regardingCondition = cond;
                }
                else
                {
                    tracer.Trace($"Disregarding condition for {cond.AttributeName}");
                }
            }
            if (nullCondition == null || regardingCondition == null)
            {
                tracer.Trace("Missing expected null condition or regardingobjectid condition");
                return false;
            }
            var regardingId = (Guid)regardingCondition.Values[0];
            tracer.Trace($"Found regarding id: {regardingId}");

            tracer.Trace("Removing triggering conditions");
            query.Criteria.Conditions.Remove(nullCondition);
            query.Criteria.Conditions.Remove(regardingCondition);

            tracer.Trace("Adding link-entity and condition for activity party");
            var leActivityparty = query.AddLink("activityparty", "activityid", "activityid");
            leActivityparty.LinkCriteria.AddCondition("partyid", ConditionOperator.Equal, regardingId);
            return true;
        }

A bit of explanation:

Line 3-7: First sanity check of the query to verify that we might find our signature.
Line 13-29: Looping through the conditions to find our signature.
Line 15-19: Identifies the null-condition that was introduced in the view.
Line 20-24: Identifies the relation for the view, used to extract the record id.
Line 30-34: Verification of our signature.
Line 35: Extract the id of the parent record which is being displayed.
Line 39: Remove the null-condition used only to identify our signature.
Line 40: Remove the condition that the activities must have current record as Regarding.
Line 43: Create link-entity to activityparty, where the current record should be found.
Line 44: Add condition that the associated party shall be the current record.

As can be seen in the Execute method above, if the query is updated by our code, the updated query will be put back into the plugin execution context, and thus the updated query is what will be executed by CRM.

Finally, the plugin shall be registered in CRM.

image

Result

image

The subgrid on the form now contains all activities that are related to the current contact, in this case.

If you activate Plug-in Trace Log for all executions, you can now see logs with something like this in the message block:

Checking criteria for expected conditions
Disregarding condition for isregularactivity
Found triggering null condition
Found condition for regardingobjectid
Found regarding id: 633929a2-f2e1-e511-8106-000d3a22ebb4
Removing triggering conditions
Adding link-entity and condition for activity party

Resources

The complete source code for this plugin, including a bit more extra debugging information than shown here, can be downloaded here.

A CRM solution with the customized activity view, modified contact form, the plugin assembly and a plugin step can be downloaded here as managed version and unmanaged version.

Labels: , , , , ,