Use column comparison in queries
Unsure about entity vs. table? See Developers: Understand terminology in Microsoft Dataverse.
In the Microsoft Dataverse, users can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:
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.
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
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.