Fiscal date and "older than" date/time query operators in FetchXML

 

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

A FetchXML query in Microsoft Dynamics 365 (online & on-premises) can use special fiscal date values and older than clauses for date and time values in queries. 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.

Note

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

In This Topic

Using FetchXML fiscal date conditional operators

Using “older than” clauses for date and time values

Using FetchXML fiscal date conditional operators

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>

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>

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. The value for x is specified in the value attribute of the condition tag.

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

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>

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>

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>

Using “older than” clauses for date and time values

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 variious older than clauses in a FetchXML expression.

Clause	Syntax
Older than X minutes	<condition attribute="<AttributeName>" operator="olderthan-x-minutes" value="<VALUE>" /> Note This clause is not supported for date and time attributes with DateOnly behavior. More information:  Date and time query operators not supported for DateOnly behavior
Older than X hours	<condition attribute="<AttributeName>" operator="olderthan-x-hours" value="<VALUE>" /> Note This clause is not supported for date and time attributes with DateOnly behavior. More information:  Date and time query operators not supported for 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>" />

Note

Except for the Older than X months clause, all the other older than clauses are available only if you’re using CRM Online 2015 Update 1.or Microsoft Dynamics 365 (on-premises).

See Also

Retrieve data with queries using SDK assemblies
Build queries with FetchXML
Use a left outer join in FetchXML to query for records "not in"

Microsoft Dynamics 365

© 2016 Microsoft. All rights reserved. Copyright