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

 

image

 

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

 

image 

 

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!

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

3 thoughts on “{knowhow}–How to perform intersect queries using the same relationship twice in the in Dynamics CRM fetchxml.”

    1. Hi Jukka,
      First of all thanks for reading my post and would like to share the fact that i have been a big fan of your blog for quite sometime now :).

      Now coming to the topic, I did try to create the view using the techniques in the post you mentioned. My customer requirement was – a view that would show users having both security roles (Role 1 and role 2). Now this is what i tried initially.

      But strangely enough, it did not get me the correct results.
      Would really like to get help from you. Can you help me to construct this query. I would really love to get rid of the programmatic approach.

      Regards
      Debajit

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