FetchXML

FetchXML is a proprietary data query language used for Dynamics CRM server. Resco has also adopted this language and uses it in server queries, in Sync Filter, to define records listed in a view, etc.

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.

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

• Woodford administrators may write fetches, for example when using the Rules editor with variables of the type Entity.
• 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.

Operators

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