CDS Column Comparison through FetchXML, SDK, and OData

Microsoft have announced the availability of Column Comparison in CDS-Based Queries .. well it is a very great great news !

Column Comparison is available for FetchXML, SDK API, and Web API.

So how to use it:

Operators Currently available for comparison:

  • Equal
  • NotEqual
  • GreaterThan
  • GreaterEqual
  • LessThan
  • LessEqual

Supported Data Types:

  1. Text
  2. Lookups
  3. DateTime
  4. Optionsets
  5. Optionsets to Whole Number

Limitations:

  1. You can only compare columns within a single entity
  2. Only two columns may be compared at a time.
  3. Multi-value condition operators are not supported (i.e., “in”).
  4. Extended condition operators are not supported (i.e., “creditlimit > spends+1000”).
  5. Incompatible attribute comparison is not supported. For example, “int vs. int” attributes is a valid comparison but “int vs. string” attributes is not a valid comparison.
  6. Advanced find UI: The feature is not yet find in the Advanced find option in Model Driven Power-apps , Hopefully it will be available soon!

Fetch XML: which is very handy and used to List Records in CDS (Current Environment).

So we have the operator the same as before and valueof which is set to the other column value.

Example below: This fetches contacts where first name equals last name

<fetch>
  <entity name='contact' >
    <attribute name='firstname' />
    <filter>
      <condition attribute='firstname' operator='eq' valueof='lastname'/>
    </filter>
  </entity>
</fetch>

SDK API: In Query Expressions

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object value
)
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);

Web API:

<https://<<YourOrganization>>/api/data/v9.1/contacts?$filter=firstname eq lastname

Very nice feature, Hope it is Helpful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: