Use column comparison in queries
In the Common Data Service, 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 an attribute against a specific value and return all found records, or allow the comparison of two attributes to return all records with a matching value.
Listed below are the limitations for the current Common Data Service column comparison support.
- You can only compare columns within a single entity
- 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 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.
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 attribute that is being compared
to the selected attribute. 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 attributes.