Column Comparisons in FetchXML

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 valueof element:

<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:

• equal
• 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:

1. I’ve got a test instance with around 28,000 contacts, of which about 300 have the same first name as last name
2. I connect to this instance using the SDK and run two equivalent processes:
   1. Retrieve all the contacts including the firstname and lastname fields, compare the names and count how many are the same
   2. Do the same again but filter the records with the new operator instead of in code
   3. 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:

Method	Time
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.

Final Thoughts

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.