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

You can enroll now !We are giving 30% discount on our Internship Program

Don’t miss the chance to participate in the upcoming Internship Program which will be done using Microsoft Dot Net Web Development Full Stack Technology. The new batch will be starting from May 20, 2024.  We will have most experienced trainers for you to successfully complete the internship with live project experience.

Why to choose Our Internship Program?

Industry-Relevant Projects
Tailored Assignments: We offer projects that align with your academic background and career aspirations.
Real-World Challenges: Tackle industry-specific problems and contribute to meaningful projects that make a difference.

Professional Mentorship
Guidance from Experts: Benefit from one-on-one mentorship from seasoned professionals in your field.
Career Development Workshops: Participate in workshops that focus on resume building, interview skills, and career planning.

Networking Opportunities
Connect with Industry Leaders: Build relationships with professionals and expand your professional network.
Peer Interaction: Collaborate with fellow interns and exchange ideas, fostering a supportive and collaborative environment.

Skill Enhancement
Hands-On Experience: Gain practical skills and learn new technologies through project-based learning.
Soft Skills Development: Enhance communication, teamwork, and problem-solving skills essential for career success.

Book Your Seat Now

Free Demo Class Available