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

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

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

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

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

As you are already logged on to Dynamics 365 you can enter the 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 of Dynamics 365.

Dynamics 365 has a lot of entities but now we can try out our 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 Dynamics 365 and use the endpoint as th4e prefix to all these queries because you cannot access our CRM without logging in first.

Dynamics 365 displays a maximum of 5,000 entities at any one time and we can cut down the number of rows of data we are requesting easily by using 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 name and the beginning of the OData clauses.

There is a little too much information there so lets reduce the number of attributes showing with the $select=name,accountnumber clause to specify the attributes we want to view (just name and accountnumber) and the $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 additional information such as the ETAG and the account identifier. These are used to identify an individual records to update some data for example. If you know the identifier you can request an individual record by putting the key value in brackets 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 a muddle so we have developed the Redware OData Browser to make designing OData queries interactively much easier. You can see below the same OData query with formatted json.

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

We can sort the rows using the $orderby=name clause (note we are 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=name desc

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

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

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

You can join conditions together with and, or, not and use parentheses to group conditions together. There are also some additional functions available:

You can pull in data from a related entity in Dynamics CRM and apply the $select clause to specify which attributes to receive. In the example below each account entity may have one primary contact specified by the primarycontactid lookup attribute. The query brings in the fullname field from the 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 in Dynamics 365 which can relate to either the account or contact entities and so you can use customerid_account or customerid_contact instead of the customerid attribute.

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

The above expansion used a lookup field for a many to one relationship but you can also use the relationship name to expand in the other direction for example to show all the invoices for each account using the invoice_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 metadata and described here.

In this short tutorial on querying Dynamics 365 with OData we 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 endpoint described here which is part of the Web API with the old legacy V2.0 OData endpoint still available with Dynamics 365.

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

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

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

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