1

FetchXML

From Resco's Wiki
Jump to navigation Jump to search

FetchXML is a proprietary data query language used for Dynamics CRM server. It is only used for querying data from the server, you cannot perform create, update, or delete operations using FetchXML.

FetchXML is more or less equivalent to SQL. A fetch can be translated to an SQL command and executed on the database. An SQL query can be translated to FetchXML language. Basic syntax of FetchXML can be found in Microsoft documentation. It is based on XML format.

FetchXML and Resco

Resco has also adopted this language and uses it as the main (reference) query language:

Resco Mobile CRM uses FetchXML for communication with all CRM servers or databases:

  • In offline mode, FetchXML query is translated to SQL and executed in the local database
  • In online mode, FetchXML query is read by the appropriate server-specific service that translates it as needed:
    • Crm4Service for legacy Microsoft Dynamics CRM 4.0
    • Crm2011Service for newer Dynamics versions
    • XrmService for Resco Cloud
    • SalesforceService for Salesforce (translates FetchXML into SOQL - Salesforce Object Query Language, JSON-based)

In Resco environment, FetchXML queries can be created in several ways:

  • Woodford administrators may build fetches, most notably when using the Filter editor.
  • JavaScript developers sometimes write FetchXML queries for Offline HTML custom functions.
  • Resco developers use FetchXML queries in the source code of Resco mobile apps.

People responsible for troubleshooting synchronization problems can also encounter FetchXML queries and responses when tracing HTTP communication, for example using Fiddler.

Fetch anatomy

Simple fetch is a query where we specify

  • Entity (database table)
  • Which columns we want (possibly all columns)
  • Aliases (usually for linked entities, but also for result columns)
  • Filter, i.e., which rows we want (possibly all)
  • Sort order of the returned rows (optional)
  • Additional directives such as Count or Distinct (optional)

The result of such a query is a table, which is a subset of the original entity table. (Selected rows, selected columns, possibly different sort order.)

More complex queries can query several tables at once, whereby these tables must be linked by lookups.

Even more complex fetches can execute additional processing such as compute aggregates (counts, average values...) or group the result rows.

Condition operators

Operators are used in the filters of the queries. Many operators are described here; you can find some examples here.

Standard set of operators

This is a subset of Dynamics CRM FetchXML operators that Resco considers as standard:

DateTime operators
  • on, on-or-before, on-or-after
  • today, yesterday, tomorrow
  • {this|next|last}-{week|month|year}
  • {olderthan|last|next}-x-{hours|days|weeks|months|years}
Other operators
  • eq, ne, le, lt, ge, gt
  • {eq|ne}-{userid|businessid}
  • eq-userteams, eq-useroruserteams
  • [not-]{null|like|between}
  • [not-]in
  • neq
  • begins-with, not-begin-with
  • ends-with, not-end-with

For the exact meaning of individual operators, consult Microsoft documentation.

Operators unsupported by Resco

The FetchXML standard is not static; Microsoft occasionally adds new operators. The following operators are known as not supported by Resco. The list might not be exhaustive. We might add support for these operators in the future.

  • {next|last}-seven-days
  • olderthan-x-minutes
  • all operators that contain the word fiscal
Hierarchical
  • eq-useroruserhierarchy
  • eq-useroruserhierarchyandteams
  • under
  • eq-or-under
  • not-under
  • above
  • eq-or-above
Other
  • eq-userlanguage
  • contain-values
  • not-contain-values

Resco-specific syntax

We have extended the FetchXML language that we use internally to include some additional constructs.

The following macros used in condition values for particular operators:

  • @@TODAY+<n> or @@TODAY-<n> can be used in operators on-or-before and on-or-after. Examples: @@TODAY, @@TODAY+2, @@TODAY-123. The macro is replaced by today's date in the format "2019-07-31T00:00:00.0000000+02:00".
  • @@currentlanguage@@ can be used in operator eq. It is replaced by currently loaded language ID used in the mobile app, for example: "en-US", "fr-FR", etc.

Special Resco operators

  • not-older-than-x-days - Interpreted as the operator "on-or-after" and value "@@TODAY-x"
  • eq-customerid - Interpreted as the operator EQ used with value = {appSettings.CustomerId} (*)
  • eq-customeruserid - Interpreted as the operator EQ used with value = {settings.CustomerUserId} (*)
(*) These appSettings are set up in customer mode login.

Example: Get appointments scheduled max 7 days ago

<fetch>
  <entity name='appointment'>
    <filter type='and'>
      <condition attribute='scheduledstart' operator='on-or-after' value='@@TODAY-7'/>
    </filter>
  </entity>
</fetch>

The Resco-specific syntax (above) is translated into the following standard fetch. (@@TODAY macro is replaced by the current date valid at the moment of fetch execution.) 

<fetch version="1.0" aggregate="false">
  <entity name="appointment">
    <filter type="and">
      <condition attribute="scheduledstart" operator="on-or-after" value="2024-03-07T00:00:00.0000000+01:00" />
    </filter>
  </entity>
</fetch>

Resco-specific syntax can be used, for example, in the following instances:

  • When writing fetches in JavaScript code
  • When manually editing SyncFilter.xml customization file.

Treatment of unsupported operators

What happens if the destination party does not understand the operators? For example, if you attempt to send a custom Resco syntax to Dynamics... The behavior depends on the party.

Fetches to the database of the mobile CRM app
Only the standard set of operators is supported. Unsupported conditions are silently evaluated as "not-null".
Fetches to Dynamics server
Standard set of operators (as well as all other Dynamics operators) are supported. Unsupported operators return an error.
Fetches to Resco CRM server
Standard set of operators supported. Fetch is sent to the server without any modification. Unsupported conditions are silently evaluated as "not-null".
Fetches to Salesforce server
The standard set of operators is supported, with the following exceptions: last-x-hours, next-x-hours, not-between, eq-businessid, ne-businessid, eq-userteams, eq-useroruserteams. Salesforce throws the NotSupportedException for these operators.

FetchXML limitations in Dynamics

  • A FetchXML query can return at most 5000 (result) rows. We use paging to retrieve additional records (the next page). During a synchronization, it can make sense to use smaller page size (fewer records per page). See Advanced sync setup for more details.
  • FetchXML supports the following aggregate functions: sum, avg, min, max, count. If the query would return more than 50,000 records, the query fails with error "AggregateQueryRecordLimit exceeded".

Special FetchXML options for Dynamics

Since version 14.1, Resco Mobile CRM supports new optimization options that target FetchXML request performance, namely

  • latematerialize
  • options

These options address SQL server performance and are described in Microsoft documentation.

Woodford: There is no user interface that supports these options. If you want to use them, you have to edit SyncFilter.xml in FetchXML format.

Usage of new fetch options in sync:

  • FullSync passes these options to the server (provided they are used in sync filter)
  • IncSync with Sync Filter behaves the same way as FullSync.
  • Other IncSync forms do not use Sync Filter, hence they ignore new options.

Example

<fetch version="1.0" latematerialize="true" options="OptimizeForUnknown,DisableRowGoal,Recompile">
       <entity name="appointment">
              <filter type="and">
                     <condition attribute="ownerid" operator="eq-useroruserteams"/>
              </filter>
       </entity>
</fetch>

As far as the content of the options attribute is concerned, Resco treats it transparently as a string without checking its content. Consult Microsoft documentation for supported query hints.

Note Use these options only when instructed by Microsoft support or Resco support.

Linked entities in FetchXML

When working with data, you often want to combine data from two or more entities. In Resco, we support the traditional FetchXML link types: inner and outer joins. In addition, since release 18.0, we support link types in and exists.

See Linked entities in FetchXML for more information.





Was this information helpful? How can we improve?
Retrieved from "https://docs.resco.net/mediawiki/index.php?title=FetchXML&oldid=11754"