FetchXML - Complete Reference

FetchXML Summary

FetchXML is a query language used in Microsoft Dynamics CRM and has following features:

FetchXML can be used for retrieving data from Microsoft Dynamics CRM.
FetchXML cannot be used for Create, Update and Delete operations.
FetchXML can perform aggregations like Sum, Average, Minimum, Maximum, Count(*), Count(attributename)
FetchXML query can do GoupBy, Sorting and Filter on linked entity.
FetchXML queries are defined in XML format.
FetchXML query can retrieve upto 5000 records in a single request.
FetchXML allows paging results using paging cookie.
FetchXML can be saved as user-owned view in the userquery entity and as an organization-owned view in the savedquery entity.

Retrieve All Attributes - FetchXML Query

In the following example, the FetchXML statement retrieves all attributes for account records:


<fetch mapping='logical'>
    <entity name='account'>
        <all-attributes/>
    </entity>
</fetch>

Explanation

<all-attributes /> clause makes FetchXML query to retrive all attributes for an entity.

As a best-practice retrieve only the required attributes for better performance. Retrieving selected attributes is explained below.

Retrieve All Records - FetchXML Query

In the following example, the FetchXML statement retrieves all accounts, with selected attributes:


<fetch mapping='logical'>
   <entity name='account'>
      <attribute name='accountid'/>
      <attribute name='name'/>
   </entity>
</fetch>

Explanation

FetchXML query will return all the records if no filter applied.
To retrieve the selected attributes for an entity specify each attribute separately.
```
<attribute name='accountid'/>
```
```
<attribute name='name'/>
```

Filter Records Based On Related Entity

In the following example, the FetchXML statement retrieves all accounts where the last name of the owning user is not equal to Cannon:


<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>

Explanation

To filter entity records add <link-entity> clause, ex <link-entity name='systemuser' to='owninguser'> . Where name attributes contains linked entity logical name and to attribute contains logical name of attribute in main entity which is linked to related entity
Specify filter clause within link-entity.
Within filter specify the filter criteria using <condition attribute='lastname' operator='ne' value='Cannon' /> . Where attribute contains logical name of attribute in related entity, operator defines the comparison type here ne means not-equal and value defines the value to compare.

Complete list of FetchXML operators is defined below.

Complete List Of FetchXML Operators

FetchXML Operator	Descritption
lt	Less Than
gt	Greater Than
le	Less Than or Equal To
ge	Greater Than or Equal To
eq	Equals
ne	Not Equals
neq	Not Equal To
null	Does Not Contain Data
not-null	Contains Data
in	Is In
not-in	Is Not In
between	Between
not-between	Is Not Between
like	Like
not-like	Not Like
yesterday	Yesterday
today	Today
tomorrow	Tomorrow
next-seven-days	Next Seven Days
last-seven-days	Last Seven Days
next-week	Next Week
last-week	Last Week
this-month	This Month
last-month	Last Month
next-month	Next Month
on	On
on-or-before	On or Before
on-or-after	On on After
this-year	This Year
last-year	Last Year
next-year	Next Year
eq-userid	Equals Current User
ne-userid	Does Not Equal Current User
eq-businessid	Equals Current Business Unit
ne-businessid	Does Not Equal Current Business Unit
this-week	This Week
last-x-months	Last X Months
eq-userlanguage	Equals User Language
eq-userteams	Equals Current User’s Teams
in-fiscal-year	In Fiscal Year
in-fiscal-period	In Fiscal Period
in-fiscal-period-and-year	In Fiscal Period and Year
in-or-after-fiscal-period-and-year	In or After Fiscal Period and Year
in-or-before-fiscal-period-and-year	In or Before Fiscal Period and Year
last-fiscal-year	Last Fiscal Year
this-fiscal-year	This Fiscal Year
next-fiscal-year	Next Fiscal Year
last-x-fiscal-years	Last X Fiscal Years
next-x-fiscal-years	Next X Fiscal Years
last-fiscal-period	Last Fiscal Period
this-fiscal-period	This Fiscal Period
next-fiscal-period	Next Fiscal Period
last-x-fiscal-periods	Last X Fiscal Periods
next-x-fiscal-periods	Next X Fiscal Periods

Limit Maximum Number Of Records Returned.

In the below example, the FetchXML returns top 3 records of account.


<fetch mapping='logical' count='3'>
   <entity name='account'>
      <attribute name='name' alias='name'/>
   </entity>
</fetch>

Explanation

To limit maximum number of records returned specify count attribute in fetch node.
Maximum 5000 records can be returned from FetchXML query.

Inner Join In FetchXML.

In the below example, the FetchXML performs inner join on account and contact entity and returns contacts where accountid of account matches parentcustomerid of contact.


<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>

Explanation

To perform inner join in FetchXML query set following attributes in link-entity clause.
- Set name attribute to logical name of related entity.
- Set to attribute to linking attribute in primary entity.
- Set from attribute to linking attribute in related entity.
- Set link-type attribute to inner.
Now within link-entity clause specify attributes from related entity to retrieve

Left Outer Join In FetchXML

A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values.

In the below example, the FetchXML finds all accounts that have no leads


<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>

Explanation

You can perform a left outer join in FetchXML by using the entityname attribute as a condition operator. The entityname attribute is valid in conditions, filters, and nested filters.

To perform left outer join in FetchXML query set following attributes in link-entity clause.
- Set name attribute to logical name of related entity.
- Set to attribute to linking attribute in primary entity.
- Set from attribute to linking attribute in related entity.
- Set link-type attribute to outer.
In filter clause specify the condition with following attrbutes
- Set entityname attribute to logical name of related entity.
- Set attribute to linking attribute in related entity
- Set operator to valid FetchXML operator(null in this case).

Aggregates in FetchXML

In Microsoft Dynamics CRM FetchXML includes grouping and aggregation features that let you calculate sum, average, minimum, maximum and count.

The following aggregate functions are supported:

sum
avg
min
max
count(*)
count(attribute name)

About Aggregation

To create an aggregate attribute,

Set the keyword aggregate to true in fetch node.
Specify a valid entity name, attribute name, and alias (variable name).
You must also specify the type of aggregation you want to perform, for example count, min, max etc.

The following example shows a simple aggregate attribute in FetchXML.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='entity name'> 
    <attribute name='attribute name' aggregate='count' alias='alias name'/> 
  </entity> 
</fetch>

Average Aggregates

Following FetchXML calculates average of estimated value from opportunity.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
  </entity> 
</fetch>

Explanation

To calculate average set following:

Set aggregate to true in <fetch> clause.

<fetch distinct='false' mapping='logical' aggregate='true'>

In <aggregate> attribute set aggregate to avg

<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' />

Limitation with null values while computing average

Null values are not considered when Microsoft Dynamics CRM computes the average of data. However, zero (0) is used.
In the following example, with the following data, the average for Account 1 (two entries) is shown as 250 whereas the average for Account 2 (two entries) is shown as 125.

Topic	Potential Customer	Estimated value
Opportunity 1	Account 1	null
Opportunity 2	Account 1	250
Opportunity 3	Account 2	0
Opportunity 4	Account 2	250

Count Aggregate

The following FetchXML counts opportunity records


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='name' alias='opportunity_count' aggregate='count'/> 
  </entity> 
</fetch>

Explanation

To calculate count set following:

Set aggregate to true in <fetch> clause.

<fetch distinct='false' mapping='logical' aggregate='true'>

In <aggregate> attribute set aggregate to count

<attribute name='name' alias='opportunity_count' aggregate='count'/>

Countcolumn Aggregate

Countcolumn aggregate counts records where specified column don’t have null.

In the following FetchXML opportunity count is returned where name column don’t have null values.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='name' alias='opportunity_colcount' aggregate='countcolumn'/> 
  </entity> 
</fetch>

Explanation

To use countcolumn aggregate

Set aggregate to true in <fetch> clause.

<fetch distinct='false' mapping='logical' aggregate='true'>

Set aggregate to countcolumn in aggregate attribute.

<attribute name='name' alias='opportunity_colcount' aggregate='countcolumn'/>

Count Distinct Columns

The following FetchXML counts all opportunities with distinct names


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='name' alias='opportunity_distcount' aggregate='countcolumn' distinct='true'/> 
  </entity> 
</fetch>

Explanation

To count distinct column values

Set aggregate to true in fetch clause.
Set aggregate to countcoumn in aggregate attribute
Set distinct to true

Max Aggregate

Max aggregate returns the maximum value in a specified column.

Null values are not considered when Microsoft Dynamics CRM computes the maximum of data. However, zero (0) is used.

The following FetchXML shows how to get the maximum value from estimated column of opportunity entity.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
  </entity> 
</fetch>

Explanation

To get maximum value from a column set:

Set aggregate to true in fetch node.
Set aggregate to max in aggregate attribute.

Min Aggregate

Min aggregate returns the minimum value in a specified column.

Null values are not considered when Microsoft Dynamics CRM computes the minimum of data. However, zero (0) is used.

The following FetchXML shows how to get the maximum value from estimated column of opportunity entity.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='estimatedvalue' alias='estimatedvalue_min' aggregate='min' /> 
  </entity> 
</fetch>

Explanation

To get minimum value from a column set:

Set aggregate to true in fetch node.
Set aggregate to min in aggregate attribute.

Sum Aggregate

The following FetchXML calculates the sum of estimated value from opportunity entity.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
  </entity> 
</fetch>

Explanation

To get the sum of values in a column:

Set aggregate to true in fetch node.
Set aggregate to sum in aggregate attribute.

Multiple Aggregates

Multiple aggregates can be computed in a single FetchXML query.

Following FetchXML calculates count, sum and avg from opportunity entity.


<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'/> 
  </entity> 
</fetch>

Explanation

To calculate multiple aggregate in single FetchXML query

Set aggregate to true in fetch node.
For each type of aggregate add separate aggregate attribute and set aggregate value accordingly.

Group By

Aggregates can be grouped in FetchXML, to group aggregates set groupby attribute to true.

The following example returns opportunity records grouped by their owners.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
    <attribute name='ownerid' alias='ownerid' groupby='true' /> 
  </entity> 
</fetch>

Explanation

To group aggregates in FetchXML query

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute and set keyword groupby to true.

<attribute name='ownerid' alias='ownerid' groupby='true' />

Group by with linked entity

The following FetchXML returns opportunities grouped by opportunity owner’s managers.


<fetch distinct='false' mapping='logical' aggregate='true'> 
  <entity name='opportunity'> 
    <attribute name='name' alias='opportunity_count' aggregate='countcolumn' /> 
    <link-entity name='systemuser' from='systemuserid' to='ownerid'>
      <attribute name='parentsystemuserid' alias='managerid' groupby='true' />
    </link-entity> 
  </entity> 
</fetch>

Explanation

To create group by with linked entity FetchXML

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add link-entity clause in entity clause.

<link-entity name='systemuser' from='systemuserid' to='ownerid'>

Add group by attribute in link-entity clause and set keyword groupby to true.

<attribute name='parentsystemuserid' alias='managerid' groupby='true' />

Group By Year

Group By for dates uses the day, week, month, quarter or year value.

The following FetchXML query shows how to use the aggregate attribute and the groupby attribute to group the results by year.


<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='year' alias='year' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Year FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute set keyword groupby to true and dategrouping to year.

<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />

Group By Quarter

The following example shows how to use the aggregate attribute and the groupby attribute to group the results by quarter.


<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' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Quarter FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute set keyword groupby to true and dategrouping to quarter.

<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />

Group By Month

The following FetchXML shows how to use the aggregate attribute and the groupby attribute to group the results by month.


<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='month' alias='month' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Month FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute set keyword groupby to true and dategrouping to month.

<attribute name='actualclosedate' groupby='true' dategrouping='month' alias='month' />

Group By Week

The following FetchXML shows how to use the aggregate attribute and the groupby attribute to group the results by week.


<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='week' alias='week' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Week FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute set keyword groupby to true and dategrouping to week.

<attribute name='actualclosedate' groupby='true' dategrouping='week' alias='week' />

Group By Day

The following FetchXML shows how to use the aggregate attribute and the groupby attribute to group the results by week.


<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='day' alias='day' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create group by Day FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attribute in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='countcolumn' />

Add group by attribute set keyword groupby to true and dategrouping to day.

<attribute name='actualclosedate' groupby='true' dategrouping='day' alias='day' />

Multiple group by

The following FetchXML shows how to use the aggregate attribute and multiple groupby clauses.


<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' />
      <filter type='and'>
         <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity>
</fetch>

Explanation

To create multiple group by FetchXML query:

Set aggregate to true in fetch node.

<fetch distinct='false' mapping='logical' aggregate='true'>

Add required aggregate attributes in entity clause.

<attribute name='name' alias='opportunity_count' aggregate='count' />

Add multiple group by attributes by setting keyword groupby to true.

<attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />

<attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />

Order by

The following FetchXML query shows how to use the aggregate attribute and multiple orderby clauses.


<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>

Explanation

To create order by FetchXML query:

Add order clause in entity and set descending keyword to true or false.
```
<order alias='year' descending='false' />
```

Fiscal Date in FetchXML

A FetchXML query can query data on fiscal dates. For example, a FetchXML query can find all orders fulfilled in the last fiscal month or urgent cases with high severity that are older than 15 minutes.

For all fiscal date queries, the FetchXML query uses the organization’s fiscal year settings.

Last Fiscal Period FetchXML Query

The following example shows a FetchXML expression that finds all orders fulfilled in the last fiscal period, according to the organization’s fiscal year settings.

For example, if the organization uses fiscal months, the query returns orders fulfilled in the last fiscal month. If the organization uses fiscal quarters, the query returns orders fulfilled in the last fiscal quarter. If the organization uses fiscal semesters, orders fulfilled in the last fiscal semester are returned.


<fetch>
 <entity name="order">
  <attribute name="name"/>
  <filter type="and">
   <condition attribute="datefulfilled" operator="last-fiscal-period"/>
  </filter>
 </entity>
</fetch>

Explanation

To create last fiscal period FetchXML query:

In condition attribute set operator keyword to last-fiscal-period.

<condition attribute="datefulfilled" operator="last-fiscal-period"/>

In Fiscal Year FetchXML Query

The following example shows a FetchXML expression that finds all accounts created in fiscal year 2013.


<fetch>
 <entity name="account">
  <attribute name="name"/>
  <filter type="and">
   <condition attribute="createdon" operator="in-fiscal-year" value="2013"/>
  </filter>
 </entity>
</fetch>

Explanation

To create In Fiscal Year FetchXML query:

In condition attribute set operator keyword to in-fiscal-year.

<condition attribute="createdon" operator="in-fiscal-year" value="2013"/>

next-x-fiscal-years Query

The following example shows a FetchXML expression that finds all opportunities with an estimated close date in the next three fiscal years, based on the organization’s fiscal year settings.


<fetch>
 <entity name="opportunity">
  <attribute name="name"/>
  <filter type="and">
   <condition attribute="estimatedclosedate" operator="next-x-fiscal-years" value="3"/>
  </filter>
 </entity>
</fetch>

Explanation

To create next-x-fiscal-years FetchXML query:

In condition attribute set operator keyword to next-x-fiscal-years.

<condition attribute="createdon" operator="in-fiscal-year" value="2013"/>

Specify value of x in value attribute of condition tag.

in-fiscal-period FetchXML Query

The following example shows a FetchXML expression that finds all orders fulfilled in period three of any fiscal year, according to the organization’s fiscal year settings. The fiscal period value is specified in the value attribute of the condition tag. If the organization uses fiscal months, the query returns results from month three. If the organization uses fiscal quarters, the query returns results from quarter three. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out-of-range.


<fetch>
 <entity name="order">
  <attribute name="name"/>
  <filter type="and">
   <condition attribute="datefulfilled" operator="in-fiscal-period" value="3"/>
  </filter>
 </entity>
</fetch>

Explanation

To create in-fiscal-period FetchXML query:

In condition attribute set operator keyword to in-fiscal-period.

<condition attribute="datefulfilled" operator="in-fiscal-period" value="3"/>

Specify value of fiscal period in value attribute of condition tag.

in-fiscal-period-and-year Query

The following example shows a FetchXML expression that finds all orders fulfilled in period three of fiscal year 2013, according to the organization’s fiscal year settings. If the organization uses fiscal months, the query returns results from month three. If the organization uses fiscal quarters, the query returns results from quarter three. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out-of-range.


<fetch>
 <entity name="order">
  <attribute name="name"/>
  <filter type="and">
   <condition attribute="datefulfilled" operator="in-fiscal-period-and-year">
    <value>3</value>
    <value>2013</value>
   </condition>
  </filter>
 </entity>
</fetch>

Explanation

To create in-fiscal-period-and-year FetchXML query:

In condition attribute set operator keyword to in-fiscal-period-and-year.

<condition attribute="datefulfilled" operator="in-fiscal-period-and-year">

Specify value of fiscal period in value attribute of condition tag.
```
<value>3</value>
```
Specify value of fiscal year in another value attribute of condition tag.
```
<value>2013</value>
```

fiscal-period FetchXML Query

The following example shows a FetchXML aggregation expression that sums the total amount of orders fulfilled and groups the result by fiscal semester and fiscal year.


<fetch aggregate="true">
 <entity name="order">
  <attribute name="totalamount" aggregate="sum" alias="total"/>
  <attribute name="datefulfilled" groupby="true" dategrouping="fiscal-period"/>
 </entity>
</fetch>

Explanation

To create group by with linked entity FetchXML:

Create an aggregate query
Add link-entity node for entity which contains group by attribute (systemuser entity in this case.)
In link-entity node add required attribute containing groupby keyword with value set to true.

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

The following example shows a FetchXML that finds incidents that are older than 30 minutes.


<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="30" />
    </filter>
  </entity>
</fetch>

Use the following syntax to specify various older than clauses in a FetchXML expression.

Clause	Syntax
Older than X minutes	<condition attribute="<AttributeName>" operator="olderthan-x-minutes" value="<VALUE>" /> This clause is not supported for date and time attributes with DateOnly behavior.
Older than X hours	<condition attribute="<AttributeName>" operator="olderthan-x-hours" value="<VALUE>" /> This clause is not supported for date and time attributes with DateOnly behavior.
Older than X days	<condition attribute="<AttributeName>" operator="olderthan-x-days" value="<VALUE>" />
Older than X weeks	<condition attribute="<AttributeName>" operator="olderthan-x-weeks" value="<VALUE>" />
Older than X months	<condition attribute="<AttributeName>" operator="olderthan-x-months" value="<VALUE>" />
Older than X years	<condition attribute="<AttributeName>" operator="olderthan-x-years" value="<VALUE>" />

- See more at: http://msxrmtools.com/fetchxml/reference#sthash.1pLIxpBu.dpuf

Bhoopathi

Tuesday, August 23

MS CRM :: FetchXML usage

FetchXML - Complete Reference

FetchXML Summary

Retrieve All Attributes - FetchXML Query

Explanation

Retrieve All Records - FetchXML Query

Explanation

Filter Records Based On Related Entity

Explanation

Complete List Of FetchXML Operators

Limit Maximum Number Of Records Returned.

Explanation

Inner Join In FetchXML.

Explanation

Left Outer Join In FetchXML

Explanation

Aggregates in FetchXML

About Aggregation

Average Aggregates

Explanation

Limitation with null values while computing average

Count Aggregate

Explanation

Countcolumn Aggregate

Explanation

Count Distinct Columns

Explanation

Max Aggregate

Explanation

Min Aggregate

Explanation

Sum Aggregate

Explanation

Multiple Aggregates

Explanation

Group By

Explanation

Group by with linked entity

Explanation

Group By Year

Explanation

Group By Quarter

Explanation

Group By Month

Explanation

Group By Week

Explanation

Group By Day

Explanation

Multiple group by

Explanation

Order by

Explanation

Fiscal Date in FetchXML

Last Fiscal Period FetchXML Query

Explanation

In Fiscal Year FetchXML Query

Explanation

next-x-fiscal-years Query

Explanation

in-fiscal-period FetchXML Query

Explanation

in-fiscal-period-and-year Query

Explanation

fiscal-period FetchXML Query

Explanation

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

Use the following syntax to specify various older than clauses in a FetchXML expression.