OData is a standard for accessing data from a databasesuited to contemporary programming practise which often involves consuming aweb request in json format from a web endpoint. Your average JavaScriptprogrammer can access data easily using an OData endpoint.

The standard originated with Microsoft and many of theirsoftware packages including Dynamics 365, Microsoft NAV and MicrosoftFinancials have great support for OData. Power BI and other data visualisationpackages such as Qlik and Tableau can also consume OData.

This article introduces OData for Dynamics 365 and acts as aprimer for the data access language. We also demonstrate our Redware OData Browserto show you how easy it is to select and filter your data to get only the datayou need from the OData endpoint.

You can view this blog as a video if you prefer.

We start by using the browser to viewOData queries against your online (or on-premise) Dynamics 365. First, locate the endpoint from theSettings-Customizations-Developer Resources page of your CRM Installation.

As you are already logged on to Dynamics 365 you can enterthe URL into your browser to view all the EntitySets available to you or add$metadata to the URL to see a full description of the Web API for your copy ofDynamics 365.

Dynamics 365 has a lot of entities but now we can try outour first query for example to get data from the accounts EntitySet:

https://telecomsbilling02.api.crm11.dynamics.com/api/data/v9.1/accounts

Note that you will need to browse first to your own Dynamics365 and use the endpoint as th4e prefix to all these queries because you cannotaccess our CRM without logging in first.

Dynamics 365 displays a maximum of 5,000 entities at any onetime and we can cut down the number of rows of data we are requesting easily byusing the $top qualifier on our URL:

https://telecomsbilling02.api.crm11.dynamics.com/api/data/v9.1/accounts?$top=5

Note that there is a ? character after the EntitySet nameand the beginning of the OData clauses.

There is a little too much information there so lets reducethe number of attributes showing with the $select=name,accountnumberclause to specify the attributes we want to view (just name and accountnumber) andthe $top=5 clause to show just the first 5 records.

https://telecomsbilling02.api.crm11.dynamics.com/api/data/v9.1/accounts?$top=10&$select=name,accountnumber

Note that the OData clauses are separated with the &character.

If you examine the json you can see some additionalinformation such as the ETAG and the account identifier. These are used toidentify an individual records to update some data for example. If you know theidentifier you can request an individual record by putting the key value inbrackets after the EntitySet:

https://telecomsbilling02.api.crm11.dynamics.com/api/data/v9.1/accounts(f77f38ac-982f-e811-a957-00224807251a)?$select=name

Even experienced programmers can find json a little bit of amuddle so we have developed the Redware OData Browser to make designing ODataqueries interactively much easier. You can see below the same OData query withformatted json.

Now I dare say there is a great add-on for most browsers toview the data in the same way but we can also view the data in tabular format withthe Redware OData Browser which we will do from now on in this blog.

We can sort the rows using the $orderby=name clause (note weare missing out the first part of the OData URL from now on):

accounts?$top=5&$select=name,accountnumber&$orderby=name

You can reverse the order by adding the desc clause:

accounts?$top=10&$select=name,accountnumber&$orderby=namedesc

The $filter clause is used to select records in the table sothe example below selects all accounts in London:

accounts?$top=10&$select=name,accountnumber,address1_city&$orderby=namedesc&$filter=address1_city eq 'london'

Here are the filter expressions supported by Dynamics 365and you can find more details in the Microsoft documentation here.

You can join conditions together with and, or, not and useparentheses to group conditions together. There are also some additionalfunctions available:

You can pull in data from a related entity in Dynamics CRMand apply the $select clause to specify which attributes to receive. In theexample below each account entity may have one primary contact specified by theprimarycontactid lookup attribute. The query brings in the fullname field fromthe contact entity if the primarycontactid value is specified on the account:

accounts(f77f38ac-982f-e811-a957-00224807251a)?$select=name&$expand=primarycontactid($select=fullname)

There is a special case for the customerid variable inDynamics 365 which can relate to either the account or contact entities and soyou can use customerid_account or customerid_contact instead of the customeridattribute.

invoices?$select=invoicenumber&$expand=customerid_account($select=name,address1_city)&$top=3

The above expansion used a lookup field for a many to onerelationship but you can also use the relationship name to expand in the otherdirection for example to show all the invoices for each account using theinvoice_customer_accounts relationship;

accounts?$select=name&$top=5&$expand=invoice_customer_accounts($select=invoicenumber)

The Dynamics 365 relationships are known as 'single valued'or 'collection valued' navigation properties and are documented in the metadataand described here.

In this short tutorial on querying Dynamics 365 with ODatawe have looked at querying from the Dynamics 365 OData endpoint by using:

• entity(key) to select a single record using a key value.
• $select=attribute1,attribute2 to select the attributes to be pulled in to the query.
• $filter=attribute eq 'value' to filter according to the selection criteria.
• $orderby=cname desc to order the rows as desired.
• $top=3 to limit the number of rows.

Note - please do not confuse the V4.0 OData endpointdescribed here which is part of the Web API with the old legacy V2.0 ODataendpoint still available with Dynamics 365.

You saw how to use the Web Endpoint to interactively executea query using the browser (and also our Redware OData Browser tool) to viewqueries. You can use the same queries inside a program or from JavaScriptwithin a CRM form or external website.

Note that Microsoft requires you to set up an API Key beforeyou can use OData from an external program as detailed here.

OData can also be used to access actions and functions thatpower some aspects of Dynamics 365 and also to update, delete, and add recordsquite easily using json. There is also more to the OData specification itself forexample to count and calculate aggregate data for totals and averages.

In fact, the whole of the Dynamics 365 API is now called viaOData through the addition of actions and functions. But here we wanted to showthe power of OData as query language so happy browsing !