Comparison of column was challenging part while executing FetchXML in Dynamics CRM. Microsoft added one new node inside FetchXML condition tag to compare fields from same entity within same FetchXML.
The “valueof” is used to identify the column that is being compared to the selected column. With the help of valueof we can compare two attributes(fields) with same data type and then it will return any records that contain the same value across both fields.
Let’s see how we can compare two fields using valueof tag in FetchXML.
1. valuof node using lt
<fetch>
<entity name=’quote’ >
<attribute name=’quotenumber’ />
<filter>
<condition attribute=’discountamount’ operator=’lt’ valueof=’freightamount’/>
</filter>
</entity>
</fetch>
If we execute this fetchxml in CRM Restbuilder, XrmToolbox or any other tool then it will give result of all quote records where discount amount is greater than freightamount.
2. valuof node using eq:
For below product I have added valid from and valid to as same date.
We can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:
Equal(eq), NotEqual(neq), GreaterThan(gt), GreaterEqual(ge), LessThan(lt), LessEqual(le)
Syntax to compare two attributes using valuof node:
<condition attribute=”firstattributelogicalname” operator=”operatorkeyword” valueof=”secondattributelogicalname” />
Below are some of the limitations for the valueof column comparison tag:
- 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., “discountamount < totalamount + 100”).
- 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.
Reference:
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/column-comparison#limitations
Everything Dynamics 