{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

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

  1. Hi Debajit

    Nice solution for unions within crm thank you.

    My issue is with replicating union in fetchxml for an ssrs report that I need to publish within crm for my users. It’s based on a parent entity (event) and needs to show 3 identical fields (contact, account, jobtitle) from two different child entities (delegate and team). It has to be a union as the data needs to be sorted in the report so I cannot simply do two datasets. As a newbie to all this, could you please show a version of your fetchxml that will link the data together?

    Here’s hoping!

    Many thanks
    Jules

      1. Hi Debajit

        Sorry I wasn’t terribly clear was I! I have 3 custom entities; Event, Delegate and Team Member. Delegate holds details of people (contacts) who have tickets for an event, Team Member is other contacts who are involved in some way (e.g. speakers, staff attendees).

        Delegate and Team Member both have lookups to Event, Contact and Account. Event is 1:N for Delegate and Team Member.

        I need to create an ssrs report that combines contact name, account and job title for all the Delegates and Team Members by Event (the report needs to be loaded into CRM and run from within any Event, so producing an Attendee List for that specific event). The data needs to be joined as it has to be sorted by contact name. I know I could use two datasets but this would mean users having to perform a further operation on the data and you know what users are like! I need to make their lives as easy as possible, the little darlings.

        For this one, ultimately I could create a new view with the new fetchxml as you have done in your blog, but that’s wayyyy in the future :). If you can show me the dark art of getting this data all in one query it will be a massive help as I have many other ssrs reports that use Union and I should be able to use this as my template/How To.

        I hope you can help!

        Many thanks
        Jules

        Here are the two sets of fetchxml I need to join:

        1
        863820001

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s