Dynamics 365 CRM and the platform underneath has always provided for a proprietary query language called FetchXML. With the introduction of CRM Online, this became the defacto query language as SQL commands could no longer be used (well querying data through sql is being reintroduced, under preview right now, but that is another topic for discussion)
The FetchXML syntax is not as extensive and flexible as SQL syntax and had its limitations. One of them being when you provide filter conditions, the value on the right hand side of the condition always had to be a constant static value
i.e address1_city = ‘Mumbai’
<condition attribute=’address1_city’ operator=’eq’ value=’Mumbai’ />
If we wanted to list out contacts that do not have the same city in the bill-to and ship-to addresses, we were looking for a query in the form of
Select * from contact
where contact.address1_city <> contact.address2_city
This had not been possible until now as it was comparison between values in fields rather than constant or static values.
With the new enhancements to FetchXML, it is now possible to frame this query
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” >
<entity name=”contact” >
<attribute name=”fullname” />
<attribute name=”address1_city” />
<attribute name=”contactid” />
<filter type=”and” >
<condition attribute=” address1_city” operator=”eq” valueof=”address2_city” />
</filter>
</entity>
</fetch>
If you notice to use comparison between fields, we are using valueof instead of value that we have traditionally used when comparing with static values
The field comparison supports the following operators
- Equal (eq)
- NotEqual (neq)
- GreaterThan (gt)
- GreaterEqual (ge)
- LessThan (lt)
- LessEqual (le)
<condition attribute=”FirstAttributeLogicalName” operator=”OperatorKeyword” valueof=”SecondAttributeLogicalName” />
OperatorKeyword will have one among values from the list – eq,neq,gt,ge,lt,le
Here is a table of the field types supported by each operator
Apart from FetchXML, if you would like to use the query expression, you could use the following code for field comparison.
var query = new QueryExpression(“nc_devices”);
query.ColumnSet.AddColumns(“nc_faultycount”, “nc_propercount”);
query.Criteria.Conditions.Add(new ConditionExpression(“nc_faultycount”, ConditionOperator.GreaterThan, true, “nc_propercount”));
EntityCollection results = new EntityCollection();
results=service.RetrieveMultiple(query);
Note: This field comparison feature in Query Expression can be used only for sdk assemblies with version greater than or equal to 9.0.2.25. But field comparison feature in FetchXML is available for all v9 version of sdk assemblies and also v8 versions too.
Currently these platform enhancements are available to developers for use through SDK calls. The query designer options available through the UI like the Advanced Find Query builder is yet to support this feature.
This first iteration of this feature does come with certain limitations. Please check the below link regarding the limitations.