{"id":24754,"date":"2020-09-16T11:58:42","date_gmt":"2020-09-16T11:58:42","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=24754"},"modified":"2020-09-17T07:59:06","modified_gmt":"2020-09-17T07:59:06","slug":"field-comparisons-now-available-for-queries-in-power-platform","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2020\/09\/field-comparisons-now-available-for-queries-in-power-platform\/","title":{"rendered":"Field Comparisons now available for Queries in Power Platform"},"content":{"rendered":"

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)<\/p>\n

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<\/p>\n

i.e address1_city = \u2018Mumbai\u2019<\/p>\n

<condition attribute=’address1_city’ operator=’eq’ value=’Mumbai’ \/><\/p>\n

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<\/p>\n

Select * from contact<\/p>\n

where contact.address1_city <> contact.address2_city<\/p>\n

This had not been possible until now as it was comparison between values in fields rather than constant or static values.<\/p>\n

With the new enhancements to FetchXML, it is now possible to frame this query<\/p>\n

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” ><\/p>\n

<entity name=”contact” ><\/p>\n

<attribute name=”fullname” \/><\/p>\n

<attribute name=”address1_city” \/><\/p>\n

<attribute name=”contactid” \/><\/p>\n

<filter type=”and” ><\/p>\n

<condition attribute=”<\/strong> <\/em>address1_city” operator=”eq” valueof=”address2_city” \/><\/em><\/strong><\/p>\n

<\/filter><\/p>\n

<\/entity><\/p>\n

<\/fetch><\/p>\n

If you notice to use comparison between fields, we are using valueof<\/strong> instead of value<\/strong> that we have traditionally used when comparing with static values<\/p>\n

The field comparison supports the following operators<\/p>\n