;

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
ltLess Than
gtGreater Than
leLess Than or Equal To
geGreater Than or Equal To
eqEquals
neNot Equals
neqNot Equal To
nullDoes Not Contain Data
not-nullContains Data
inIs In
not-inIs Not In
betweenBetween
not-betweenIs Not Between
likeLike
not-likeNot Like
yesterdayYesterday
todayToday
tomorrowTomorrow
next-seven-daysNext Seven Days
last-seven-daysLast Seven Days
next-weekNext Week
last-weekLast Week
this-monthThis Month
last-monthLast Month
next-monthNext Month
onOn
on-or-beforeOn or Before
on-or-afterOn on After
this-yearThis Year
last-yearLast Year
next-yearNext Year
eq-useridEquals Current User
ne-useridDoes Not Equal Current User
eq-businessidEquals Current Business Unit
ne-businessidDoes Not Equal Current Business Unit
this-weekThis Week
last-x-monthsLast X Months
eq-userlanguageEquals User Language
eq-userteamsEquals Current User’s Teams
in-fiscal-yearIn Fiscal Year
in-fiscal-periodIn Fiscal Period
in-fiscal-period-and-yearIn Fiscal Period and Year
in-or-after-fiscal-period-and-yearIn or After Fiscal Period and Year
in-or-before-fiscal-period-and-yearIn or Before Fiscal Period and Year
last-fiscal-yearLast Fiscal Year
this-fiscal-yearThis Fiscal Year
next-fiscal-yearNext Fiscal Year
last-x-fiscal-yearsLast X Fiscal Years
next-x-fiscal-yearsNext X Fiscal Years
last-fiscal-periodLast Fiscal Period
this-fiscal-periodThis Fiscal Period
next-fiscal-periodNext Fiscal Period
last-x-fiscal-periodsLast X Fiscal Periods
next-x-fiscal-periodsNext 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>" />