Column Comparison is one of the best features that has been recently added to the product.
Column comparison allows us to compare values of 2 different columns in our query be it Fetch XML, Web API, or through SDK API.
Sharing some of the basic examples that we tried using our favorite FetchXML Builder
- If we run the following query “Find all contact which has the first name equal to the last name”
The result –
- Find all contact where the first name is not equal to last name
The result –
The operators supported are –
- Only 2 columns can be compared.
Only compatible attribute types can be compared.
E.g. “Find all contact where the first name is equal to address 1 city”
It works as both are string attributes.
- If we try comparing text field with options set, we will get below error message
- Two option set can be compared as they represent the same type integer
Both having value 1
- Comparing date fields
- We are getting the result
- For the lookups, comparing created by and owner id
works as expected.
- For the below record we have the First name and job title as null
And here for below record, we have the same value for First Name and Job title.
On running the below query – find all contact where the first name is equal to the job title.
We get only one result. It ignored the blank (null) record.
Get all the details here
Hope it helps..