Personalized Community is here!
Quickly customize your community to find the content you seek.
Check out the latest Sales updates!Learn about the key capabilities and features of Dynamics 365 Sales and experience some of the new features.
Download overview guide | Watch Sales video
2020 Release Wave 2Discover the latest updates and new features to Dynamics 365 planned through March 2021.
Release overview guides and videos Release Plan | Preview 2020 Release Wave 2 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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” />
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
<condition attribute=”FirstAttributeLogicalName” operator=”OperatorKeyword” valueof=”SecondAttributeLogicalName” />
OperatorKeyword will have one among values from the list – eq,neq,gt,ge,lt,le
Note: Operator applicable depend on the data type of the field as has always been the case. No changes there. GreaterThan is not supported for string
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.Criteria.Conditions.Add(new ConditionExpression(“nc_faultycount”, ConditionOperator.GreaterThan, true, “nc_propercount”));
EntityCollection results = new EntityCollection();
Note: This field comparison feature in Query Expression can be used only for sdk assemblies with version greater than or equal to 184.108.40.206. 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.
Business Applications communities