Monday, May 9, 2016

Show hierarchically related activities in subgrid

In this article I will demonstrate how to implement a plugin to extend the possibilities for showing activities that are related through account hierarchy in a subgrid on a Microsoft Dynamics CRM form.

In my previous article I showed how to create a simple plugin to show all directly related activities in a subgrid, and not just activities related through the Regarding field.

Objective

The goal is to be able to display all activities (yellow boxes) anywhere below any account (blue box) that is opened in CRM.

Some of you may recognize this model – it is respectfully borrowed from MVP Jukka Niiranen's blog post on this problem: CRM 2011 subgrids ain't what associated views used to be. As this article indicates, this has been a problem ever since we left CRM 4.0 behind.

Method

The approach to the problem is the same as described in detail in my last post, but in this case I will expand the query with a few outer joins to related contacts, opportunities etc. It's really not that complex at all, as long as you have a decent knowledge about the entity model we are querying.

I will also use one of the new condition operators available in CRM 2016, the UnderOrEqual condition that provides us the means to dynamically query hierarchical information, in this case account hierarchies.

As this article expands the concepts of the previous article, you should probably read that one before continuing.

Customizations

imageIn this case I create a view for activities that has a specific signature that identifies it as a query to let our plugin manipulate. The signature in this case is to filter by activities created by null.

A subgrid is added to the account form, and the new view selected as the default and only available view.

image

Query manipulation

I investigate the query being sent to CRM when displaying an account with the new subgrid. The query is pasted into FetchXML Builder for XrmToolBox to get a visual representation and an easy way to extend and test the query.

This is the query being sent to CRM:

image

By investigating the model and the objective, the query I want to compose looks like this:

image

Plugin

This plugin will be somewhat more complicated than the one from the previous article, where the query should be modified to find activities where the current contact was any kind of party.

In this case we need to evaluate the colorful model above, add required outer joins to activities through the different possible paths from the current account, and add an OR-filter to ensure that at least one of the joined paths are valid.

I have kept and refactored the plugin from previous article to be able to use both features in the same plugin. I will only go through the actual query manipulation, the rest of the plugin code is available for download at the bottom.

The code that removes the unwanted filters and adds the new filters looks like this:

private static bool ReplaceAccountRegardingCondition(QueryExpression query, ITracingService tracer, ConditionExpression nullCondition, ConditionExpression regardingCondition)
{
    var regardingId = (Guid)regardingCondition.Values[0];
    tracer.Trace($"Found regarding id: {regardingId}");

    tracer.Trace("Adding distinct to avoid duplicates");
    query.Distinct = true;

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

    tracer.Trace("Adding link-entity for activity party");
    var leParty = query.AddLink("activityparty", "activityid", "activityid");
    leParty.JoinOperator = JoinOperator.LeftOuter;

    tracer.Trace("Adding link-entity for account party");
    var leAccount = leParty.AddLink("account", "partyid", "accountid", JoinOperator.LeftOuter);
    leAccount.EntityAlias = "accountparty";

    tracer.Trace("Adding link-entity for contact party");
    var leContact = leParty.AddLink("contact", "partyid", "contactid", JoinOperator.LeftOuter);
    leContact.EntityAlias = "contactparty";

    tracer.Trace("Adding link-enitity for contact party account");
    var leContactAccount = leContact.AddLink("account", "parentcustomerid", "accountid", JoinOperator.LeftOuter);
    leContactAccount.EntityAlias = "contactaccount";

    tracer.Trace("Adding link-entity for regarding opportunity");
    var leOpportunity = query.AddLink("opportunity", "regardingobjectid", "opportunityid", JoinOperator.LeftOuter);
    leOpportunity.EntityAlias = "opportunity";

    tracer.Trace("Adding link-entity for regarding opportunity account");
    var leOpportunityAccount = leOpportunity.AddLink("account", "customerid", "accountid", JoinOperator.LeftOuter);
    leOpportunityAccount.EntityAlias = "opportunityaccount";

    tracer.Trace("Adding link-entity for regarding order");
    var leOrder = query.AddLink("salesorder", "regardingobjectid", "salesorderid", JoinOperator.LeftOuter);
    leOrder.EntityAlias = "order";

    tracer.Trace("Adding link-entity for regarding order opportunity");
    var leOrderOpportunity = leOrder.AddLink("opportunity", "opportunityid", "opportunityid", JoinOperator.LeftOuter);
    leOrderOpportunity.EntityAlias = "orderopportunity";

    tracer.Trace("Adding link-entity for regarding order opportunity account");
    var leOrderOpportunityAccount = leOrderOpportunity.AddLink("account", "customerid", "accountid", JoinOperator.LeftOuter);
    leOrderOpportunityAccount.EntityAlias = "orderopportunityaccount";

    tracer.Trace("Define filter for any related activity");
    var feOrConditions = new FilterExpression(LogicalOperator.Or);
    query.Criteria.AddFilter(feOrConditions);
    feOrConditions.AddCondition("accountparty", "accountid", ConditionOperator.UnderOrEqual, regardingId);
    feOrConditions.AddCondition("contactaccount", "accountid", ConditionOperator.UnderOrEqual, regardingId);
    feOrConditions.AddCondition("opportunityaccount", "accountid", ConditionOperator.UnderOrEqual, regardingId);
    feOrConditions.AddCondition("orderopportunityaccount", "accountid", ConditionOperator.UnderOrEqual, regardingId);

    return true;
}

The tracing lines in the code above should be pretty descriptive for what the code does.

Result

The activities subgrid on the "Top level account" and the "Sub-account" from Jukka's model above show this:

image

Opening the "Sub-sub-account" shows the following:

image

 

Resources

The complete source code for this plugin can be downloaded here.

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

5 comments:

  1. Anyone had any success doing activity rollups in the new social pane?

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. hello, great post again, anyway, if we still use CRM 2015 how to do "the UnderOrEqual condition " ? can we replace by something else?

    ReplyDelete
  4. Hello thank you for your post but i want to display the all related activity for contact, i mean when i create lead and i add an activity for it , and after when i qualify the lead i dont find the activity in subgrid of contact

    ReplyDelete
    Replies
    1. Hi YOSS
      I think that should be possible by manipulating the query to include activities related to lead as well, just as it currently does for opportunities.

      Delete