Tuesday, April 26, 2016

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.

50 comments:

  1. when I create an appointment record related at the same time to regarding object and activity party at the same time a an issues of duplicate of record will be showed on the subrid on contact form

    ReplyDelete
    Replies
    1. In the ReplaceRegardingCondition method, just add:

      query.Distinct = true;

      Delete
    2. Thank you very much Jonas for your help it works as expected

      Delete
    3. Hi Jonas,
      I am having the same issue and was wondering if you can clarify for me where to add the "query.distinct = true"? Do I need to modify the source code, compile the DLL and then repackage the solution? Or is there a better/easier way, like through customization or something?

      Thank you in advance, by the way...Your solution has helped fix a problem that was driving me and my users crazy!

      Delete
    4. Hi Unknown,
      Unfortunately (?) that must be done in the code.
      It could be inserted at line 8 in the last code snippet above.

      Delete
    5. Excellent, thank you! I got it working and learned a lot more about coding a plugin than I ever knew I wanted to, haha. Thanks again!

      Delete
  2. Great post. Out of interest, in CRM 4.0 you could see all activity history for an open Contact record where the Regarding: or To: fields were set to that same Contact.

    This has evidently been dropped in later versions of CRM?

    ReplyDelete
  3. Hi Jonas,

    Thanks so much for this! It works perfectly on my contact entity, Could you point me in the direction of displaying all related activities on the account entity also?

    Thanks again!

    Sam

    ReplyDelete
    Replies
    1. Just saw your other blog post. Now I feel stupid!

      Thanks again Jonas, absolute hero!

      Delete
  4. Hi - I must be missing something...I imported the unmanaged solution and see the new form and subgrid. There are no records in the view though. Is there something else I need to do to configure it? Thanks for your help!

    ReplyDelete
    Replies
    1. I activated the step under SDK Message Processing Steps and that seems to have fixed my issue.

      Delete
    2. Hi Kelsey
      I guess perhaps you missed to check the "Enable any SDK message processing steps" during import?
      Good that you solved it anyway!

      Delete
    3. Hi again Jonas - is there any security or permissions for this? I have users with read access to all activities but nothing appears in this subgrid - it says they don't have permission to access these records. If I use an out of the box view, it does show the activities. Thanks for your help & for a great solution!

      Delete
    4. Hi Kelsey
      Do these users get an error message in the subgrid with the no permission message?
      The plugin adds link-entity to systemuser and activityparty, so users must at least have read access for those entities.

      Delete
  5. Hi, first thank you for the walk through. Second - I've created the plugin and use it for both the account and contact forms. I've added in the "distinct" clause on the contact and it's working wonderfully. However, on the account form, it will not load when the screen loads. It prompts to click on a hyperlink to load the records. When clicking on the link it takes quite a while to load the records. Any thoughts on how to potentially improve performance?

    Thanks for your help.

    ReplyDelete
    Replies
    1. Sorry for extremely late response...
      Yes, as this method extends the query to make it quite a bit more complex, it can probably be seen on the performance depending on existing data volumes.
      The only option I see would be to analyse the database and create indexes according to suggestion from SQL Profiler or similar tools.
      If this is an online environment I assume MS could help you create the indexes.
      Hope this helps!

      Delete
  6. Hi, I have tried to import the unmanaged solution into our CRM 2015 environment but I keep getting this error message, "You can only import solutions with a package version of 7.0 or earlier into this org. Also you can't import any solutions into this org that were exported from MS CRM 2011 or earlier"

    Would you be able to provide a updated export which will work with CRM 2015?

    ReplyDelete
    Replies
    1. Hi Unknown
      Unfortunately this sample was developed in CRM 2016 so thus not possible to import in 2015.
      But the code is available in the post with descriptions of what customizations to be made.
      I currently don't have a 2015 installation to replicate it in, but I am sure you can do it :)

      Delete
  7. Hi Jonas,

    That would make sense!

    I am new to writing plugins for CRM and when I create a project with the above code I keep getting error messages where there are $ present for the tracer.Trace($".... lines.

    i.e. tracer.Trace($"Disregarding condition for {cond.AttributeName}");

    Am I missing something?

    ReplyDelete
    Replies
    1. Hi - this is a feature introduced in VS2015 (I think...)
      You can change the statement with this to make it work with earlier versions:

      tracer.Trace("Disregarding condition for {0}", cond.AttributeName);

      Delete
    2. Hi Jonas,

      Thank you very much for the response. That has resolved that issue.

      I have now registered the plugin but I am unable to activate the Plug-in Trace as we have on-premise CRM 2015 which does not have the plug-in trace feature.

      How or where am I able to confirm that the plugin is doing what it is meant to be doing because at the moment my activity grid is not showing any activities? and I know these contacts have activities against them.

      Delete
    3. I think the tracelog feature was introduced in 2015 Update 2. Do you have that?
      If so, you can register the plugin in sandbox, and it will log to the trace.

      Delete
    4. Hi,

      mmmm, these are the only updates which I can see that are out at the moment -> https://support.microsoft.com/en-us/help/3018363/microsoft-dynamics-crm-2015-updates-and-hotfixes

      I cannot see a Update 2 unless you can point me to the download page? The main issue is that we are running On-Prem 2015 version and all the latest server updates are only for CRM 2015 online.

      Delete
    5. My mistake, it should be Update 1, or the version number 7.1.x.x.
      More info: https://msdn.microsoft.com/library/gg309589.aspx#BKMK_PluginandCustomWorkflowActivityReporting

      Delete
  8. Hi Jonas,
    I copied the code into a plug-in, but I had to make a slight modification for it to work. The ReplaceRegardingCondition method was updating the query variable, but it was only updating the one passed to the method as a parameter and was not updating the variable in the Execute method. Thus, nothing was being changed in the query used in CRM. You may want to examine that part of the code.

    ReplyDelete
    Replies
    1. Hi Devon
      Hm... the code works for me (and evidently a lot of others who have tried it)
      My assumption is that since QueryExpression is a complex type it is always passed as ref (terminology might be failing me) so if any of it's properties are changed, the change will be visible when looking at the query from the calling method too.

      Even if I might not be expressing it correctly, it does actually work...

      Delete
    2. That might be the case. For some reason it didn't work for me until I changed the query variable to a class attribute instead of it being passed as a parameter. But I guess it's probably just something going on on my end if it's working for everyone else. I thought you might have made a recent change or something.

      Delete
  9. This is a great solution.

    Is it possible to apply this in some way to the Word template to retrieve these same results. Similar to this : http://www.crmsoftwareblog.com/2016/12/word-document-templates-that-use-fetchxml-for-complex-data-retrieval/ ?

    ReplyDelete
    Replies
    1. Thanks Unknown :)
      Without having fully read/understood the article you refer to, I would say that as long as it is based on FetchXML, it would definitely be possible to compose queries similar to the altered query in my post above.
      However, many of the more complex queries cannot be composed with Advanced Find, I use FetchXML Builder for XrmToolBox instead.

      Delete
  10. Hi Jonas,

    Firstly, Many Thanks for your brilliant post.

    I have a query on filtering the activities further based on their activitytype and showing them on each related sub-grids on an account form (Example - Emails onto emails sub-grid and Phone calls onto Phone call sub-grid). Could you please suggest whether it is possible or not? If possible, how I can achieve it. Please suggest.

    Many Thanks for your help!!

    ReplyDelete
    Replies
    1. Hi Bellam - and thanks :)

      I think you could just create one view for each subgrid, adding filter for activity type, and using the same "signature filter" that triggers the plugin and keep the plugin as is.
      If that is not possible you could try to create different "signatures" and update the plugin to be aware of which types of activities to retrieve fore each signature.

      Hope this helps :)

      Delete
  11. Hi Jonas,

    I have applied this solution on my test environment and because of some reason if I create a Open Phone call it shows duplicate records in the contact -> All Activities grid? Any idea why it is doing that? Many Thanks for your help

    ReplyDelete
    Replies
    1. Hi Venkatesh.
      This could be if the contact is tagged as both from and regarding, try updating your query with "query.Distinct=true;"

      Delete
    2. Thanks for your quick response. That resolved the issue.

      Delete
    3. One more quick question...any idea why it is not displaying the pagination on the grid?

      Delete
    4. Glad it worked :)
      Can't really explain the paging... the query is altered before being executed, so CRM should handle it like any normal query/view...

      Delete
    5. Ok thanks...I have figured out...it is just a property which was turned off. Thanks Again.

      Delete
    6. The grid is not showing the rollup view for the account, it is only showing the activity regarding of the account. I have created a phone call under a contact and it didn't show up on this grid. Any idea?

      Delete
    7. You probably want to have a look at the next article...

      Delete
    8. Thanks Jonas for sharing this link. Actually I followed the steps which you have mentioned and it works like a charm under Account but under contact if I try to implement the same approach it is not displaying any activities. Under contact I am just trying to hide account activity information and display all other activities associated to that contact. Please help.

      Delete
  12. Thank You, Jonas! Saved a lot of time, our business didn't want to use standard activities grid in account.

    ReplyDelete
  13. Hi Jonas. Great walkthrough and detail. I was wondering if you might be able to help me debug and issue I'm having? I keep getting an error that says Unable to load plugin assembly. The error doesn't appear when i view a contact, but neither do the Activities. The error appears on the Activities records view, and any other pages with Activity views, but not on the Form I customized with this plugin. Any help you can offer would be greatly helpful.

    ReplyDelete
    Replies
    1. Hi,
      This error usually indicates using a plugin referencing other SDK than what is on the server. Which version of CRM are you using? Did you simply import the solution or did you build the code?
      Since the problem does not appear where you would expect it to trigger, are you sure it is this plugin causing the error?
      /Jonas

      Delete
    2. Thank you so much for the reply! I am using Dynamics 365 on premises. I first tried to import the solution and register the plugin, which went perfectly smooth, but on the Contact form it shows "Click here to load records" but nothing happens when I click. Then on any other pages showing Activity views of any kind, I get the error that says Unable to load plugin assembly. When I view the error log details it says Assembly should be provided. Any thoughts on what I can do or change to get it working successfully?

      Delete
    3. Now I'm at a loss... Tried it in an onprem 8.1 and online 8.2 (don't have access to onprem 8.2) and it works just fine. Could you try disabling the steps for "my" plugin and see if the problem persists?

      Delete
    4. Thanks again for the reply, Jonas. I'm on onprem 8.2, if that helps at all. I tried disabling the step for the ActivitySubgridHelper and that actually did stop the error from showing up. If I pull up a Contact Form that uses the view with the Null trigger condition, the error doesn't show, but in the subgrid there is just a link that says "To load Activity records, click here." but it doesn't do anything when I click it.

      I have more detailed information about the error and when it occurs on this community forum: https://community.dynamics.com/crm/f/117/p/269728/766164#766164 if you have time to browse my error, and some of the suggested solutions, maybe that can help identify what I'm doing incorrectly.

      I really appreciate your help with this, as this is the 100% perfect solution for what I'm trying to do, and there doesn't seem to be any better or easier way to accomplish it than this wonderful plugin. I just can't seem to get it to work for me. :)

      Delete
    5. It seems like you are registering the plugin on disk. I have not tried that since CRM 4.0 so I don't have any relevant experience.
      Have you tried registering the plugin in Database instead?

      Delete
    6. I followed your instructions exactly, and as such have only registered the plugin in Database. Could that maybe be my issue? That the assembly file/solution is stored on disk or a on a different server, but that I'm registering it to Database?

      Delete