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.

10 thoughts on “Show hierarchically related activities in subgrid

  1. 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

    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.

  2. Hello thank you for your post. Since there is a limit of using only 10 link entities in fetchXml, How to overcome this limitation?

    1. Hi User,

      Well, unfortunately there is no easy way out of that 🙁
      I think there is a server setting, if you are onpremise. Online you are probably out of luck.

      Jonas

  3. Hi Jonas,

    Thanks for this great article. Going further, I’m trying to implement same solution for opportunity entity. I have an account, created one opportunity for this account & then created an order from this opportunity. Now for this order I’m creating one phone call activity.
    So this activity is getting displayed on account form sub-grid not on opportunity form sub-grid(I have added same sub-grid as of like account on opportunity form).

    It would be great help if I could get to know what do I need to change either in customization or in plug-in code.

    Thanks in advance.

    1. Hi Ajinkya, sorry for very late reply.

      I don’t see any reason this wouldn’t work too. You perform the steps similar to what I did in this and previous article on the Account entity, but for the Opportunity entity.
      Of course some tweaking of the code to adjust for opportunity instead, but I’m sure you can figure that out too! 🙂

      Jonas

  4. Hello, takes you for this, but it seems doesn’t work on 9.1 versions.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.