web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / xRMCenter / Key Notes for FetchXml

Key Notes for FetchXml

Views (1722)
nghieppham Profile Picture nghieppham 4,755

FetchXml is one of query language that was supported by Microsoft Dynamic CRM, it is a great technology for query data from Microsoft Dynamic CRM. Actually, It is very similar to Query Expression, but you can use it for coding at client side, until now FetchXml is the best option for creating report in CRM Online.

How FetchXml work?

For constructing the fetch xml, you can utilize advance find of CRM to build the query.

  • For the basic query
    • Go to Advance Find, Select the view or create new view, add condition and then download FetchXml as XML file.

adanveFind

  • For complex query, you can use:
    • LinQ pad for CRM, you can see all guideline at this link.
    • Getting FetchXml from sql2fetchxml.
    • Using QueryExpressionToFetchXmlRequest to construct the FetchXml.

Key notes of FetchXml

Above feature is just about basic for constructing the query to get data from Microsoft Dynamic CRM, this section will note key notes for fetchXml which developer should remember for getting data by FetchXml.

Paging in FetcXml

  • FetchXml support for paging by using the Paging cookie
  • The Paging cookie will be returned just for a set of records
  • Paging-cookie only help when you retrieve consecutive pages
  • Passing the paging-cookie when you get the next set of records will help you improve the performance.

New enhancement from version 2013

  • Supporting for Fetching Out join
    • Specifying filter criteria for linked entities (Forcing to use Outer join)

Fetch

Limitation of FetchXml

You can follow this site  or this link for Up-to-Date the limitation of fetchXml.

Limitation Notes
  •  Do not support for Unions
  •  Aggregation just support for one for each FetchXMls
  •  Right Outer Join is not supported.
  •  You can't compare two fields directly.
  •  You can't have OR condition across entities
 For instance, the following SQL query is not supported by FetchXML. SELECT a.* FRM entity1 aINNER JOIN entity2 b ON a.entity1id = b.entity1id WHERE a.name = '123' OR b.name = '123'
  •  50000 records limit for both of CRM Online & On-premise
  • But you can adjust setting for On-Premise
  If you want to get more records, Paging-cookies is the only way support until now.
  •  You can't have more than 10 linked entities in a FetchXML query
 It is possible to overcome this limit by creating or updating a QueryLinkEntityLimit setting, however this is generally not recommended. If you ever run into this situation, you would definitely want to re-visit your CRM data model or re-engineer your query.
  •  There is no way to use subquery.
  •  There is no support of a CASE / WHEN structure.
  •  The maximum number that will participate in the aggregation will be 50,000 records for aggregation
  This can be overcome by updating "AggregateQueryRecordLimit" setting, however it is generally not recommended.

Hope it is usefue for someone.

Comments

*This post is locked for comments