Left Join with Not-in operator in FetchXml in Microsoft Dynamics CRM 2013

Just the other day, I was discussing with one of my colleagues about his experience of building fetch xml reports for one online implementations and we stumbled upon the topic where he needed to show the list of accounts with no cases associated to it. With SSRS reports it’s just a simple query. However for online, we have to limit our self with fetch xml reports and till 2011 version of CRM, it was not possible to construct a query of left join with NOT-IN operator.

But in dynamics 2013, you can construct the query to do this. The following is the query to retrieve the list of accounts with no leads. The sample code is taken from MSDN.

<fetch mapping=’logical’>
  <entity name=’account’>
    <attribute name=’name’/>
    <link-entity name=’lead’ from=’leadid’ to=’originatingleadid’ link-type=’outer’/>
    <filter operator=’and’>
      <condition entityname=’lead’ attribute=’leadid’ operator=’null’/>
    </filter>
  </entity>
</fetch>

For full documentation refer to the link : http://msdn.microsoft.com/en-in/library/dn531006.aspx

Hope this helps!

Advertisements

4 thoughts on “Left Join with Not-in operator in FetchXml in Microsoft Dynamics CRM 2013

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