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:

active, AND
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”

Pingback: Inside FetchXML pt 5 - aggregate - Mark Carrington
Ravi says:

28th February 2021 at 2:34 pm

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?

Reply
1. Mark Carrington says:
  
  28th February 2021 at 3:01 pm
  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>
```
  Reply
  1. Ravi says:
    
    1st March 2021 at 1:05 pm
    
    Thanks a lot Mark.
    
    Reply
  2. Ravi says:
    
    1st March 2021 at 1:08 pm
    
    Thanks a lot. 🙂
    
    Reply
  3. KM says:
    
    8th December 2023 at 5:12 am
    
    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:
    
    Reply
    1. Mark Carrington says:
      
      8th December 2023 at 8:56 am
      
      It looks like your code has been lost from the comment – can you please try encoding it before posting it using a tool like https://www.w3docs.com/tools/html-encoder/
      
      Reply
      1. KM says:
        
        12th December 2023 at 3:20 am
        
        <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. KM says:
        
        14th December 2023 at 3:45 am
        
        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.
      3. Mark Carrington says:
        
        14th December 2023 at 6:47 am
        
        What problem are you having with this? Are you getting an error message, not getting the results you expect, or something else?
Inge says:

29th March 2021 at 5:57 am

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?

Reply
1. Mark Carrington says:
  
  2nd April 2021 at 10:50 pm
  
  Good catch, thanks! I’ve updated the image.
  
  Reply
Sandeep says:

16th June 2021 at 10:58 am

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?

Reply
1. Mark Carrington says:
  
  16th June 2021 at 12:08 pm
  
  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.
  
  Reply
Mahesh says:

4th November 2022 at 10:05 pm

Hi Mark,

Can we pass dynamic value from HTML control for example on HTML Dropdown Selected Option :

Reply
1. Mark Carrington says:
  
  13th November 2022 at 9:41 pm
  
  Sure, just be careful when building the FetchXML dynamically that you correctly XML-escape any values.
  
  Reply
Kim Creese says:

22nd December 2022 at 3:01 pm

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

Reply
1. Mark Carrington says:
  
  23rd December 2022 at 10:18 am
  These are application users, which you can filter out based on the applicationid field:
```
<condition attribute="applicationid" operator="null" />
```
  Reply
Anitha says:

18th January 2023 at 6:44 am

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

Reply
1. Mark Carrington says:
  
  24th January 2023 at 8:36 am
  
  Unfortunately it looks like your XML was removed from the comment – can you please try encoding your XML first, e.g. with https://emn178.github.io/online-tools/html_encode.html and repost it?
  
  Reply
Peter says:

29th June 2023 at 3:59 pm

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!

Reply
1. Mark Carrington says:
  
  3rd August 2023 at 8:46 am
  
  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.
  
  Reply
Bill says:

10th July 2023 at 10:53 pm

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.

Reply
1. Mark Carrington says:
  
  3rd August 2023 at 8:48 am
  
  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.
  
  Reply
Erica says:

3rd December 2023 at 4:25 am

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

Reply
1. Mark Carrington says:
  
  3rd December 2023 at 3:15 pm
  
  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>
  
  Reply
  1. Erica says:
    
    3rd December 2023 at 6:37 pm
    
    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.
    
    Reply
    1. Mark Carrington says:
      
      4th December 2023 at 12:09 pm
      
      Yes, it will find records that match either X or Y. What is it you’re wanting to achieve?
      
      Reply
IN says:

25th June 2024 at 5:54 pm

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

Reply
1. Mark Carrington says:
  
  28th June 2024 at 10:08 pm
  
  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.
  
  Reply
Mike says:

15th July 2024 at 10:44 pm

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 + ‘%’

Reply
1. Mark Carrington says:
  
  17th July 2024 at 1:27 pm
  
  No, in FetchXML the data in the table can only be used as the input, not the pattern.
  
  Reply