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.

Update 2020-08-25 – I’ve added more information about the dynamic date/time filters to describe what these do in more detail.

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>

32 thoughts on “Inside FetchXML pt 3 – filter”

  1. Hi Mark,

    This is a great article. Thanks.
    I have similar requirement on the linked entity. I would like to list down all contacts from application which are not associated with given static marketing list. Here I am confused how I can use not in query to get correct result. Can you please advise?

    1. You can use the same approach for this example as I used in the Filtering Linked Entities section. There I retrieved accounts that didn’t have an active contact named Mark, but your example wants contacts that aren’t in a particular static marketing list. The contact <-> marketing list link is stored in the listmember entity, so you can rewrite the example query as:

      <fetch>
        <entity name="contact">
          <attribute name="fullname" />
          <link-entity name="listmember" to="contactid" from="entityid" link-type="outer">
            <filter>
              <condition attribute="listid" operator="eq" value="list-guid" />
            </filter>
          </link-entity>
          <filter>
            <condition attribute="listid" entityname="listmember" operator="null" />
          </filter>
        </entity>
      </fetch>
      
      1. Hi Mark!
        I’m trying to get all agreements from a selected user.
        I have two tables msdyn_agreement and systemuser and both tables are linked by systemuser.systemuserid and msdyn_agreement.ownerid

        Cloud flow: My event start “When a record is selected” (that means when a user is selected) then I call the action “List Rows” with the following Fetch query:

        I try with many queries but I can not find the solution.

        My query just works when I provide the user code like this:

          1. <fetch count =”5″>
            <entity name=”msdyn_agreement”>
            <attribute name=”msdyn_name”/>
            <attribute name=”ownerid”/>
            <link-entity name=”systemuser” to=”ownerid” from=”systemuserid”
            link-type=”outer”>
            <filter>
            <condition attribute=”systemuserid” operator=”eq” value=”{ownerid}”/>
            </filter>
            </link-entity>
            </entity>
            </fetch>

          2. Can you help me with my questions?
            I’m trying to get all agreements from a selected user.
            I have two tables msdyn_agreement and systemuser and both tables are linked by systemuser.systemuserid and msdyn_agreement.ownerid

            Cloud flow: My event start “When a record is selected” (that means when a user is selected) then I call the action “List Rows” with the following Fetch query:

            <fetch count =”5″>
            <entity name=”msdyn_agreement”>
            <attribute name=”msdyn_name”/>
            <attribute name=”ownerid”/>
            <link-entity name=”systemuser” to=”ownerid” from=”systemuserid”
            link-type=”outer”>
            <filter>
            <condition attribute=”systemuserid” operator=”eq” value=”{ownerid}”/>
            </filter>
            </link-entity>
            </entity>
            </fetch>

            I try with many queries but I can not find the solution.

  2. Hi Mark,

    I am confused on this paragraph “Filtering Linked Entities”.
    You write: “can be combined to generate a query to find records that do not have any related records that match some criteria”.
    However the output of “filtered join results” shows the element(s) that are Active and having name Mark.

    Based on your specification I would have expected records with account Id’s 1 and 3 to be returned.

    What did I miss out?

  3. your filtering by first name with Mark then how you got 1 and 3, we should get no records found as there is no data match with the query?

    1. Accounts 1 and 3 match because they do not have an associated active contact called Mark. Account 2 does have an active contact called Mark, so that account is excluded from the final results due to the filter:

      <condition attribute="contactid" entityname="contact" operator="null" />

      Because account 2 does have a related contact that matched the previous filter, that record has a non-null contactid and therefore is excluded by this rule.

  4. Hi Mark, this is so helpful thank you. I’m super new at this stuff but do you know how I could build the following parameters? I want to exclude all of our system system users (like these below that contain #), and run the query for wide open days or at least a month’s worth of logins. Is that possible? Thanks

    # Flow-CDSNativeConnectorEurope
    # Flow-RP
    # RelevanceSearch
    # RelevanceSearch
    # CDSFileStorage
    # CDSFileStorage

  5. Hi,
    I am trying to fetch the list by specific user with their company code. I tried with fetch XML filter with below code.

    but it is not working..Please help me out

  6. Hi Mark, just came across your blog while investigating using FetchXML in an Azure Data Factory pipeline. Since the Data Export Service has been retired in Dynamics, I’m trying to build my own using Azure Data Factory, and I’m running into a few challenges. Right now, I’m passing a list of Dynamics table names and primary keys to a foreach loop which then performs an upsert for each table to an Azure SQL Database. It works good, but our previous export to SQL using the DES would insert a column into the Email table indicating the entity type referenced in the regardingobjectid column. I’m wondering if it’s possible to do this with fetchxml? I would need to take the table name as a variable in the fetchxml, check if the table name is “Email”, and if so, check the regardingobjectid column values, determine what type of entity they are, and enter that into a new column named regardingobjectid_entitytype. Is this possible, or should I be exploring using the dynamic pipeline expression builder option?
    Thanks in advance, and great blog!

    1. I’m not sure I understand your question. You can’t use FetchXML to update records. If you retrieve the regardingobjectid column then the type information is returned, but I’m not sure how to access that in ADF.

  7. Hello Mark,
    Is there a way to pass in a variable/parameter that contains a string containing a list of strings that are comma delimited to the filter in the xml file?

    @trim(item().api12list)

    This is an microsoft ADF pipeline where the lookup activity queries a table that constains the one row with the value containing the list of strings comma delimited. This the row from the lookup activity is to be used as the input parameter to the filter in the copy activity that contains the fetch-xml query.

    1. An “in” condition can take a list of possible values, presumably if you can format your list into that format and inject it into some dynamic FetchXML then it should work.

  8. Hi Mark! Do you know if there is a way to allow users to input more than one value into a text filter and it return multiple results. Trying to do this dynamically

    1. You probably want to use an “in” filter, something like:

      <fetch>
      <entity name=”account”>
      <filter>
      <condition attribute=”name” operator=”in”>
      <value>X</value>
      <value>Y</value>
      </condition>
      </filter>
      </entity>
      </fetch>

      1. Thank you Mark! I tried this but unfortunately it is giving me two separate filters for “X” and “Y”, sidenote, I am attempting this in Power Pages and it just may not be available.

  9. Hi,

    how to prevent automatic random on in dataverse fetchxml. I want to show the out put on the order of values given in “IN” condition

    5e7dfcb7-6317-ef11-9f89-6045bdef00f4
    87fa3022-0423-ef11-840a-000d3a17af33
    89fa3022-0423-ef11-840a-000d3a17af33
    83fa3022-0423-ef11-840a-000d3a17af33
    a3fa3022-0423-ef11-840a-000d3a17af33
    a5fa3022-0423-ef11-840a-000d3a17af33
    99fa3022-0423-ef11-840a-000d3a17af33

    The output which i expect should be in order
    5e7dfcb7-6317-ef11-9f89-6045bdef00f4
    87fa3022-0423-ef11-840a-000d3a17af33
    .
    .
    .
    99fa3022-0423-ef11-840a-000d3a17af33

    1. There isn’t an option to order the results in this way. The only ordering option is to sort the results by a specific field, and sorting on a guid field would not give you this order.

  10. Is it possible to swap the operands in a like or ‘begins-with’

    Say we have a table of Zip Code Prefixes (5 characters) and our input is a Zip+4

    I’m trying to replicate this SQL in Fetch syntax

    Where ‘60601-2034’ like table.zipCodePrefix + ‘%’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.