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. 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. The 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. 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. 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.
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.
Result
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.
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
In the ReplaceRegardingCondition method, just add:
query.Distinct = true;
Thank you very much Jonas for your help it works as expected
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!
Hi Unknown,
Unfortunately (?) that must be done in the code.
It could be inserted at line 8 in the last code snippet above.
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!
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?
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
Just saw your other blog post. Now I feel stupid!
Thanks again Jonas, absolute hero!
Super! Enjoy 🙂
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!
I activated the step under SDK Message Processing Steps and that seems to have fixed my issue.
Hi Kelsey
I guess perhaps you missed to check the "Enable any SDK message processing steps" during import?
Good that you solved it anyway!
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!
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.
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.
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!
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?
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 🙂
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?
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);
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.
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.
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.
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
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.
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…
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.
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/ ?
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.
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!!
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 🙂
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
Hi Venkatesh.
This could be if the contact is tagged as both from and regarding, try updating your query with "query.Distinct=true;"
Thanks for your quick response. That resolved the issue.
One more quick question…any idea why it is not displaying the pagination on the grid?
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…
Ok thanks…I have figured out…it is just a property which was turned off. Thanks Again.
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?
You probably want to have a look at the next article…
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.
Thank You, Jonas! Saved a lot of time, our business didn't want to use standard activities grid in account.
You're welcome 🙂
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.
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
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?
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?
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. 🙂
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?
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?
Hello,
I created an assembly and i am calling it in a Custom Action. Everything is working fine on my on-premise environment. I exported the solution(dll and custom actions included). The assembly has been registered in sandbox in database. When the client imported them on his on-premise environment, he is getting error "Assembly should be provided" in the Developer Console as the custom action is returning 500 internal server error. Please help. Thank you
Hi Congo,
Is this in any way related to the heirarchy solution in this article?
Or just a general question around importing solutions?
I can't really find the connection, perhaps you should try a Dynamics forum?
Jonas
Hi Jonas,
Thanks so much for providing us with this great solution. I have tried it on On Prem 2016 and it works like a charm, however, when I tried it on 365 online, it works but I do get an error when I tried to resolve a Case (Please note, when I deactivate the plugin step, I am able to successfully resolve a Case). Here is the error details below:
“http://schemas.microsoft.com/2003/10/Serialization/Arrays:anyType’ contains data from a type that maps to the name ‘Microsoft.Crm.Common.ObjectModel:ActivityState’. The deserializer has no knowledge of any type that maps to this name. Consider changing the implementation of the ResolveName method on your DataContractResolver to return a non-null value for name ‘ActivityState’ and namespace ‘Microsoft.Crm.Common.ObjectModel’.”
Please can you advise on the resolution for this issue. Thanks.
Hi Addy
Did you make sure to recompile the code with latest SDK v9.0.2?
/Jonas
Hi Jonas,
Thanks for the quick response. You are right, I used SDK 365 for v8.2 to recompile the code, that must have been the issue. I will try to recompile using v9.02. God bless you.
Hi Jonas,
please may I ask if it is possible to extend the plugin to also show in the subgrid all activities within a Case where the Contact is the Case Customer? Thanks for your anticipated reply.
Hi – the method to do that is explained in the follow-up article: http://jonasrapp.net/2016/05/hierarchy-activities/
Hope that helps!
Thanks you so much Jonas, stay blessed.
Hi Jonas,
I have tried all day trying to get it to show all activities regarding a Case where the Contact is the Case Customer but I just can’t get it to work. Please could you point me in the right direction when you are able to.
Thanks.
Hi Jonas,
I’m glad to let you know that I got the desired results for showing the activities regarding a Case where the Contact is the Case Customer. Thanks so much for this wonderful blog that put me in the right direction.
Great to hear that Addy, sorry I did not find time to assist more, but awesome that you did it! 🙂
I’ve got this approach working fine,however, using a search box with the grid doesn’t return anything. Is this expected?
Hi Phil,
I haven’t tried it, but I would guess the search box alters the query so it is no longer recognized by the plugin.
If you create a plugin to trace what the actual query is when you search, you could get the actual query, and hopefully adjust the code to get it working.
My Canary solution is one way of tracing messages, although it does not trace RetrieveMultiple messages in this version: https://jonasrapp.net/2017/09/canary/
Jonas
Hi Jonas,
Looking forward to trying out this plugin! I’m curious if this would work with the Connections area. Explanation: Our Contacts can be connected to multiple accounts via the Connection area and we would like to see those Connection Contact Activities on the Accounts (these accounts aren’t listed in regarding- only the contact). Hoping we can use this plugin or add to the code to do this.
thanks,
Kaitlin
Hi Kaitlin,
It wouldn’t work just as is, but the same approach could most likely be applied using the relationships via Connections.
Good luck!
Jonas
I was wondering if you had a version yet that works in the new Unified Interface as it doesn’t seem to support QueryExpression.
I *think* it should be possible to use with UI as well, if I recall correctly the queries are now FetchExpression instead.
The easiest way given the existing code above would be to first convert the fetchxml to a QueryExpression by executing a FetchXmlToQueryExpressionRequest, and finally convert back using QueryExpressionToFetchXmlRequest.
Might not be the best option from performance perspective though…
Thank you for the quick response Jonas,
You are correct they are FetchExpression now. I will try to implement and see how badly the performance is hindered.
I finally got this working, it was an odd and complicated feat. Let me know if you are ever interested in posting for others.
Thanks Brandon – absolutely!
You can find my contact info in the menu above, under “Jonas – About”.
Thanks
Hi everyone, I’m hoping someone can post an updated dll that contains the extra line of code “query.Distinct = true;” in the appropriate place. I have spent the last 5 hours trying to blindly muddle my way through this; and failed miserably. While I work in IT and understand the nature of what needs to be done (recompile the dll with the extra code) I am not a developer and have neither the skill, the tools, or the background to perform this task.
Can someone who has the knowledge please link to an updated dll? Thanks.
Updated and seriously refactored code is now available here: https://github.com/rappen/ShowAllActivities
It includes a (hard coded) constant to specify if the Distinct flag should be set.
Hi Jonas,
Thanks for the post. I am trying to get this plugin to work on UCI, however I am getting error. I am wondering if the plugin is tested with Dynamics 365 UCI client?
Debugging the code, “regardingobjectid” is not a condition instead part of link-entity.
\n\t\n\t\t\n\t\n\t\n”
So only following else part of codes executes which also error on RemoveConditon method call.
else
{
tracer.Trace(“No condition for regardingobjectid”);
var accountCondition = query.GetCondition(“accountid”, ConditionOperator.Equal, tracer);
if (accountCondition != null &&
accountCondition.Operator.Equals(ConditionOperator.Equal) &&
accountCondition.Values.Count == 1 &&
accountCondition.Values[0] is Guid accountConditionID)
{
accountId = accountConditionID;
query.RemoveCondition(accountCondition, tracer);
}
System.Runtime.Remoting.RemotingException: ‘Object ‘/d6761d38_69c9_46c8_83a1_c4e385322543/eqtbguwihov6ghox2x8mc1kn_70.rem’ has been disconnected or does not exist at the server.’
Any pointers will be helpful
Thanks.
Thank you Jonas, I’ll try and figure out what to do with all this. Is it possible to simply post the dll file?
Alex
Hi Jonas,
Thanks for the post. Trying this with Dynamics 365 UCI client doesn’t seem to work as I am getting error on the following line:
QueryExpressionToFetchXmlResponse resp = (QueryExpressionToFetchXmlResponse)service.Execute(req);
Unhandled Exception: System.Runtime.Remoting.RemotingException: Object ‘/d6761d38_69c9_46c8_83a1_c4e385322543/r+15whlva2bljl6blbed9b42_246.rem’ has been disconnected or does not exist at the server.
Debugging the code shows all the conditions(removal of null value and account condition in my case, addition of activitypartylink and party condition) being successfully added.
Any idea how this can be resolved? I wonder if the code is executed on latest release of Dynamics 365
Thanks