Thursday, October 17, 2013

CRM 2013 FetchXML Enhancements

CRM 2013 provides the major awaited FetchXML enhancement. "Left Outer Join". 



CRM 2011 Fetch cannot accurately express queries of 'Left Outer Join' type. i.e. "Leads without Tasks". This limitation is mainly due to the FetchXML structure and due to its equivalent SQL query structure.

Lets see how the example 'Leads without Tasks' FetchXML gets misinterpreted in CRM 2011, and how CRM 2013 FetchXML enhancement helps to overcome it.


CRM 2011 FetchXML : 'Leads without Tasks'

Below is the fetchxml formed when to try to identify leads without tasks.


The above FetchXML gets transformed to the below SQL query. Note that the condition 'RegardingObjectID IS NULL' is appended to the 'ON Condition' yielding incorrect results






CRM 2013 FetchXML : 'Leads without Tasks'

Below is the fetchxml formed when to try to identify leads without tasks.


The above FetchXML gets transformed to the below SQL query. Note that the condition 'RegardingObjectID IS NULL' is appended to the 'Where Condition' yielding correct results. 

Note: These advanced FetchXML structure (i.e. with EntityName parameter in conditions) cannot be created via Advanced Find (or) Entity Customization (or) Report Wizard. These can only be created via code, i.e. Service Call (or) Custom SSRS Reports.

4 comments:

  1. Hi Vinoth - nice article!
    Would you know if a resultset showing columns from more that one table (entity) can be constructed in a view or advanced find?

    Thanks
    Gordon MacDonald

    ReplyDelete
    Replies
    1. Hi Gord

      Usual FetchXML showing columns from more that one entity can be constructed in a view or advanced find. But the same will is not possible with FetchXML that contains left outer join.

      Thanks & Regards
      Vinoth

      Delete