Skip to content

Instantly share code, notes, and snippets.

@jdthorpe

jdthorpe/FetchXML.md

Last active July 3, 2024 15:56
Show Gist options
  • Save jdthorpe/5ad4768651f50d0405d9871b9d8d10d6 to your computer and use it in GitHub Desktop.
Save jdthorpe/5ad4768651f50d0405d9871b9d8d10d6 to your computer and use it in GitHub Desktop.
Download ZIP
Notes on FetchXML
Raw
FetchXML.md

Learn to Love Tolerate FetchXML

Overview

FetchXML seems to be a quirky subset of the SQL implemented in XML and optimized for UI tasks, including:

  • the ability select specific fields (foo, b.bar) or all fields (*) from an entity
  • perform LEFT OUTER and INNER joins
  • assign aliases to attributes (select a.b as foo) and (joined) entities (left join foo as BAR)
  • perform simple joins conditions (i.e. on a.foo = b.bar)
  • use filter conditions (i.e. the WHERE clause)
  • order the results
  • use aggregate functions (sum,count, etc.)

However, at first glance, compared to SQL, it lacks:

  • join on more than one field
  • filter by comparing fields in separate tables/entities
  • the HAVING clause
  • RIGHT and FULL OUTER joins
  • the NOT operator
  • nested queries (though some use-cases can be replicated)

In addition, it has the ability to:

  • implicitly join on entity relationships stored in the CDS/Dataverse (i.e. you can join without specifying the join fields if there is already a relationship defined in the Dataverse)

Tooling

The Awesome XRM ToolBox has a couple of extensions that make working with fetch xml a breeze. Specifically:

  • SQL4CDS: Write SQL and then click a button to render the equivalent FetchXML and execute the query
  • FetchXML Builder: Use a GUI to build a FetchXML statement, and then click a button to render the equivalent SQL and Odata Query, and execute the query

Docs

Did you find the official docs as useless as I did? Don't worry, Mark Carrington put together some fantastic blog posts on FetchXML:

The Fetch XML schema (Fetch)

If you want to understand the nuts and bolts of FetchXML, you'll want to understand the schema. However, if you're not a computer, xml schemas are difficult to read, and I find re-phrasing the schema in my own words to be a useful exercise, and here it is in my own words:

Note that the Green Book icon (📗) indicates an element type

Basics

A FetchXML query (on the account table, in this case) looks basically like this:

<fetch >
  <entity name='account'>
    ... lots of stuff
  </entity>
</fetch>

and each element can have a pre-defined set of attributes (e.g. name='account') and child elements (e.g. the <entity> element nested within the fetch element), and the type definitions in the schema define all the possible attribute and child element types.

Type definitions

📗 The <fetch> element

A single fetch (i.e. SELECT) statement

Notes:

  • Setting distinct=true prevents the default behavior of including the primary id of the main entity in the query results
  • For text fields, distinct is based on the first 2000 characters

Children of the <fetch> element

  • <entity>: The main table/entity of the query
  • <order/>: An field/attribute on which to order the results

Attributes of the <fetch> element

  • version (string): Always "1.0" as far as I can tell
  • mapping ("internal" or "logical"): Always "logical" as far as I can tell
  • distinct (boolean): SQL's SELECT DISTINCT ...
  • top (integer): SQL's TOP
  • count (integer, default: 5000, max: 5000)): [Paging] Number of records to return per page
  • page (integer): [Paging] Page number to return
  • paging-cookie (string): [Paging] The paging cooke from the previous page
  • no-lock (boolean, optional, default="false): SQL's NOLOCK
  • output-format ( "xml-ado", "xml-auto", "xml-elements", "xml-raw" or "xml-platform"): format for the return value?
  • returntotalrecordcount (boolean, optional, default="false): return the record count of the query
  • min-active-row-version (boolean, optional, default="false"): T-SQL's MIN_ACTIVE_ROWVERSION
  • aggregate (boolean): Unused? Deprecated?
  • utc-offset (IntOrEmpty): Unused? Deprecated?

📗 The <entity> element

Represents the main table being fetched (i.e. the FOO from select * from FOO inner join b on a.id = foo.id).

This is also the container for all the rest of the SQL clauses (JOINs, WHERE, ORDER BY,) so acts kind of like the SELECT statement.

Children of the <entity> element

Attributes of the <entity> element

  • name (string, required): The name of the table
  • enableprefiltering (boolean)
  • prefilterparametername (string)

📗 The <condition/> element

An individual boolean condition (e.g where foo.id = 1)

Note that the attribute "value" is used for all operators that compare to a single value (for example, eq). The element "value" is used for operators that compare to multiple values (for example, in). Some operators require neither the attribute "value" or the element "value" (for example, null).

Children of the <condition/> element

  • <value>: A value when the operator takes more than one comparable (e.g. then IN operator)

Attributes of the <condition/> element

  • attribute (string): Attribute name (BAR of SELECT foo.BAR)
  • entityname (string): Entity name (FOO of SELECT FOO.bar)
  • alias (string): Alias for the field (attribute)
  • operator (Operator): the operator used by the comparison
  • value (string): Value when ther is exactly one comparable (e.g. then EQ operator)
  • valueof (string): The name of a field/attribute (within the same entity!!) to which the column field/attribute is compared
  • aggregate (AggregateType): one of "count", "countcolumn", "sum", "avg", "min", "max"
  • rowaggregate (RowAggregateType: "countchildren"
  • column (string): ???
  • uiname: (internal / unused)
  • uitype: (internal / unused)
  • uihidden (TrueFalse01Type): (internal / unused)

📗 The <filter> element

Basic and/or logical operators for aggregating conditions into a single condition (e.g. where foo.id = 1 AND foo.amount > 1)

Note that conditions always need to be wrapped in a filter, even if there is only one condition. Hence, this is kind of a mix between the WHERE and AND/OR keywords.

Children of the <filter> element

  • <condition>: Conditions on the columns of the current entity (i.e. entity.month = "February")
  • <filter> (Self referential): for mixing and and or statments (e.g. a = 1 AND (b = 1 OR c = 3))

Attributes of the <filter> element

  • type ("and" or "or"; default: "and"): operator used to combine child <filter>'s and <condition/>'s
  • isquickfindfields (boolean): ???

📗 The <attribute/> element

Attribute selectors (e.g. the foo.bar as bat of select FOO.BAR AS BAT from baz).

Note that this is a mix of SQL's GROUPBY (e.g. GROUPBY foo.bar) and the SQL's field selectors (select FOO.BAR AS BAT), as the grouping is defined in this node (which I find strangly appealing).

To apply an aggregate function, supply the aggregate="[AggregateType]" attribute. In which case:

  • All the other <attribute />s should have either groupby="true" or an aggregate="[AggregateType]" specified
    • name="foo" aggregate="count" is equivalent to SQL's COUNT()
    • name="foo" aggregate="countcolumn" is equivalent to SQL's COUNT(foo NOT NULL)
    • name="foo" aggregate="countcolumn" distinct="true" is equivalent to SQL's COUNT(DISTINCT foo)
  • when grouping on a date/time field, you must specify the dategrouping attribute
  • when aggregating on a parent record, setting rowaggregate="countchildren" returns the count of child records

Attributes of the <attribute/> element

  • name (string, required): the attribute name (e.g. the BAR of foo.BAR as bat)
  • addedby (string): (internal / unused)
  • alias (string): attribute aliase (e.g. the BAT of foo.bar as BAT). Note that this can cause problems with filters, so Mark Carrington does NOT recommend using this feature
  • groupby (FetchBoolType): Group by this field/attribute (when at least one <attribute/> includes an aggregate="..." function)
  • dategrouping (DateGroupingType ]): required when setting groupby="true" on a datetime field.
  • aggregate (AggregateType): The name of an aggregate function to apply to the field
  • distinct (FetchBoolType): for use with aggregate="countcolumn" to form COUNT( DIST foo )
  • usertimezone (FetchBoolType): (unused?)
  • build (build type): (internal / unused)

📗 The <order/> element

The type specification for the <order> element. Important notes:

  • Each <order /> element represents a single field ordering within SQL's ORDER clause. If you want to order more than one field, just include multiple <order /> elements. Note that:
  • If you sort on the foreign-key of a link-entity, the sording will be based on the link-entity's primary name field (Hey, this is optimized for UI, right?)
  • You can sort on fields within link-entity's by incliuding an <order /> within the link-entity. However, there is no way to specify the precedence of the fields across multiple entities.
  • Ties are broken using the primary key where possible (for consistent paging purposes)
  • Aliases muck up sorting

Attributes

  • attribute (string): the field (attribute) on which to order the results
  • alias (string): ???
  • descending (boolean, default: false): Obvious

📗 The <link-entity> element

The type specification for the <link-entity> element. A mix between SQL's JOIN operator, and attribute selector for field of joined tables (i.e. select B.C from a INNER JOIN B ON A.ID = B.ID)

This is limited to:

  • LEFT OUTER and INNER joins
  • exact matching on a single field
  • a maximum of 10 links per query

Children of the <link-entity> element

  • <attribute/>: Include a particular element from the linked entity
  • <all-attributes/>: Include all attributes of the linked entity (i.e. SELECT *)
  • <order/>: An ordering for the results based on the current entity
  • <link-entity> (Self referential): Another entity linked to the current entity
  • <filter>: A filter condition based on the current entity

Attributes of the <link-entity> element

  • name (string, required): the linked entity name (e.g. the B in from a inner join B)
  • to (string): the FOO in from a inner join b on a.FOO = b.bar (note, this feels backward)
  • from (string): the BAR in from a inner join b on a.foo = b.BAR (note, this feels backward)
  • alias (string): the linked entity alias (e.g. the BAZ in from a inner join b as BAZ). This affects the field names in the query output
  • link-type (string): "inner" (SQL's INNER JOIN) or "outer" (SQL's LEFT OUTER JOIN), and maybe others??
  • visible (boolean): used? deprecated?
  • intersect (boolean): internal? N:N relationships depend on a special Relationship Entity, sometimes called an "Intersect"...
  • enableprefiltering (boolean): Improve report performance by using filters
  • prefilterparametername (string): Improve report performance by using filters

📗 Atoms

These represent many of the tokens in the SQL language (this designation is mine, and not part of the Fetch XML Spec)

all-attributes (element)

The Star operator (i.e. SELECT *), represented by the simple element <all-attributes>

value (element)

Represents a value when an operator includes multiple comparisons (like the IN operator). Example: <value>hello world</value>

IntOrEmpty (simpleType)

Empty string or string of ints. Used by utc-offset attribute of FetchType

TrueFalse01Type (Enumeration)

One of "1" or "0". Used by condition (unhidden attribute) and SerializedTrueFalse01Type

AggregateType (Enumeration)

One of "count", "countcolumn", "sum", "avg", "min" or "max"

RowAggregateType (Enumeration)

Just "countchildren"

DateGroupingType (Enumeration)

One of "day", "week", "month", "quarter", "year", "fiscal-period", "fiscal-year"

FetchBoolType (Enumeration)

One of "true", "false", "1", or "0". Used by some fetch attributes

build (simpleType)

Build number. One of "1.504021" or "1.003017"

operator (Enumeration)

Enumeration of operators used by the condition and savedquery.columnset.filter.condition types

Standard operators:
  • "eq"
  • "neq"
  • "ne"
  • "gt"
  • "ge"
  • "le"
  • "lt"
  • "like"
  • "not-like"
  • "in"
  • "not-in"
  • "between"
  • "not-between"
  • "null"
  • "not-null"
Hierarchy operators:
  • "under"
  • "eq-or-under"
  • "not-under"
  • "above"
  • "eq-or-above"
  • "eq-useroruserhierarchy"
  • "eq-useroruserhierarchyandteams"
String operators:
  • "begins-with"
  • "not-begin-with"
  • "ends-with"
  • "not-end-with"
Multi-select picklist field operators
  • "contain-values"
  • "not-contain-values"
Operators that compare to the current user
  • "eq-userid"
  • "ne-userid"
  • "eq-userteams"
  • "eq-useroruserteams"
  • "eq-businessid"
  • "ne-businessid"
  • "eq-userlanguage"
Date operators
  • "on"
  • "on-or-before"
  • "on-or-after"
  • "in-fiscal-year"
  • "in-fiscal-period"
  • "in-fiscal-period-and-year"
  • "in-or-before-fiscal-period-and-year"
  • "in-or-after-fiscal-period-and-year"
Operators that compare to the current date
  • "yesterday"
  • "today"
  • "tomorrow"
  • "last-seven-days"
  • "next-seven-days"
  • "last-week"
  • "this-week"
  • "next-week"
  • "last-month"
  • "this-month"
  • "next-month"
  • "last-year"
  • "this-year"
  • "next-year"
  • "this-fiscal-year"
  • "this-fiscal-period"
  • "next-fiscal-year"
  • "next-fiscal-period"
  • "last-fiscal-year"
  • "last-fiscal-period"

Operators that compare to the current date with a specified range (e.g. created within the last 3 days: '<condition attribute="createdon" operator="last-x-days" value="3" />):

  • "last-x-hours"
  • "next-x-hours"
  • "last-x-days"
  • "next-x-days"
  • "last-x-weeks"
  • "next-x-weeks"
  • "last-x-months"
  • "next-x-months"
  • "olderthan-x-months"
  • "olderthan-x-years"
  • "olderthan-x-weeks"
  • "olderthan-x-days"
  • "olderthan-x-hours"
  • "olderthan-x-minutes"
  • "last-x-years"
  • "next-x-years"
  • "last-x-fiscal-years"
  • "last-x-fiscal-periods"
  • "next-x-fiscal-years"
  • "next-x-fiscal-periods"

📗 savedquery

The savedquery element is used to create views within the Dynamics. These are not of interest to me just yet, so to simplify understanding of the core Fetch type, definitions exclusive to savedquery are in this separate section

savedquery (element)

A big and ugly object.

SerializedInteger (complexType)

Non-negative integer string. Used within savedquery

VersionType (simpleType)

Version string format. Used by IntroducedVersion attribute of savedquery

BoolOrEmpty (simpleType)

Empty string or string of boolean. Used by savedquery.layoutxml

SerializedTrueFalse01Type

Extension of TrueFalse01Type. Used for savedquery boolean attributes

(UNREFERENCED) LocalizedNamesType (complexType)

An instnace of the FieldXmlFieldUIType

(UNREFERENCED) DescriptionsType (complexType)

An instnace of the FieldXmlFieldUIType

(UNREFERENCED) ColorsType (complexType)

An instnace of the FieldXmlFieldUIType

FieldXmlFieldUIType (complexType)

a simple mapping between an id (guid), language code (int) and descripiton (string)

guid (simpleType)

A string in guid format

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment