In this blog post, we are going to go through the newly added and long overdue feature that is comparing two attributes or columns in Dynamics 365 conditions. The updated column comparison feature allows us to compare values of 2 different columns in our query whether its Fetch XML, Web API, or SDK API. This post would be useful to D365 developers or consultants that deal with customization and extension of Dynamics 365 on a daily basis.
The following operators are supported when it comes to using two columns vs column-value:
This example illustrates how to use this functionality with the Web API.
A couple of thins to remember are that you can compare only fields within the same entity, the fields have to be of the same data type and not all types are supported.
For example, we can filter contacts that have the same first and last name (by comparing SLOT fields):
[Organization-URL]/api/data/v9.1/contacts?$filter=firstname eq lastname&$select=fullname
or we can query contacts that haven’t been modified since creation (by comparing Date Time fields):
[Organization-URL]/api/data/v9.1/contacts?$filter=createdon eq modifiedon&$select=fullname
If we try to compare two different types, for example Decimal and Date Time, we will get the following error:
“A binary operator with incompatible types was detected. Found operand types ‘Edm.Decimal’ and ‘Edm.DateTimeOffset’ for operator kind ‘Equal’.”
Attribute types that this type of condition doesn’t work for with Web API at the moment, are:
- Two Options
- Option Set
- Whole Number
- Floating Point Number
If we try to compare unsupported attribute types, we will get the following error:
“The right side of the \”Equal\” operator must be a constant value.”
This is an example of how to retrieve a contact that hasn’t been modified or assigned to a different user.
In the condition, we are comparing attributes of types lookup and date and time, but it also works with other field types like single/multiple lines of text, currency, decimal, two options etc.
Before you start developing, make sure you have installed the latest version of Microsoft.CrmSdk.CoreAssemblies in your project.
You can start by writing a Query Expression. You can see bellow that we are using the Contact entity again.
This query will return the contacts that have the same last and first name. You can notice that a new parameter has been added to the ConditionExpression constructor to compare columns.
With the SDK API, unlike some previous examples, you can compare fields of Option Set type. If any of the columns have null values, they will be ignored, and those records won’t be retrieved in the result set.
A few things that are unfortunately not possible with the “Comparing two columns” feature are: multi-value condition operators (i.e., “in”), extended condition operators (i.e., “totalamount > amount+1000”), fields from different entities, more than two fields, etc. However, this is a great addition to the Dynamics 365 product and will definitely come in handy with future projects.