{"id":2157,"date":"2015-12-23T18:12:33","date_gmt":"2015-12-23T12:42:33","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=2157"},"modified":"2022-07-01T12:36:46","modified_gmt":"2022-07-01T07:06:46","slug":"execute-fetchxml-using-web-api-in-dynamics-crm-2016","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2015\/12\/execute-fetchxml-using-web-api-in-dynamics-crm-2016\/","title":{"rendered":"Execute fetchxml using Web API in Dynamics CRM 2016"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p>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.<\/p>\n<p>Basically Web API is advanced version of old OData. With the help of Web API you can perform following operation.<\/p>\n<ul>\n<li>Create<\/li>\n<li>Update<\/li>\n<li>Delete<\/li>\n<li>Retrieve<\/li>\n<li>Retrieve multiple( using odata query and fetch)<\/li>\n<li>Execute Web API functions<\/li>\n<li>Execute Web API Actions<\/li>\n<li>Execute Web API Query Functions<\/li>\n<\/ul>\n<p><strong>Walkthrough<\/strong><\/p>\n<p>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.<\/p>\n<p>Below is example that used to execute the fetchxml.<\/p>\n<p>First define your fetchxml and execute using Web API using following code.<\/p>\n<p>If you want retrieve formatted value and data about lookup properties. You have add following line in request header.<\/p>\n<pre class=\"lang:default decode:true\">xhr.setRequestHeader(\"Prefer\", \"odata.include-annotations=*\");<\/pre>\n<p>Below is the entire code.<\/p>\n<p>\/\/ This function is used to retrieve records using fetchxml<br \/>\nfetch: function (originalFetch, entitySetName, retrieveUsingFetchSuccess, retrieveUsingFetchError) {<\/p>\n<p>var fetch = null;<br \/>\ntry {<br \/>\n\/\/create the fetch<br \/>\nfetch = [&#8220;&#8221;, originalFetch, &#8220;&#8221;].join(&#8220;&#8221;);<br \/>\n\/\/encode the fetchxml<br \/>\nfetch = escape(fetch);<br \/>\n\/\/create AJAX request<br \/>\n$.ajax({<br \/>\ntype: &#8220;GET&#8221;,<br \/>\ncontentType: &#8220;application\/json; charset=utf-8&#8221;,<br \/>\ndatatype: &#8220;json&#8221;,<br \/>\n\/\/async: false,<br \/>\nurl: this.getWebAPIPath() + entitySetName + &#8220;?fetchXml=&#8221; + fetch,<br \/>\nbeforeSend: function (xhr) {<br \/>\n\/\/Specifying this header ensures that the results will be returned as JSON.<br \/>\nxhr.setRequestHeader(&#8220;Accept&#8221;, &#8220;application\/json&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;Content-Type&#8221;, &#8220;application\/json; odata.metadata=minimal&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;OData-MaxVersion&#8221;, &#8220;4.0&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;OData-Version&#8221;, &#8220;4.0&#8221;);<br \/>\n\/\/\/\/xhr.setRequestHeader(&#8220;Prefer&#8221;, &#8220;odata.include-annotations=OData.Community.Display.V1.FormattedValue&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;Prefer&#8221;, &#8220;odata.include-annotations=*&#8221;);<br \/>\n},<br \/>\nsuccess: function (data, textStatus, xhr) {<\/p>\n<p>if (data != null) {<\/p>\n<p>var entityObj = [];<br \/>\nvar pagingInfo = null;<\/p>\n<p>\/\/Check if results contains cookies<br \/>\n\/\/if yes then retrieve next set of records<br \/>\nif (data[&#8220;@Microsoft.Dynamics.CRM.fetchxmlpagingcookie&#8221;] != null) {<\/p>\n<p>\/\/get the pagingcookie<br \/>\npagingInfo = Inogic.ApiLib.getPagingCookie(data[&#8220;@Microsoft.Dynamics.CRM.fetchxmlpagingcookie&#8221;]);<\/p>\n<p>\/\/call this function create json object<br \/>\nInogic.ApiLib.createJsonObject(data, entityObj);<\/p>\n<p>\/\/call this to retrieve more records<br \/>\nInogic.ApiLib.fetchMore(originalFetch, entitySetName, retrieveUsingFetchSucess, retrieveUsingFetchError,pagingInfo.pageCokies,pagingInfo.pageNumber, entityObj);<\/p>\n<p>}<br \/>\n}<br \/>\n\/\/get the results and create json object<br \/>\nelse {<\/p>\n<p>var entityObj = [];<\/p>\n<p>\/\/ This function is used to create the json object<br \/>\nInogic.ApiLib.createJsonObject(data, entityObj);<\/p>\n<p>\/\/call successcallback<br \/>\nretrieveUsingFetchSuccess(entityObj);<br \/>\n}<br \/>\n},<br \/>\nerror: function (xhr, textStatus, errorThrown) {<br \/>\nretrieveUsingFetchError(Inogic.ApiLib.errorHandler(xhr));<br \/>\n}<br \/>\n});<br \/>\n} catch (e) {<br \/>\nthrow new Error(e);<br \/>\n}<\/p>\n<p>};<\/p>\n<p>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<\/p>\n<p>C-E511-80D8-F0921C194348%257d%2522%2520first%253d%2522%257b46A60D62-BF9C-E511-80DD-6C3BE5A8DACC%257d%2522%2520%252f%253e%253c%252fcookie%253e&#8221; istracking=&#8221;False&#8221; \/&gt;&#8221;<br \/>\nYou have decode this using &#8220;unescape&#8221;.<br \/>\npagingcookie = unescape(pagingcookie;)<br \/>\nWhen you decode then will looks like below.<br \/>\npagingcookie = &#8220;&#8221; istracking=&#8221;False&#8221; \/&gt;&#8221;<\/p>\n<p>From above <em>pagingcookie\u00a0 <\/em>you can extract the cookie part &amp; pagenumber and add this to fetchxml\u00a0 and pass this updated fetchxml to retrieve more records.<\/p>\n<p>When you execute fetch it return data in json format.<\/p>\n<p>To get the value of from result use following code.<\/p>\n<ol>\n<li>String\/Number etc<\/li>\n<\/ol>\n<p>var name = data.value[0].name;<\/p>\n<ol start=\"2\">\n<li>Lookup(Entity Reference)<\/li>\n<\/ol>\n<p>id: data.value[0]._primarycontactid_value,<\/p>\n<p>name: data.value[0][&#8220;_primarycontactid_value@OData.Community.Display.V1.FormattedValue&#8221;],<\/p>\n<p>logicalname: data.value[0][&#8220;_primarycontactid_value@Microsoft.Dynamics.CRM.lookuplogicalname&#8221;],<\/p>\n<ol start=\"3\">\n<li>Option set\/Money\/DataTime\/Boolean<\/li>\n<\/ol>\n<p>value: data.value[0].revenue<\/p>\n<p>formattedValue :data.value[0][&#8220;revenue@OData.Community.Display.V1.FormattedValue&#8221;]\n<p>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\u00a0a similar implementation for WEB API.<br \/>\n\/\/ This function is used to retrieve records using fetchxml<br \/>\nfetch: function (originalFetch, entitySetName, retrieveUsingFetchSuccess, retrieveUsingFetchError) {<\/p>\n<p>var fetch = null;<br \/>\ntry {<br \/>\n\/\/create the fetch<br \/>\nfetch = [&#8220;&#8221;, originalFetch, &#8220;&#8221;].join(&#8220;&#8221;);<br \/>\n\/\/encode the fetchxml<br \/>\nfetch = escape(fetch);<br \/>\n\/\/create AJAX request<br \/>\n$.ajax({<br \/>\ntype: &#8220;GET&#8221;,<br \/>\ncontentType: &#8220;application\/json; charset=utf-8&#8221;,<br \/>\ndatatype: &#8220;json&#8221;,<br \/>\n\/\/async: false,<br \/>\nurl: this.getWebAPIPath() + entitySetName + &#8220;?fetchXml=&#8221; + fetch,<br \/>\nbeforeSend: function (xhr) {<br \/>\n\/\/Specifying this header ensures that the results will be returned as JSON.<br \/>\nxhr.setRequestHeader(&#8220;Accept&#8221;, &#8220;application\/json&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;Content-Type&#8221;, &#8220;application\/json; odata.metadata=minimal&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;OData-MaxVersion&#8221;, &#8220;4.0&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;OData-Version&#8221;, &#8220;4.0&#8221;);<br \/>\n\/\/\/\/xhr.setRequestHeader(&#8220;Prefer&#8221;, &#8220;odata.include-annotations=OData.Community.Display.V1.FormattedValue&#8221;);<br \/>\nxhr.setRequestHeader(&#8220;Prefer&#8221;, &#8220;odata.include-annotations=*&#8221;);<br \/>\n},<br \/>\nsuccess: function (data, textStatus, xhr) {<\/p>\n<p>if (data != null) {<\/p>\n<p>var entityObj = [];<br \/>\nvar pagingInfo = null;<\/p>\n<p>\/\/Check if results contains cookies<br \/>\n\/\/if yes then retrieve next set of records<br \/>\nif (data[&#8220;@Microsoft.Dynamics.CRM.fetchxmlpagingcookie&#8221;] != null) {<\/p>\n<p>\/\/get the pagingcookie<br \/>\npagingInfo = Inogic.ApiLib.getPagingCookie(data[&#8220;@Microsoft.Dynamics.CRM.fetchxmlpagingcookie&#8221;]);<\/p>\n<p>\/\/call this function create json object<br \/>\nInogic.ApiLib.createJsonObject(data, entityObj);<\/p>\n<p>\/\/call this to retrieve more records<br \/>\nInogic.ApiLib.fetchMore(originalFetch, entitySetName, retrieveUsingFetchSucess, retrieveUsingFetchError,pagingInfo.pageCokies,pagingInfo.pageNumber, entityObj);<\/p>\n<p>}<br \/>\n}<br \/>\n\/\/get the results and create json object<br \/>\nelse {<\/p>\n<p>var entityObj = [];<\/p>\n<p>\/\/ This function is used to create the json object<br \/>\nInogic.ApiLib.createJsonObject(data, entityObj);<\/p>\n<p>\/\/call successcallback<br \/>\nretrieveUsingFetchSuccess(entityObj);<br \/>\n}<br \/>\n},<br \/>\nerror: function (xhr, textStatus, errorThrown) {<br \/>\nretrieveUsingFetchError(Inogic.ApiLib.errorHandler(xhr));<br \/>\n}<br \/>\n});<br \/>\n} catch (e) {<br \/>\nthrow new Error(e);<br \/>\n}<\/p>\n<p>};<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>In this way, We can use Web API to execute fetchxml instead of SOAP.<\/p>\n<p>Before you move on to the next post, check our <a href=\"http:\/\/bit.ly\/1Z0QR7M\" target=\"_blank\" rel=\"noopener noreferrer\">Maplytics InfoCenter<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.inogic.com\/blog\/2015\/12\/execute-fetchxml-using-web-api-in-dynamics-crm-2016\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[15,19,24,25,33,65],"tags":[708,798,1222],"class_list":["post-2157","post","type-post","status-publish","format-standard","hentry","category-development","category-dynamics-crm","category-dynamics-crm-2016","category-dynamics-crm-2016-update-1","category-javascript","category-webapi","tag-dynamics-crm-web-api","tag-fetchxml-dynhamics-crm","tag-ms-dynamics-crm-web-api"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/2157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=2157"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/2157\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=2157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=2157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=2157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}