Use column comparison in queries

In the Microsoft Dataverse, users can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:

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

This will allow the comparison of a column against a specific value and return all found records, or allow the comparison of two columns to return all records with a matching value.

Limitations

Listed below are the limitations for the current Dataverse column comparison support.

• You can only compare columns within a single table.
• Only two columns may be compared at a time.
• Multi-value condition operators are not supported (i.e., "in").
• Extended condition operators are not supported (i.e., "creditlimit > spends+1000").
• Incompatible column comparison is not supported. For example, "int vs. int" columns is a valid comparison but "int vs. string" columns is not a valid comparison.

Column comparison using FetchXML

The following example shows how to compare columns using FetchXML:

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

For FetchXML requests, a new node valueof has been added inside the condition node. The valueof tag is used to identify the column that is being compared to the selected column. In the above example, the 'firstname' column is being compared against the 'lastname' column and will return any records that contain the same value across both columns.

See Also

Use FetchXML to construct a query
Column comparison using the Web API
Column comparison using the SDK API