One of the lesser-known Common Data Service features released in recent months is the ability to compare the value of two columns in a query.
Traditionally, when building queries, we have only been able to query a column with a set value, such as ‘less than 10’, ‘after 1/1/2020’; however, since July, it is now possible to build more advanced queries that compare the value of one column with the value of another.
Where can I perform these new types of comparisons?
Currently, this can be achieved in three ways:
- FetchXML
- Web API
- SDK API
It is not currently available within an Advanced Find or View Filter within a model-driven app, but hopefully this functionality will come in due course. For users of XRMToolBox, this functionality has already been added to the FetchXML builder tool.
Does this type of query have any limitations?
Yes. These are as follows:
- Only columns within the same entity can be compared
- Only two columns may be compared at once.
- Multi-value condition operators are not supported (i.e., “in”).
- Extended condition operators are not supported (i.e., “creditlimit > spend + 1000”).
- The two columns being compared must be of the same type, e.g. string vs string and not string vs integer
Summary
This additional enhancement further emphasises the capabilities of the Common Data Service, and Power Platform as a whole, and we look forward to seeing further improvements made to cater for more user scenarios.
