Use column comparison in D365 CE/Power Apps/CDS queries

Microsoft has finally given option to compare two columns while querying data from D365 CE/Power Apps/CDS. Here I am sharing my experiences and examples of how it can be used from various channels

1. Power Apps UI
2. Web API
3. Fetch XML
4. SDK API (.net)

Power Apps UI: This feature is not yet available in PowerApps UI (Advance Find), hope It will be available soon

Web API: This is a sample example of how you can compare columns of various data types in web API

1. String field to String field: here is the sample where I compared “firstname” column with “lastname” and “mobilephone” with “telephone1″ and it worked like a charm <a rel=”noreferrer noopener” href=”https://<https://<<YourOrganization>>/api/data/v9.1/contacts?$filter=firstname eq lastname and mobilephone eq telephone1&$select=firstname,lastname
2. Other data types: I tried other data type field comparisons as mentioned below
   • DateTime – ex. createdon – modifiedon
   • Whole Number (Integer)
   • Option Set (Picklist)
   • Two Options (Boolean)
   • Floating Point Number (Double)

But Web API is giving error message “The right side of the \”Equal\” operator must be a constant value”, seems like Microsoft has not yet enabled this feature fully in Web API yet.

Fetch XML: This is a sample example of how you can compare columns of various data types in Fetch XML. I tried to create one query which covers following comparison scenario and it worked smoothly

1. Text to text comparison
2. 1 column ‘telephone1’ being compared with 2 columns ‘mobilephone’, and ‘fax’.
3. Float to Float comparison
4. DateTime to DateTime comparison
5. Optionset to optionset comparison
6. Optionset to whole number comparison
7. Lookup to Lookup comparison
8. Field comparison in a link entity

Query:

Response:

CRM Contact Entity which was returned in above response:

For more details on fetch XML you can refer Nishant’s Blog.

SDK API: Microsoft has shared a new API for this, you can use this to compare two column in query

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object value
)

Example Query Expression:

var query = new QueryExpression("contact");

query.ColumnSet.AddColumns("firstname", "lastname");

query.Criteria.Conditions.Add(new ConditionExpression("firstname", ConditionOperator.Equal, true, "lastname"));

var results = client.RetrieveMultiple(query);

For details explanation on how to compare columns in query expression, I suggest you refer Debajit’s Blog.

Here is some observation I had while testing this feature and limitation of this feature:

1. You can only compare columns within a single entity. IE you cannot compare column from contact entity to column in its parent account entity.
2. As per Microsoft documentation only 2 columns can be compared at a time but it did not says how many condition can be added. I tried with 12 column comparisons (9 conditions) and it worked so I guess there is no limitation on number of conditions.
3. Multi-value condition operators are not supported (i.e., “in”).
4. Extended condition operators are not supported (i.e., “creditlimit > spends+1000”).
5. Only column of compatible data type can be compared, like
   • Whole Number to Whole Number
   • Text to Text
   • Whole Number to option set
6. If you try to compare two incompatible data type columns you will get below error (below is the error while comparing Text to Whole Number).
   • SDK Error Message: “Attributes of types “System.String”, ” System.Int32″ are not supported for comparison”
   • Web API Error Message: “A binary operator with incompatible types was detected. Found operand types ‘Edm.String’ and ‘Edm.Int32’ for operator kind ‘Equal’.”
7. You can user column comparison in nested queries.
8. In Web API field comparison is only working for string data type only.
9. While comparing option set, option set numeric values are being compared not the Label (which is how it should be).