I was very pleased today to see a new feature in FetchXML – column comparisons!
This allows us to build queries that compares the values in one column against those in another. Previously we’ve only been able to compare a column against a constant value.
So how does this look? The
<condition> element has been extended to have a new
<fetch> <entity name="contact"> <attribute name="contactid" /> <filter> <condition attribute="firstname" operator="eq" valueof="lastname" /> </filter> </entity> </fetch>
This will return the IDs of any contacts that have the same value in the first and last name fields.
What can/can’t it do?
You can use this new option with the most common condition operators:
- not equal
- less than
- less than or equal
- greater than
- greater than or equal
The two columns you’re comparing must be of the same type.
You can’t compare columns across entities, so these queries won’t work:
<fetch> <entity name='account'> <link-entity name='contact' from='parentcustomerid' to='accountid'> </link-entity> <filter> <condition attribute='name' operator='eq' valueof='contact.firstname' /> </filter> </entity> </fetch> <fetch> <entity name='account'> <link-entity name='contact' from='parentcustomerid' to='accountid'> </link-entity> <filter> <condition entity='contact' attribute='firstname' operator='eq' valueof='lastname' /> </filter> </entity> </fetch>
If you do need to do this sort of thing, you can instead add a calculated field to one entity to take the value from the related entity, and then apply the filter to the calculated field instead. Alex Shlega has got some good details on this on his blog.
Why use it?
I find this particularly useful to identify some common data quality problems. As in the first example I showed, being able to find records with the same value repeated across multiple fields is a simple way to find a common way users “work around” required fields.
Previously we’ve had to do this by retrieving all the records and then comparing the values ourselves. To test the difference this approach can make I’ve built a simple test app:
- I’ve got a test instance with around 28,000 contacts, of which about 300 have the same first name as last name
- I connect to this instance using the SDK and run two equivalent processes:
- Retrieve all the contacts including the firstname and lastname fields, compare the names and count how many are the same
- Do the same again but filter the records with the new operator instead of in code
- As I’m just counting the records in this test, use an aggregate query to just return the count of records while filtering with the new operator
In each case I’ve made sure I load in all the pages of the results.
The performance comparisons are quite impressive:
|Filtering in code||8.9 sec|
|Filtering in FetchXML||0.3 sec|
|Aggregate with FetchXML filter||0.1 sec|
The performance difference you’ll see will depend on the ratio of the records you’re interested in to those you’d have to retrieve otherwise, but based on these performance numbers alone I’d advise you to take a look!
How to use it?
Currently this feature isn’t exposed in the Advanced Find interface, but I hope that’s coming soon. In the meantime you can use this in your plugins, Flows etc.
I’ve also updated SQL 4 CDS to use this feature where possible – from version 2.2.0 it will start taking advantage of this feature, there’s nothing you need to change in your SQL queries or settings.
A FetchXML Builder update is also in the works.
FetchXML has had a few updates over the years to add extra operators to handle hierarchical data and some extra date filtering options, but this is probably the first major change to FetchXML since the aggregate query options were introduced and extended from CRM 3 to 2013.
There are still limitations to how this works, but I hope this is the start of a new period of investment in this area. With this change, and the current preview of the TDS Endpoint, the future for CDS data access is looking very bright.
10 thoughts on “Column Comparisons in FetchXML”
when i try to compare columns then the error message pop up.
Attribute ‘value’ must be specified for condition operator: Equal
It probably means you’re not running on a version of D365 that supports the new functionality, such as an on-premise version.
If you try this in SQL 4 CDS it should detect that this option is not available and run the query in a different way, but if you’re using FetchXML Builder or some other way of executing the FetchXML directly then you’ll need to handle this yourself.
Mark, your tool is incredible, and I use it daily. Unfortunately, I’ve run into an issue with updates and was hoping to provide my findings to you in hopes of finding a solution. I’m simply attempting to update the owner on an account record. Your tool functionally does everything correctly, except update the value for me. Any thoughts, recommendations?
Hi Michael, are you able to post your query either here or as an issue on GitHub? Can you also show the results that it shows after running the query?
The Fetch XML can be stored as saved view (user view) and possible available to Subgird on a main form. Is it possible to pass a parameter to the saved view from the select record of the main form?
For example Contact form’s contact id to be pass as parameter to the view (linked to contact contact entity and appointment entity) for filtering by Required Attendees?
There’s no supported way to dynamically change the FetchXML of a grid control. The only option is to use the “Show related records” option to filter the grid to only records related to the current record on the main form.
Mark, can this be used to compare the Lookups fields that are present in the table? Looks like its not but would like to confirm once.
This seems to work for me:
Thanks Mark for confirming. Any idea if I can apply the valueof element to the System views in an entity? Please advise if you have any recommended solution for this.
Yes, you can use FetchXML Builder to edit your view and add in this sort of filter. https://fetchxmlbuilder.com/features/#qrymgmt-open