Prior to CRM 2016, REST queries were based on the OrganizationData service. However the functionalities provided by the REST API were really limited to the CRUD operations and Associate Requests. If you need to execute complex fetchxml queries, either you need to take your code to the server side or call the Organization service endpoint to execute your fetch xml. The problem with using the SOAP endpoint is that you need to construct the raw SOAP request and parse the response back.
I always wondered if we could have something in CRM with the power of SOAP endpoint and the simplicity of using url based queries like the REST endpoint. What a great combination it would make? Well I guess Microsoft has heard me and like me, many CRM consultants and have introduced the new Web API in Dynamics CRM 2016.
With Web API, you can execute System Views, Personal Views and even custom fetch xml using the Web API. Excited? So let’s explore it one by one. I will not be using any kind of code here. I will just demo this using the browser. After all, all the Web API queries are URL based. Isn’t it great!
Executing a System View using Web API.
Say I want to execute the ‘Active Contacts’ system view of the contact entity. For that first I need to get the viewid for the ‘Active Contacts’ view. So let’s get the id first. Below is the query for the same.
https://xrmtr11.crm5.dynamics.com/api/data/v8.0/savedqueries?$select=name,savedqueryid&$filter=name eq ‘Active Contacts’
The greyed out portion is your CRM Organization URL. Just take this URL and put in your browser. You would results as shown in the screenshot below.
This is JSON response. You can easily parse the response and get the id of the ‘Active Contacts’ view.
Now once you have the guid of the view, you can execute the view to get the results of the view with the below URL.
You would get the JSON result as shown above. As you can identify, you can see the contacts for your organization here.
Using the same approach, you can execute your personal views to get the results.
Execute custom fetchxml queries using Web API.
I would guess that this is something you will mostly end up using with. Frequently we need to execute custom fetchxml queries from our javascript. And how simple it would be if we could just execute it from URL based query. So let’s see how it can be done.
Below is the fetchxml to retrieve active accounts in the system.
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”account”>
<attribute name=”name” /> <filter type=”and”>
<condition attribute=”statecode” operator=”eq” value=”0″ />
</filter>
<link-entity name=”contact” from=”contactid” to=”primarycontactid” visible=”false” link-type=”outer” alias=”accountprimarycontactidcontactcontactid”>
<attribute name=”emailaddress1″ />
</link-entity>
</entity>
</fetch>
The first thing we need to do here is encode the URI using the encodeURI method of javascript. For this demo, I will simply pull up the developer tools in the chrome browser and encode the fetchxml in the console. Below is the output of the same.
Now I run the below query and it fetches me all the Active Accounts.
Below is the result for the same.
Hope this helps! Happy exploring CRM 2016.
Hi Mr. Debajit Dutta,
I am Trying To Fetch the Lookup Data Using Web Api in crm 2016 But i am Not getting the Data(error: Bad Query Syntax Error),Can You Please help me or post Web Api Code To fetch Lookup Data Lookup Data.
Hi Kundan,
The url should be something like this
[Organization URI]/api/data/v8.0/accounts(99390c24-9c72-e511-80d4-00155d2a68d1)?$select=accountid&$expand=parentaccountid($select%20=%20createdon,%20name)
I have retrieving the parentaccountid for a specific account record. The parentaccount is a lookup on the account form.
Let me know if this works for you.
Mr. Debajit ,
Sorry for My Late Response..I Am posting my JS code that i am using for Fetching Data by Lookup.(new_totalratelookup and new_productname present in record that will be fecthed by lookup from new_totalrates entity )
function retrieveRecorByLookup()
{
var lookupObject = Xrm.Page.getAttribute(“new_totalratelookup”).getValue();
var serverURL = Xrm.Page.context.getClientUrl();
if (lookupObject != null) {
var name = lookupObject[0].name;
var ID = lookupObject[0].id;
}
var entityName = “new_totalrates”;
var EntityId = Xrm.Page.data.entity.getId();
var columnSet = “?$select=EntityId&$expand=ID($select=new_productname)”;
var Query = entityName + “(” + ID + “)” + columnSet;
var req = new XMLHttpRequest();
req.open(“GET”, serverURL + “/api/data/v8.0/” + Query, true);
req.setRequestHeader(“Accept”, “application/json”);
req.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);
req.setRequestHeader(“OData-MaxVersion”, “4.0”);
req.setRequestHeader(“OData-Version”, “4.0”);
req.setRequestHeader(“Prefer”, “odata.include-annotations= “*”, odata.maxpagesize=3″);
req.onreadystatechange = function() {
if (this.readyState == 4 ) {
req.onreadystatechange = null;
if (this.status == 204) {
var data = JSON.parse(this.response);
if(data!=null && data.new_productname!=null)
alert(data.new_productname);
} else {
var error = JSON.parse(this.response).error;
alert(error.message);
}
}
};
req.send();
}
if i understand correctly, new_totalratelookup is the lookup field on the new_totalrate entity record and you wish to derive new_productname value for new_totalratelookup field. If this is the case, the following query should suffice.
Query = entityName + “(” + EntityId + “)” + “/new_totalratelookup?$select=new_productname”
-Debajit
Sorry Debjait,,actually new_totalratelookup is on new_calculaterate entity and new_totalrate is the entity where new_productname field is present ,so i want to fetch new_productname from new_calculaterate entity(by lookup new_totalratelookup) .
Sorry Debjait,,actually new_totalratelookup is on new_calculaterate entity and new_totalrate is the entity where new_productname field is present ,so i want to fetch new_productname on new_calculaterate entity(by lookup new_totalratelookup) .