Inside FetchXML pt 3 – filter

So far we’ve looked at using the <attribute> and <link-entity> elements to control what columns to include in your query, but for a truly useful query you’ll likely want to select which rows to include instead of taking everything. Enter <filter>!

Filter or Condition?

A <filter> is a group of individual <condition>s. Each condition applies a filter on a single field. The job of the filter is to combine those conditions with an AND or OR.

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="firstname" operator="eq" value="Mark" />
    </filter>
  </entity>
</fetch>

This query will retrieve the names of all contacts that are active (statecode = 0) and have a first name of Mark.

Nesting Filters

As well as containing conditions, filters can also contain other filters. This lets you create complex criteria by combining AND and OR logic:

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <filter type="or">
        <condition attribute="createdon" operator="on-or-after" value="2019-01-01" />
        <condition attribute="createdby" operator="eq" value="8109eeaf-44d8-4a9b-8c7a-95209b26f6e5" />
      </filter>
    </filter>
  </entity>
</fetch>

This will retrieve only contacts that are:

  1. active, AND
  2. at least one of
    1. created no earlier than the start of 2019, OR
    2. created by a specific user

Complex Operators

The most common operator you’ll use will be eq, short for equals. However, there’s a huge range of operators you can use, especially if you’re filtering on date or user fields. I won’t list them all here, but you can find the full list in the Microsoft documentation. The main ones you’re likely to use are:

  • eq
  • ne
  • null
  • not-null
  • like

It’s worth highlighting that the like operator can be used on lookup fields as well as text fields – in this case it is applied to the primary name attribute of the related record, while eq will be applied to the unique identifier stored in the lookup field itself.

For date values there are many operators [olderthan/last/next]-x-[hours/days/weeks/months], e.g. last-x-days. The value you use in the condition becomes the “x”, e.g. to find records created in the last 3 days:

<condition attribute="createdon" operator="last-x-days" value="3" />

For user lookup fields, such as ownerid, there are various operators to check if the user matches the current user (eq-user), or any of the same teams the user is part of (eq-userteams, eq-useroruserteams). It can also look through the user hierarchy to match users below the current user in the user hierarchy (eq-useroruserhierarchy,
eq-useroruserhierarchyandteams). You can also filter by the same business unit as the current user (eq-businessid)

While we’re talking about hierarchies, remember that any other entity type can also have a hierarchy defined, and you can use the above, under and similar operators to match any record in them rather than having to navigate them manually yourself.

There’s some more good information about querying date and hierarchy data in the Microsoft documentation.

Condition Values

All conditions compare a field to a specific value. Importantly, there is no condition to compare one field to another field.

Most conditions require a single value to be specified in the value attribute, such as the equals operator:

<condition attribute="statecode" operator="eq" value="0" />

For some the value is implicit in the operator, so no value is required:

<condition attribute="primarycontactid" operator="null" />

A few can work with more than one value:

<condition attribute="statuscode" operator="in">
  <value>1</value>
  <value>4</value>
  <value>10</value>
</condition>

Conditions on lookup fields require the primary key guid value to filter on. If you build a query in Advanced Find and download the FetchXML you’ll also see the uiname and uitype attributes included in the condition to identify the name and type of the record respectively, but these are not used when the query is actually executed.

Dynamic Values

We saw some of the more advanced operators earlier, especially the date and user operators. These are particularly important when building views for your users to use on a regular basis.

For example, a view that shows active leads owned by the current user created in the last 7 days is probably more useful than one that shows leads created since a fixed date and owned by you, the author of the view.

Filtering Linked Entities

There are two ways filters can be applied to linked entities – within the <link-entity> element, or within the main <entity> element using the entityname attribute on the conditions. Both are valid, but do subtly different things.

When the filter is applied within the <link-entity>, the filter is applied to the entity before it is joined. By adding an entityname attribute to a condition within the main filter, the filter is applied after the join.

When using inner joins there is no practical difference in the results, but with outer joins these approaches can be combined to generate a query to find records that do not have any related records that match some criteria, e.g.:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="accountid" from="parentcustomerid" link-type="outer">
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="firstname" operator="eq" value="Mark" />
      </filter>
    </link-entity>
    <filter>
      <condition attribute="contactid" entityname="contact" operator="null" />
    </filter>
  </entity>
</fetch>

This will retrieve all the accounts that do not have an active contact called Mark.

The list of contacts is filtered to only active Marks, and the list of accounts is linked to that filtered list using an outer join. As we looked at in the previous part of this series, the outer join type will include all the records from the first list (the accounts). If there are any matches from the second list (the contacts) then those are included, otherwise, all the contact fields in the result are left blank (null). The outer filter then restricts the final result to only those joined records that have a null contactid, and therefore didn’t find a matching contact.

Combining filters and joins to find records without related records

Note that, if you give the link entity an alias, it is that alias you should use in the entityname attribute, not the actual logical name of the entity.

SQL Equivalents

The main filter is equivalent to the SQL WHERE clause. For example, the first query in this post would be equivalent to:

SELECT fullname
FROM   contact
WHERE  statecode = 0
       AND firstname = 'Mark'

The from and to attributes on a <link-entity> form an ON clause for a join, and a filter within the link entity extend that ON clause. The filter created by the from and to attributes will always be combined to the additional filter with an AND. For example, the query from earlier:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <link-entity name="contact" to="accountid" from="parentcustomerid" alias="contact" link-type="outer">
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="firstname" operator="eq" value="Mark" />
      </filter>
    </link-entity>
    <filter>
      <condition attribute="contactid" entityname="contact" operator="null" />
    </filter>
  </entity>
</fetch>

can be written in SQL as:

SELECT account.name
FROM   account
       LEFT OUTER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
          AND contact.statecode = 0
          AND contact.firstname = 'Mark'
WHERE  contact.contactid IS NULL;

The more advanced operators do not have any direct equivalent in SQL. Many of the date conditions compare values only on the date part and ignore the time part to make it easier for the user to build useful queries, but would require a range comparison in SQL. The hierarchical operators would likely require more complex CTEs, and are a great example of how FetchXML delivers a much more powerful application-level query language rather than just a thin veneer over SQL.

In SQL 4 CDS these operators can be used through the pattern of attribute = operator(value), e.g.

SELECT fullname
FROM   contact
WHERE  ownerid = equseroruserteams()
       OR createdon = lastxweeks(4);
<fetch xmlns:generator="MarkMpn.SQL4CDS">
  <entity name="contact">
    <attribute name="fullname" />
    <filter type="or">
      <condition attribute="ownerid" operator="eq-useroruserteams" />
      <condition attribute="createdon" operator="last-x-weeks" value="4" />
    </filter>
  </entity>
</fetch>

Leave a comment

Your e-mail address will not be published. Required fields are marked *