Retrieve Records by comparing two Columns in Web API Query

Hi learners,

Till now, $filter can be applied for a field against a constant value. With this new feature $filter can be applied for a field against another field in scenarios like below

  • Get all contacts where First Name = Last Name
  • Get all Opportunities where Estimated Revenue >= Actual Revenue
  • Get all Cases Resolved post due date.
    etc..

Supported Filters:

  1. Equal
  2. NotEqual
  3. GreaterThan
  4. GreaterEqual
  5. LessThan
  6. LessEqual

Example: To retrieve all contacts whose First Name = Last Name

Web API Query:

<Instance Web API>/contacts?$filter=firstname eq lastname
[ Note: This Web API expression works only for version 9.1.0000.19562 or later ]

fetch XML Query:


References:

1) https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison
2) https://powerapps.microsoft.com/en-us/blog/announcing-column-comparison-through-fetchxml-sdk-and-odata/

Author: Sudheer Reddy Nelakurthi

Power Platform developer for 8 years

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 )

Google photo

You are commenting using your Google 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