Finally we have Column Comparison in FetchXml, SDK and OData!

Microsoft has made an announcement and finally we have Column Comparison available using FetchXml, SDK and OData.

Now we will be able to perform column comparisons for the Common Data Service. Column comparisons will also work in the Power Apps expression language with CDS version 9.1.0000.19562 or later.

We can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:

• Equal
• NotEqual
• GreaterThan
• GreaterEqual
• LessThan
• LessEqual

Columns comparison using FetchXML:

<fetch>

  <entity name='contact' >

    <attribute name='firstname' />

    <filter>

      <condition attribute='firstname' operator='eq' valueof='lastname'/>

    </filter>

  </entity>

</fetch>

Columns comparison using Web Api:

https://<environment-root>/contacts?$select=firstname&$filter=firstname eq lastname

Columns comparison using SDK Api & Organization Service:

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object value
)

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object[] values
)

Following link provide more details: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison

Premjit Singh
Attendite Ltd.
Twitter

How to Retrieve record(s) from Dynamics CRM using FetchXml and Web Api

Following JavaScript shows how to retrieve record(s) from Dynamics CRM using FetchXml and Web Api.

function RetrieveUsingFetch() {

var date = new Date();

var today = date.DateFormat("yyyy-MM-dd"); \\ Click here to see function details.

var marketCondition = "";

var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +

"<entity name='contractdetail'>" +

"<attribute name='contractid' />" +

"<attribute name='title' />" +

"<attribute name='contractdetailid' />" +

"<filter type='and'>" +

"<condition attribute='statuscode' operator='in'>" +

"<value>2</value>" +

"<value>1</value>" +

"</condition>" +

"<condition attribute='activeon' operator='on-or-before' value='" + today + "' />" +

"<condition attribute='expireson' operator='on-or-after' value='" + today + "' />" +

"<condition attribute='customerid' operator='eq' value='" + Xrm.Page.getAttribute("dbc_customerid").getValue()[0].id + "' />" +

"</filter>" +

"<link-entity name='contract' from='contractid' to='contractid' alias='aa'>" +

" <attribute name='title' />" +

"<filter type='and'>" +

"<condition attribute='statuscode' operator='eq' value='3' />" +

"</filter>" +

"</link-entity>" +

"</entity>" +

"</fetch>";

var uri = "/contractdetails?fetchXml=" + encodeURIComponent(fetchXml);

var clientUrl = Xrm.Page.context.getClientUrl();

var webApiPath = "/api/data/v8.1";

uri = clientUrl + webApiPath + uri;

var request = new XMLHttpRequest();

request.open("GET", encodeURI(uri), false);

request.setRequestHeader("Accept", "application/json");

request.setRequestHeader("Content-Type", "application/json; charset=utf-8");

request.setRequestHeader("OData-MaxVersion", "4.0");

request.setRequestHeader("OData-Version", "4.0");

request.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");

request.setRequestHeader("Prefer", "odata.maxpagesize=10");

request.onreadystatechange = function () {

if (request.readyState === 4 /* complete */) {

if (request.status === 200) {

request.onreadystatechange = null; 

var data = JSON.parse(request.response);

if (data.value.length === 1 || data.value.length > 1) {

alert(data.value[0].contractdetailid);

}

} else {

var error = JSON.parse(request.response).error;

alert(error.message);

}

}

};

request.send();

}

Note: Make sure that you have right version of WebApi in your code.

Happy Coding

P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)

Please check my other (non-CRM) blog here: Programming Blogs