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 –
Equal | LessEqual | GreaterEqual |
Not Equal | LessThan | GreaterThan |
- 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”
The result
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
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison
Hope it helps..
Reblogged this on All about Dynamics 365, PowerApps, Power Platform and Power Apps portals by Debajit Dutta and commented:
Compare two columns in Fetchxml in Dyanmics 365. A new feature introduced in Dynamics 365 and wonderfully articulated by Nishant Rana
LikeLike
Awesome post Nishant. Quick one what about Option set to option set comparison? Will that work? Does the optionset number has to match or can just the labels match?
I’m assuming it’ll only work if the two optionsets have the same integer value against the options.
LikeLiked by 1 person
Yes it worked, as it basically contains integer internally . It will simply compare the integer value.
LikeLike
Hi Nishant,
Great post. Is it possible to compare values from the selected entity with link entities?
LikeLike
Doesn’t seem so Niels.
LikeLike
@Niels,
“You can only compare columns within a single entity” but there is a workaround by using calculated field if you want to compare values from the selected entity with link entities.
https://www.itaintboring.com/dynamics-crm/we-can-now-do-column-comparison-with-fetchweb-api
LikeLike
Reblogged this on Dynamics 365 and commented:
Much needed feature.. Thank you Nishant Sir for the good examples.
LikeLike
Reblogged this on Arun Potti's MS CRM blog.
LikeLike
Reblogged this on Amar Singh.
LikeLike
Reblogged this on Phani Rajasekhar.
LikeLike
Reblogged this on Prashant Kumar Maurya and commented:
A nice addition to fetch XML query
LikeLike