Execute fetchxml using Web API in Dynamics CRM 2016

By | December 23, 2015

Introduction

Microsoft Dynamics CRM Online 2016 Update and Microsoft Dynamics CRM 2016 (on-premises) introduced new concept called The Web API. It can be used across a wide variety of programming languages, platforms, and devices. The Web API implements the OData (Open Data Protocol), version 4.0, an OASIS standard for building and consuming Restful APIs.

Basically Web API is advanced version of old OData. With the help of Web API you can perform following operation.

  • Create
  • Update
  • Delete
  • Retrieve
  • Retrieve multiple( using odata query and fetch)
  • Execute Web API functions
  • Execute Web API Actions
  • Execute Web API Query Functions

Walkthrough

As the earlier version of Odata does not support to execute the fetchxml. To execute fetchxml you have to use SOAP. Now using Web API you can execute the fetchxml.

Below is example that used to execute the fetchxml.

First define your fetchxml and execute using Web API using following code.

If you want retrieve formatted value and data about lookup properties. You have add following line in request header.

xhr.setRequestHeader("Prefer", "odata.include-annotations=*");

Below is the entire code.

// This function is used to retrieve records using fetchxml
fetch: function (originalFetch, entitySetName, retrieveUsingFetchSuccess, retrieveUsingFetchError) {

var fetch = null;
try {
//create the fetch
fetch = [“”, originalFetch, “”].join(“”);
//encode the fetchxml
fetch = escape(fetch);
//create AJAX request
$.ajax({
type: “GET”,
contentType: “application/json; charset=utf-8”,
datatype: “json”,
//async: false,
url: this.getWebAPIPath() + entitySetName + “?fetchXml=” + fetch,
beforeSend: function (xhr) {
//Specifying this header ensures that the results will be returned as JSON.
xhr.setRequestHeader(“Accept”, “application/json”);
xhr.setRequestHeader(“Content-Type”, “application/json; odata.metadata=minimal”);
xhr.setRequestHeader(“OData-MaxVersion”, “4.0”);
xhr.setRequestHeader(“OData-Version”, “4.0”);
////xhr.setRequestHeader(“Prefer”, “odata.include-annotations=OData.Community.Display.V1.FormattedValue”);
xhr.setRequestHeader(“Prefer”, “odata.include-annotations=*”);
},
success: function (data, textStatus, xhr) {

if (data != null) {

var entityObj = [];
var pagingInfo = null;

//Check if results contains cookies
//if yes then retrieve next set of records
if (data[“@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”] != null) {

//get the pagingcookie
pagingInfo = Inogic.ApiLib.getPagingCookie(data[“@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”]);

//call this function create json object
Inogic.ApiLib.createJsonObject(data, entityObj);

//call this to retrieve more records
Inogic.ApiLib.fetchMore(originalFetch, entitySetName, retrieveUsingFetchSucess, retrieveUsingFetchError,pagingInfo.pageCokies,pagingInfo.pageNumber, entityObj);

}
}
//get the results and create json object
else {

var entityObj = [];

// This function is used to create the json object
Inogic.ApiLib.createJsonObject(data, entityObj);

//call successcallback
retrieveUsingFetchSuccess(entityObj);
}
},
error: function (xhr, textStatus, errorThrown) {
retrieveUsingFetchError(Inogic.ApiLib.errorHandler(xhr));
}
});
} catch (e) {
throw new Error(e);
}

};

Web API return 5000 records at time, to retrieve more than 5000 records you have to use paging cookie. To identify more records you can use @Microsoft.Dynamics.CRM.fetchxmlpagingcookie. If query has more records then @Microsoft.Dynamics.CRM.fetchxmlpagingcookie contains value. The format of it given below

C-E511-80D8-F0921C194348%257d%2522%2520first%253d%2522%257b46A60D62-BF9C-E511-80DD-6C3BE5A8DACC%257d%2522%2520%252f%253e%253c%252fcookie%253e” istracking=”False” />”
You have decode this using “unescape”.
pagingcookie = unescape(pagingcookie;)
When you decode then will looks like below.
pagingcookie = “” istracking=”False” />”

From above pagingcookie  you can extract the cookie part & pagenumber and add this to fetchxml  and pass this updated fetchxml to retrieve more records.

When you execute fetch it return data in json format.

To get the value of from result use following code.

  1. String/Number etc

var name = data.value[0].name;

  1. Lookup(Entity Reference)

id: data.value[0]._primarycontactid_value,

name: data.value[0][“_primarycontactid_value@OData.Community.Display.V1.FormattedValue”],

logicalname: data.value[0][“_primarycontactid_value@Microsoft.Dynamics.CRM.lookuplogicalname”],

  1. Option set/Money/DataTime/Boolean

value: data.value[0].revenue

formattedValue :data.value[0][“revenue@OData.Community.Display.V1.FormattedValue”]

Since there were requests to include the code in the API, I have added the code of the functions used here. This is our implementation and not necessarily the only or the best way to have it developed. We have been using XrmServiceToolkit for a while and the code here is a similar implementation for WEB API.
// This function is used to retrieve records using fetchxml
fetch: function (originalFetch, entitySetName, retrieveUsingFetchSuccess, retrieveUsingFetchError) {

var fetch = null;
try {
//create the fetch
fetch = [“”, originalFetch, “”].join(“”);
//encode the fetchxml
fetch = escape(fetch);
//create AJAX request
$.ajax({
type: “GET”,
contentType: “application/json; charset=utf-8”,
datatype: “json”,
//async: false,
url: this.getWebAPIPath() + entitySetName + “?fetchXml=” + fetch,
beforeSend: function (xhr) {
//Specifying this header ensures that the results will be returned as JSON.
xhr.setRequestHeader(“Accept”, “application/json”);
xhr.setRequestHeader(“Content-Type”, “application/json; odata.metadata=minimal”);
xhr.setRequestHeader(“OData-MaxVersion”, “4.0”);
xhr.setRequestHeader(“OData-Version”, “4.0”);
////xhr.setRequestHeader(“Prefer”, “odata.include-annotations=OData.Community.Display.V1.FormattedValue”);
xhr.setRequestHeader(“Prefer”, “odata.include-annotations=*”);
},
success: function (data, textStatus, xhr) {

if (data != null) {

var entityObj = [];
var pagingInfo = null;

//Check if results contains cookies
//if yes then retrieve next set of records
if (data[“@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”] != null) {

//get the pagingcookie
pagingInfo = Inogic.ApiLib.getPagingCookie(data[“@Microsoft.Dynamics.CRM.fetchxmlpagingcookie”]);

//call this function create json object
Inogic.ApiLib.createJsonObject(data, entityObj);

//call this to retrieve more records
Inogic.ApiLib.fetchMore(originalFetch, entitySetName, retrieveUsingFetchSucess, retrieveUsingFetchError,pagingInfo.pageCokies,pagingInfo.pageNumber, entityObj);

}
}
//get the results and create json object
else {

var entityObj = [];

// This function is used to create the json object
Inogic.ApiLib.createJsonObject(data, entityObj);

//call successcallback
retrieveUsingFetchSuccess(entityObj);
}
},
error: function (xhr, textStatus, errorThrown) {
retrieveUsingFetchError(Inogic.ApiLib.errorHandler(xhr));
}
});
} catch (e) {
throw new Error(e);
}

};

Conclusion

In this way, We can use Web API to execute fetchxml instead of SOAP.

Before you move on to the next post, check our Maplytics InfoCenter.

6 thoughts on “Execute fetchxml using Web API in Dynamics CRM 2016

  1. Dev

    Hello,

    I’m getting same page cookies every time in FetchMore() function, So it is going in recursive loop.

    Please help me to solve this issue.

    fetchMore: function (originalFetch, entitySetName, retrieveUsingFetchSucess, errorCallback, pageCookies, pageNumber, entityObj) {

    //pageCookies

    }

    1. inogic

      Recently, we also came across the same kind of issue, where when we tried to retrieve more than 5K records of Contact Entity, it returned the same paging cookie all the time. Due to this, it retrieved the same records again and again and the process went into a recursive loop that never ended. As we worked on this, we found that it occurred because we have created a field on the Contact Entity and the value in this field gets populated by the plug-in. We have registered the plugin on the Retrieve Multiple message which reads value from other Entity and sets into this field that is shown to the user, but we do not save the value of this field in database. This field is like a virtual field, which may be the reason that it gives the same paging cookie each time. If we remove this field from fetchxml and execute using Web API, it starts working.

      Thanks!

  2. md chen

    1) where the code for //call this function create json object
    Inogic.ApiLib.createJsonObject(data, entityObj);? –> createJsonObject ???

    2) where can get Inogic.ApiLib? is that free or buy one time fee?

    1. Inogic

      If you are on Dynamics 365 version 9.0 and above then use the Xrm.WebApi client object methods to perform CRUD operations.

      However, below is the code for createJsonObject and the Inogic.ApiLib is not for selling purposes.

      //This function is used to create the json object
      function createJsonObject(data, entityObj) {
      var ent = null;
      try {
      //validate response text
      if (data[“@odata.context”].split(“(“)[1] != undefined) {
      //get the attribute list
      var attList = data[“@odata.context”].split(“(“)[1].replace(“)”, “”).split(“,”);
      //loop through all records and create json object
      for (var rec = 0; data.value.length > rec; rec++) {
      //new object
      ent = {};
      for (var att = 0; attList.length > att; att++) {
      //check the navigation properties
      if (data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.associatednavigationproperty”] != undefined) {
      var nameValue = “”;
      if (data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.formattedvalue”] != undefined) {
      nameValue = data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.formattedvalue”];
      }
      else if (data.value[rec][attList[att] + “@Display.DisplayableValue”] != undefined) {
      nameValue = data.value[rec][attList[att] + “@Display.DisplayableValue”];
      }
      else if (data.value[rec][attList[att] + “@OData.Community.Display.V1.FormattedValue”] != undefined) {
      nameValue = data.value[rec][attList[att] + “@OData.Community.Display.V1.FormattedValue”];
      }
      //create entityobject for EntityRef
      ent[data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.associatednavigationproperty”]] = {
      id: data.value[rec][attList[att]],
      name: nameValue,
      type: “entityreference”
      }
      } else if (data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.formattedvalue”] != undefined || data.value[rec][attList[att] + “@OData.Community.Display.V1.FormattedValue”] != undefined) {
      //create object for optionset & money
      ent[attList[att]] = {
      value: data.value[rec][attList[att]],
      formattedvalue: data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.formattedvalue”] != undefined ? data.value[rec][attList[att] + “@Microsoft.Dynamics.CRM.formattedvalue”] : data.value[rec][attList[att] + “@OData.Community.Display.V1.FormattedValue”],
      type: “formattedvalue”
      }
      } else {
      ent[attList[att]] = data.value[rec][attList[att]];
      }
      }
      //push the object
      entityObj.push(ent);
      }
      }
      }
      catch (e) {
      throw new Error(e);
      }
      return entityObj;
      }

      Hope this helps.

      Thanks!

Comments are closed.