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
- created no earlier than the start of 2019, OR
- 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.
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>
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?
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
listmemberentity, 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>Thanks a lot Mark.
Thanks a lot. 🙂
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:
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/
<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>
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.
What problem are you having with this? Are you getting an error message, not getting the results you expect, or something else?
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?
Good catch, thanks! I’ve updated the image.
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?
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
contactidand therefore is excluded by this rule.Hi Mark,
Can we pass dynamic value from HTML control for example on HTML Dropdown Selected Option :
Sure, just be careful when building the FetchXML dynamically that you correctly XML-escape any values.
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
These are application users, which you can filter out based on the applicationid field:
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
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?
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!
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.
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.
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.
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
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>
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.
Yes, it will find records that match either X or Y. What is it you’re wanting to achieve?
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
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.
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 + ‘%’
No, in FetchXML the data in the table can only be used as the input, not the pattern.
Hey Mark,
Thank you for this. This was super useful and clear!
In my case, I have a certain relationship between accounts and contacts. Also, some of my accounts are parents of the other accounts. I want to get all the accounts that have the relationship to a certain contact AND their children accounts (although the children won’t have the relationship). I also want to replace parent accounts with the children, in other words, if an account has a child I want just the child to be included and not the parent.
Is this doable in fetchxml?
Thank you so much for your help!
If you only want direct children to be included I think you can get the required information with:
<fetch> <entity name="account"> <attribute name="name" /> <link-entity name="contact" from="parentcustomerid" to="accountid"> <filter> <condition attribute="firstname" operator="eq" value="Mark" /> </filter> </link-entity> <link-entity name="account" from="accountid" to="parentaccountid" link-type="outer"> <attribute name="name" /> </link-entity> </entity> </fetch>This query starts from the top-level account record and finds those that have a relationship to a specific contact (in this case one called “Mark”). It also includes a join to the child accounts and includes their names. The results will include the name of the parent account that has the relationship to the contact as well as the list of child accounts, and if it has no child accounts then it will only include the name of the parent.
It gets more complicated if you want to include details of any descendent accounts, not just direct children. For this you can use the “under” filter, but this requires a specific account guid to search for. To get this to work I think you’d need to break your query down into two parts – one to get the list of accounts that have a relationship to your desired contact, and then another to perform the “under” query now you know the guids of the parent accounts.
Thank you so much. This worked! Although I had to switch to and from in the second link-entity: from=”parentaccountid” to=”accountid”