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.

29 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
  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