{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://debajmecrm.com/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 = savedQueryC
ollection.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!