Difference between revisions of "FetchXML"

From Resco's Wiki
Jump to: navigation, search
(FetchXML and Resco)
(FetchXML and Resco)
Line 8: Line 8:
* in server queries
* in server queries
* in [[Sync Filter]]
* in [[Sync Filter]]
* to define records listed in a view, etc.
* to [[Filter editor|define records]] listed in a view, etc.
[[Resco Mobile CRM]] uses FetchXML for communication with all CRM servers or databases:  
[[Resco Mobile CRM]] uses FetchXML for communication with all CRM servers or databases:  

Revision as of 11:23, 5 December 2019

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 CRM server
    • 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 Mobile CRM 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

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

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

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

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 Speed up synchronization 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".