{knowhow}–How to perform intersect queries using the same relationship twice in the in Dynamics CRM fetchxml.

Title seems confusing. Right? Well even to me it’s not a great title to explain the topic. For me, nothing is better than real time examples. So let me illustrate the same with an example.

So I was in this project where my customer wanted me to create a view that would show users in the system who have both – Role1 and Role2 in the system. Role1 and Role2 are two security roles in the system. Now imagine this query from SQL perspective. It would something like below.

select distinct sr.systemuserid, su.domainname
        FROM [Role] r INNER JOIN [SystemUserRoles] sr ON r.RoleID = sr.RoleID
        INNER JOIN [SystemUser] su ON su.systemuserid = sr.systemuserid
        WHERE r.Name = ‘role1’
        INTERSECT
        select distinct sr.systemuserid, su.domainname
        FROM Role] r INNER JOIN SystemUserRoles] sr ON r.RoleID = sr.RoleID
        INNER JOIN SystemUser] su ON su.systemuserid = sr.systemuserid
        WHERE r.Name = ‘role2’

For people who are not accustomed to SQL, nothing to worry. The below figure explain what the query does.

The query returns the intersection of the users who have both role1 and role2. Now to achieve the same stuff in a view, we would need to configure fetchxml query for the same. Unfortunately if we try to join the SystemUser table with the SecurityRoles twice, then CRM would give you the following error – “The relationship you are adding already exists in the query”

So is there no way you can achieve this? Well I asked some people and the answer I got was ‘No’. I understood that from UI we cannot we do this for sure.

However just for an experiment I tried the below fetchxml and executed it programmatically.

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’> 
  <entity name=’systemuser’>   
    <attribute name=’systemuserid’ />
    <attribute name=’fullname’ />
    <order attribute=’firstname’ descending=’false’ />
    <filter type=’and’>
      <condition attribute=’isdisabled’ operator=’eq’ value=’0′ />
    </filter>
    <link-entity name=’systemuserroles’ from=’systemuserid’ to=’systemuserid’ visible=’false’ intersect=’true’>     
      <link-entity name=’role’ from=’roleid’ to=’roleid’ alias=’ac’>
        <attribute name=’name’ />
        <filter type=’and’>         
          <filter type=’or’>           
            <condition attribute=’name’ operator=’eq’ value=‘role2’ />           
          </filter>         
        </filter>       
      </link-entity>     
    </link-entity>
    <link-entity name=’systemuserroles’ from=’systemuserid’ to=’systemuserid’ visible=’false’ intersect=’true’>     
      <link-entity name=’role’ from=’roleid’ to=’roleid’ alias=’anc’>       
        <attribute name=’name’ />
        <filter type=’and’>         
          <filter type=’or’>           
            <condition attribute=’name’ operator=’eq’ value=‘role1’ />           
          </filter>         
        </filter>       
      </link-entity>     
    </link-entity>   
  </entity> 
</fetch>

And voila! It worked. It returned me all the users who have both role1 and role2 in the system.

So what I did is I created a view for this and updated the fetchxml of the view programmatically.  You need to access the savedquery entity and update the fetchxml property programatically.

P.S – The limitation of this approach is that since the fetchxml is not supported through UI, it cannot be imported as part of the solution. You would need to update the fetchxml using some executable programmatically. So if your customer is stuck on achieving this kind of functionality, this can be your way out.

Hope this helps!