SharePoint Journey: 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.

Retrieve all attributes of account entity

[code lang=”xml”]
<fetch mapping=’logical’>
<entity name=’account’>
<all-attributes/>
</entity>
</fetch>
[/code]

Retrieve selected attributes of account entity

[code lang=”xml”]
<fetch mapping=’logical’>
<entity name=’account’>
<attribute name=’accountid’/>
<attribute name=’name’/>
</entity>
</fetch>
[/code]

Retrieve records of account entity with filter condition

[code lang=”xml”]
<fetch mapping=’logical’>
<entity name=’account’>
<attribute name=’accountid’/>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’name’ operator=’ne’ value=’Softchief’ />
</filter>
</entity>
</fetch>
[/code]

Retrieve contract records using linkedentity with customer using filter condition in linked entity

[code lang=”xml”]
<fetch mapping=’logical’>
<entity name=’contract’>
<attribute name=’title’/>
<attribute name=’customerid’/>
<link-entity name=’contact’ from=’contactid’ to=’customerid’>
<filter type=’and’>
<condition attribute=’lastname’ operator=’eq’ value=’Pradhan’ />
</filter>
</link-entity>
</entity>
</fetch>
[/code]

Limit the Maximum (Lets Say 5)Number Of Records to be retrieved in contact entity.

[code lang=”xml”]
<fetch mapping=’logical’ count=’5′>
<entity name=’contact’>
<attribute name=’name’ />
</entity>
</fetch>
[/code]

Limit the Maximum (Lets Say 5)Number Of Records to be retrieved in contact entity.

[code lang=”xml”]
<fetch mapping=’logical’ count=’5′>
<entity name=’contact’>
<attribute name=’name’ />
<attribute name=’lastname’ />
</entity>
</fetch>
[/code]

Use of Inner Join in FetchXML

[code lang=”xml”]
<fetch version=’1.0′ mapping=’logical’ distinct=’false’>
<entity name=’contact’>
<attribute name=’fullname’/>
<link-entity name=’account’ to=’parentcustomerid’ from=’accountid’ link-type=’inner’>
<attribute name=’name’/>
</link-entity>
</entity>
</fetch>
[/code]

Use of Left Outer Join in FetchXML

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

Use Avarage Aggregate in FetchXML

[code lang=”xml”]
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<entity name=’opportunity’>
<attribute name=’estimatedvalue’ alias=’estimatedvalue_avg’ aggregate=’avg’ />
</entity>
</fetch>
[/code]

Count No. of records retrieved in FetchXML

[code lang=”xml”]
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<entity name=’opportunity’>
<attribute name=’name’ alias=’opportunity_count’ aggregate=’count’/>
</entity>
</fetch>
[/code]

Using “older than” clauses for date and time values in FetchXML Query

[code lang=”xml”]
<fetch>
<entity name="incident">
<attribute name="title" />
<attribute name="ticketnumber" />
<attribute name="createdon" />
<attribute name="incidentid" />
<filter type="and">
<condition attribute="createdon" operator="olderthan-x-minutes" value="40" />
</filter>
</entity>
</fetch>
[/code]

fiscal-period FetchXML Query

[code lang=”xml”]
<fetch aggregate="true">
<entity name="order">
<attribute name="totalamount" aggregate="sum" alias="total"/>
<attribute name="datefulfilled" groupby="true" dategrouping="fiscal-period"/>
</entity>
</fetch>
[/code]

Order by in FetchXML Query

[code lang=”xml”]
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<entity name=’opportunity’>
<attribute name=’opportunityid’ alias=’opportunity_count’ aggregate=’count’/>
<attribute name=’estimatedvalue’ alias=’estimatedvalue_sum’ aggregate=’sum’/>
<attribute name=’estimatedvalue’ alias=’estimatedvalue_avg’ aggregate=’avg’/>
<attribute name=’actualclosedate’ groupby=’true’ dategrouping=’quarter’ alias=’quarter’ />
<attribute name=’actualclosedate’ groupby=’true’ dategrouping=’year’ alias=’year’ />
<order alias=’year’ descending=’false’ />
<order alias=’quarter’ descending=’false’ />
<filter type=’and’>
<condition attribute=’statecode’ operator=’eq’ value=’Won’ />
</filter>
</entity>
</fetch>
[/code]

Order by in FetchXML Query

List of Operators used in FetchXML are given below.

FetchXML Operator	ConditionOperator	Description
like	BeginsWith	The string occurs at the beginning of another string.
between	Between	The value is between two values.
like	Contains	The string contains another string.
not-like	DoesNotBeginWith	The string does not begin with another string.
not-like	DoesNotContain	The string does not contain another string.
not-like	DoesNotEndWith	The string does not end with another string.
like	EndsWith	The string ends with another string.
eq	Equal	The values are compared for equality.
eq-businessid	EqualBusinessId	The value is equal to the specified business ID.
eq-userid	EqualUserId	The value is equal to the specified user ID.
eq-userteams	EqualUserTeams	The record is owned by teams that the user is a member of.
ge	GreaterEqual	The value is greater than or equal to the compared value.
gt	GreaterThan	The value is greater than the compared value.
in	In	TheThe value exists in a list of values.
in-fiscal-period	InFiscalPeriod	The value is within the specified fiscal period.
in-fiscal-period-and-year	InFiscalPeriodAndYear	The value is within the specified fiscal period and year.
in-fiscal-year	InFiscalYear	The value is within the specified year.
in-or-after-fiscal-period-and-year	InOrAfterFiscalPeriodAndYear	The value is within or after the specified fiscal period and year.
in-or-before-fiscal-period-and-year	InOrBeforeFiscalPeriodAndYear	The value is within or before the specified fiscal period and year.
last-seven-days	Last7Days	The value is within the last seven days including today.
last-fiscal-period	LastFiscalPeriod	The value is within the last fiscal period.
last-fiscal-year	LastFiscalYear	The value is within the last fiscal year.
last-month	LastMonth	The value is within the last month including first day of the last month and last day of the last month.
last-week	LastWeek	The value is within the previous week including Sunday through Saturday.
last-x-days	LastXDays	The value is within last X days.
last-x-fiscal-periods	LastXFiscalPeriods	The value is within the last X (specified value) fiscal periods.
last-x-fiscal-years	LastXFiscalYears	The value is within the last X (specified value) fiscal periods.
last-x-hours	LastXHours	The value is within the last X hours.
last-x-months	LastXMonths	The value is within the last X (specified value) months.
last-x-weeks	LastXWeeks	The value is within the last X (specified value) weeks.
last-x-years	LastXYears	The value is within the last X years.
last-year	LastYear	The value is within the previous year.
le	LessEqual	The value is less than or equal to the compared value.
lt	LessThan	The value is less than the compared value.
like	Like	The character string is matched to the specified pattern.
next-seven-days	Next7Days	The value is within the next seven days.
next-fiscal-period	NextFiscalPeriod	The value is within the next fiscal period.
next-fiscal-year	NextFiscalYear	The value is within the next fiscal year.
next-month	NextMonth	The value is within the next month.
next-week	NextWeek	The value is within the next week.
next-x-days	NextXDays	The value is within the next X (specified value) days.
next-x-fiscal-periods	NextXFiscalPeriods	The value is within the next X (specified value) fiscal period.
next-x-fiscal-years	NextXFiscalYears	The value is within the next X (specified value) fiscal years.
next-x-hours	NextXHours	The value is within the next X (specified value) hours.
next-x-months	NextXMonths	The value is within the next X (specified value) months.
next-x-weeks	NextXWeeks	The value is within the next X weeks.
next-x-years	NextXYears	The value is within the next X years.
next-year	NextYear	The value is within the next year.
not-between	NotBetween	The value is not between two values.
ne	NotEqual	The two values are not equal.
ne-businessid	NotEqualBusinessId	The value is not equal to the specified business ID.
ne-userid	NotEqualUserId	The value is not equal to the specified user ID.
not-in	NotIn	The given value is not matched to a value in a subquery or a list.
not-like	NotLike	The character string does not match the specified pattern.
not-null	NotNull	The value is not null.
ne	NotOn	The value is not on the specified date.
null	Null	The value is null.
olderthan-x-months	OlderThanXMonths	The value is older than the specified number of months.
on	On	The value is on a specified date.
on-or-after	OnOrAfter	The value is on or after a specified date.
on-or-before	OnOrBefore	The value is on or before a specified date.
this-fiscal-period	ThisFiscalPeriod	The value is within the current fiscal period.
this-fiscal-year	ThisFiscalYear	The value is within the current fiscal year.
this-month	ThisMonth	The value is within the current month.
this-week	ThisWeek	The value is within the current week.
this-year	ThisYear	The value is within the current year.
today	Today	The value equals today’s date.
tomorrow	Tomorrow	The value equals tomorrow’s date.
yesterday	Yesterday	The value equals yesterday’s date.

List of older than clauses in a FetchXML expression

Older than X minutes

Older than X hours

Older than X days

Older than X weeks

Older than X months

Older than X years

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]

FetchXML V/S Query Expression

FetchXML	Query Expression
Easy to construct FetchXML Query using out-of-the-box Advance Find visual tool	Manually need to write the code to declare objects of Query Expression, Filters, Conditions, attributes etc.
Can be used in both client side and server side data retrieval	Can only be used in server side code data retrieval such as plugins, custom workflows, custom .net components
Get Optionset Text Directly	NO Direct retrieval of Optionset Text without Metadata Request
Easy to construct multiple liked entity	Complex when Multiple linked entity required
supports Aggregation	Don’t not support Aggregation