Querying data in Microsoft Dynamics CRM 2016 using Web API

Introduction:

With Microsoft Dynamics CRM 2016, Web API that was in Preview for CRM 2015 is now GA. Web API is really OData V4 which is the later version of OData V2 that was implemented in earlier versions of CRM until CRM 2015.

With CRM 2016, OData V2 is deprecated, this means that though the legacy code written using OData V2 will continue to function, it is not advised to continue to develop using this version of OData starting CRM 2016.

The earlier version of OData was strictly restricted to CRUD operations, how with Web API you can even perform operations that earlier required us to use SOAP requests.

In the earlier post, it was all about executing FetchXML queries using Web API, well that was to some extent because I didn’t have much success querying and applying some very common queries. After spending some more time with WEB API and trying to get things done exclusively using OData V4, I did get quite a few things figured out.

In this post, I am listing out some of the frequently used queries and how these could be generated using Web API.

OData v4 endpoints:

Under Developer Resources, you can now find the endpoint for Web API.

Notice the change in the URL structure, it is now /api/data/v8.0/

Earlier it was /xrmservices/2011/organizationdata.svc/

Sample Queries:

1. List accounts starting with A and C

   var qry = “accounts?$filter=startswith(name,’A’) or startswith(name,’C’)”;
   

   Since field names have not been specified all the columns are listed.

   The entitycollection does not include Set Prefix anymore besides its using the logical name throughout.

2. List accounts owned by a particular user

   var qry = “accounts?$select=name,accountnumber,_primarycontactid_value,createdon,accountcategorycode,revenue&$filter=_ownerid_value%20eq%201ea6fc32-9b2d-47e6-b9ab-b5f23f69e34f “;

   Here we had to provide the guid of the user that you need to filter by. Also notice that the ownerid attribute has been mentioned as _ownerid_value

   All lookups are listed in the query result to follow the naming structure

   _<logicalname>_value.

3. Read account details along with the primary contact phone

   We need to use the $expand clause to request data of the related entity. However, it appears that the $expand clause only works when requesting data of a single record as shown in the below query.

   var qry = “accounts(4ef2c099-bca1-e511-80de-3863bb349a78)?$select=name,accountnumber,_primarycontactid_value,createdon,accountcategorycode,revenue&$filter=contains(name,'(sample)’)&$orderby=name&$expand=primarycontactid($select=fullname,telephone1)”
   

   If you were to request this for the entire accounts collection, you receive the following error message

   {
   
   "error":{
   
   "code":"","message":"Expansion of navigation properties isn\u2019t supported on entity collections.","innererror":{
   
   "message":"Expansion of navigation properties isn\u2019t supported on entity collections.","type":"Microsoft.Crm.CrmHttpException","stacktrace":" at

4. List all Phone Calls of an account

   This will display only the subject of the related phone calls.

   var qry = “accounts(4ef2c099-bca1-e511-80de-3863bb349a78)?$expand=Account_Phonecalls($select=subject)”

Understand the Query Results:

Unlike the OData V2 queries, the results here are returned in JSON format

{

"@odata.context":"https://dyn20161.crm5.dynamics.com/api/data/v8.0/$metadata#accounts(name,accountnumber,_primarycontactid_value,createdon,accountcategorycode,revenue)","value":[

"@odata.etag":"W/\"632098\"","name":"Alpine Ski House (sample)","accountnumber":"ABCO9M32","_primarycontactid_value":"1cf3c099-bca1-e511-80de-3863bb349a78","createdon":"2015-12-13T17:12:15Z","accountcategorycode":null,"revenue":90000.0000,"accountid":"4cf2c099-bca1-e511-80de-3863bb349a78","_transactioncurrencyid_value":"5022ed92-bca1-e511-80e1-3863bb345aa8"

},{

"@odata.etag":"W/\"632065\"","name":"A. Datum Corporation (sample)","accountnumber":null,"_primarycontactid_value":"1ef3c099-bca1-e511-80de-3863bb349a78","createdon":"2015-12-13T17:12:15Z","accountcategorycode":null,"revenue":10000.0000,"accountid":"4ef2c099-bca1-e511-80de-3863bb349a78","_transactioncurrencyid_value":"5022ed92-bca1-e511-80e1-3863bb345aa8"

}

]

}

Now if see the results here, the primary contact id only provides the Guid, not really helpful, and for currency data types, it again returns only the number, the currency is provided as transaction guid, so again formatted value missing.

If we want the results to return the formatted values, we need to pass the following in the header tag of the request

odata.include-annotations=”OData.Community.Display.V1.FormattedValue”

I haven’t figured it out how to pass these in the browser url, but here is the code I use to pass the header values.

var qry = url;

var headers = {


'Content-Type': 'application/json',

Accept: 'application/json', 'OData-MaxVersion': 4.0,

Prefer: 'odata.include-annotations="*", odata.maxpagesize=3'

};

var request = {

requestUri: qry,

method: 'GET',

headers: headers,

data: null

};

 
odatajs.oData.request(

request,

successCallback,

errorCallback);

In the above code, you can replace the “*” with “OData.Community.Display.V1.FormattedValue”, the other preferences supported are

Microsoft.Dynamics.CRM.lookuplogicalname

Microsoft.Dynamics.CRM.associatednavigationproperty

Generally, for lookups, we are used to receiving 3 set of data,

The lookup entity name, the guid and the record name. By default we only received the Guid of a lookup field, if we need the record name and the entity name, we need to include the preferences listed above.

If you want all preferences to be applied, pass “*”

With all preferences the applied the result would appear as

Lookup field details are available as shown above

_primarycontactid_value: “1ef3c099-bca1-e511-80de-3863bb349a78”

_primarycontactid_value@Microsoft.Dynamics.CRM.associatednavigationproperty: “primarycontactid”

_primarycontactid_value@Microsoft.Dynamics.CRM.lookuplogicalname: “contact”

_primarycontactid_value@OData.Community.Display.V1.FormattedValue: “Rene Valdes (sample)”

And we now also get the revenue formatted with the currency symbol and the date.

Paging:

To handle paging you need to specify the page size, which is now called odata.maxpagesize=n

N stands for the count of records to be returned.

You also receive the paging cookie, which will allow you to request the next set of records.

Use the nextlink url to the Web API to request the next set of data. It returns a fully qualified url with the query parameters set that can be used just as is.

Result Count:

When you include the $count=true in your query, you get the total count of the records returned by the query in the query result.

If you query the following,

var qry5 = “https://xxx.crm5.dynamics.com/api/data/v8.0/accounts?$count=true&$top=2”

You get

The total count is 19 even though the results requested were only top 2

Note, if there are more than 5000 records that the query returns, the count here would still return 5000 only.

Conclusion:

The Web API query still has a few limitations and like the earlier OData V2, there are limitations on what kind of data can be queried. You can find the complete list here.

In this post I have covered queries, in the later blogs hope to cover the other operations supported by WEB API.

Make your Dynamics CRM life easy with Inogic Dynamics CRM Solutions. Read more about our add-ons here.

Cut short 90% of your manual work and repetitive data entry!

Get 1 Click apps and say goodbye to all repetitive data entry in CRM –
Click2Clone – Clone/Copy Dynamics 365 CRM records in 1 Click
Click2Export – Export Dynamics 365 CRM Report/CRM Views/Word/Excel template in 1 Click
Click2Undo – Undo & Restore Dynamics 365 CRM data in 1 Click