Execute fetchxml using Web API in Dynamics CRM 2016

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;

2. 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”],

3. 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.