Most Used FetchXML Queries in Dynamics 365 CRM

FetchXML is a XML based query language used in Microsoft Dynamics 365 CRM to fetch data. FetchXML is capable of doing many things as explained below.

• Can only be used to retrieve data not able to perform CUD (Create/Update/Delete) operation.
• Can be used in JavaScript to retrieve data in client side also used in server side code to retrieve data.
• Can perform Aggregations such as Sum, Average, Minimum, Maximum, Count(*), Count(attributename).
• Supports Linked entity data retrieval with conditions, filters, GroupBy, Sorting
• Can fetch 5000 record data at a time and supports paging concept to fetch more records page by page .
• Can be saved in userquery  entity as user owned view and savedquery  entity as organization owned view.
• Can also be used in SSRS query in Reporting.
• Supports aliased name for attributes retrieved.
• Supports similar query capabilities as query expressions.
• Can be executed by using the RetrieveMultiple method.
• Can be downloaded/saved from Advance Find query.

Here is a list of sample FetchXML Queries given below.

Everyday we use fetchXML in development. So here i have given a list of ready-made fetchXMLs which you can use by modifying the code as per your business requirement. FetchXML query should validated by the xsd schema.

List of Operators used in FetchXML are given below.

List of older than clauses in a FetchXML expression

How to Retrieve Data using Fetch XML in server side (Sample given below)

[code lang=”xml”]
// Retrieve all accounts owned by the user with read access rights to the accounts and
// where the last name of the user is not Cannon.
string varFetchXML = @"
<fetch mapping=’logical’>
<entity name=’account’>
<attribute name=’accountid’/>
<attribute name=’name’/>
<link-entity name=’systemuser’ to=’owninguser’>
<filter type=’and’>
<condition attribute=’lastname’ operator=’ne’ value=’Cannon’ />
</filter>
</link-entity>
</entity>
</fetch> ";

EntityCollection result =
_serviceProxy.RetrieveMultiple(new FetchExpression(varFetchXML));

foreach (var c in result.Entities)
{
System.Console.WriteLine(c.Attributes["name"]);
}
[/code]

How to Retrieve Data using Fetch XML in client side using WebAPI(Sample given below)

[code lang=”xml”]

var fetchXml =
"<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>"+
"<entity name=’account’>"+
"<attribute name=’name’ />"+
"<attribute name=’primarycontactid’ />"+
"<attribute name=’telephone1′ />"+
"<attribute name=’accountid’ />"+
"<order attribute=’name’ descending=’false’ />"+
"<filter type=’and’>"+
"<condition attribute=’name’ operator=’like’ value=’C%’ />"+
"</filter>"+
"</entity>"+
"</fetch>";
var encodedFetchXml = encodeURI(fetchXml);

var queryPath = "/api/data/v8.0/accounts?fetchXml=" + encodedFetchXml;
var requestPath = Xrm.Page.context.getClientUrl() + queryPath;

var req = new XMLHttpRequest();
req.open("GET", requestPath, true);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.onreadystatechange = function ()
{
if (this.readyState === 4)
{
this.onreadystatechange = null;

if (this.status === 200)
{
var returned = JSON.parse(this.responseText);
var results = returned.value;

for (var i = 0; i < results.length; i++)
{
var accountName = results[i]["name"];
var primaryContactId = results[i]["_primarycontactid_value"];
var telephone = results[i]["telephone1"];
var accountId = results[i]["accountid"];
//TODO: Implement logic for handling results as desired
}
}
else
{
alert(this.statusText);
}
}
};
req.send();
[/code]

Related Links:

Create a SSRS report using FetchXML.

Retrieve More than 5000 record using FetchXML Paging.

FetchXML V/S Query Expression