{Dynamics CRM} Create a view in Dynamics CRM that shows UNION of results

Recently we had a requirement where we needed to create a view in Dynamics CRM that shows a UNION of results. To explain it in a more detailed way, let me explain the scenario.

We had a custom entity called Opportunity group and under Opportunity Group we had multiple opportunities. Opportunity entity had four custom fields 1) Sales Person 1, 2) sales person 2, 3) sales person 3 and 4) sales person 4 which are all user lookups.

The customer wanted a view called “My Opportunity Groups” which would show the following

<Opportunity groups owned by the current user>

OR

<Opportunity groups which has opportunities which are owned by the current user or the user is in Sales Person 1 or Sales Person 2 or Sales Person 3 or Sales Person 4>

From the requirement itself, you can easily understand that this is a very simple SQL UNION query. Being a CRM consultant we are not that lucky in that respect since we don’t have the full power of SQL in our hands. So the following questions started arising.

First question – Is it possible through CRM UI? The answer is No because CRM UI does not give you an option with link entities to perform a UNION of results.

Second question – Is it possible through FetchXml? The answer is – if you are using CRM 2013 and above it is possible. Although a bit complex but we finally could put it together. We tested this through SDK and it was fetching the correct results. But had to say, not very efficient performance wise. Please find the fetchxml below for this requirement.

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>
  <entity name=’new_opportunitygroup’>
    <link-entity name=’opportunity’ from=’new_opportunitygroupid’ to=’new_opportunitygroupid’ link-type=’outer’ alias=’an’>
      <attribute name=’new_person1′ />
      <attribute name=’new_person2′ />
      <attribute name=’new_person3′ />
      <attribute name=’new_person4′ />
      <attribute name=’ownerid’ />
      <filter type=’and’>
        <filter type=’or’>
          <condition attribute=’ownerid’ operator=’eq-userid’ />
          <filter type=’or’>
            <condition attribute=’new_person1′ operator=’eq-userid’ />
            <condition attribute=’new_person2′ operator=’eq-userid’ />
            <condition attribute=’new_person3′ operator=’eq-userid’ />
            <condition attribute=’new_person4′ operator=’eq-userid’ />
          </filter>
        </filter>
      </filter>
    </link-entity>
    <link-entity name=’systemuser’ from=’systemuserid’ to=’owninguser’ alias=’ao’ link-type=’outer’>
      <attribute name=’systemuserid’ />
      <filter type=’and’>
        <condition attribute=’systemuserid’ operator=’eq-userid’ />
      </filter>
    </link-entity>
 
  <filter operator=’and’>
      <filter type=’or’>
        <condition entityname=’ao’ attribute=’systemuserid’ operator=’eq-userid’ />
        <filter type=’or’>
          <condition entityname=’an’ attribute=’ownerid’ operator=’eq-userid’ />
          <filter type=’or’>
            <condition entityname=’an’ attribute=’new_person1′ operator=’eq-userid’ />
            <condition entityname=’an’ attribute=’new_person2′ operator=’eq-userid’ />
            <condition entityname=’an’ attribute=’new_person3′ operator=’eq-userid’ />
            <condition entityname=’an’ attribute=’new_person4′ operator=’eq-userid’ />
          </filter>
        </filter>
      </filter>
    </filter>
  </entity>
</fetch>

The trick is in the portion of the fetchxml in red. With 2013 onwards you can write queries in fetchxml similar to left join with a where clause in SQL. Check out my following blog post for more details.https://debajitcrm.wordpress.com/2014/08/27/left-join-with-not-in-operator-in-microsoft-dynamics-crm-2013/

So now the last question. How to put this fetchxml in the view? One solution is to directly go the database and change the fetchxml of the My Opportunity Groups view. Other solution would be register a plugin on the pre-operation of retrievemultiple message of savedquery and for this view, specifically we change the change the fetchxml at runtime. However we did not go by any of the mentioned ways because first of all direct changes in the database is something we were trying to avoid. The other option with plugins would not be very performance effective since it would trigger on the retrieve of every saved view.

Instead we developed an utility to update the fetchxml of the saved query. The following is the code.

var orgServiceProxy = GetService();

var query = new QueryExpression("savedquery");
query.Criteria.AddCondition("name", ConditionOperator.Equal, "My Opportunity Groups");
query.Criteria.AddCondition("returnedtypecode", ConditionOperator.Equal, "new_opportunitygroup");
var savedQueryCollection = orgServiceProxy.RetrieveMultiple(query);

if (savedQueryCollection.Entities.Count > default(int))
{
      var savedQuery = savedQueryCollection.Entities.First();

      savedQuery["fetchxml"] = fetchExpression;

      orgServiceProxy.Update(savedQuery);
}

 

You have to write the GetService Method on your own to fetch the Organizationservice instance.

One catch of this implementation is that you wont be able to edit this view through CRM UI.

Hope this helps!

Advertisements

Author: Debajit

I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

2 thoughts on “{Dynamics CRM} Create a view in Dynamics CRM that shows UNION of results”

    1. Hi Jonas,
      Thanks for reading my blog.

      Even I am a big fan of the FetchXmlBuilder in codeplex and I think everybody is :). The utility that I mentioned in the console app is just to update the fetchxml of the saved query because the from the UI it would not be possible to edit the view.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s