How to Fetch Data from Dynamics 365 Using OData

Introduction: Why Use OData?

OData (Open Data Protocol) is a standard protocol for creating and consuming data APIs, enabling seamless data interaction over the web using standard HTTP protocols. With OData, you can simplify data integration and manipulation, making it an essential tool for businesses leveraging Dynamics 365. This article explores how to effectively use OData to fetch data from Dynamics 365, including various query techniques and practical use cases.

What is OData?

OData is a protocol designed for creating and consuming data APIs. It leverages standard HTTP methods and conventions to interact with data services. For a comprehensive overview of OData, check out this detailed guide.

Why Use OData?

Ease of Integration: OData simplifies the process of integrating different systems and applications by providing a standardized way to interact with data. Developers can use a common set of conventions and protocols, reducing the need for custom integration logic.

Powerful Query Capabilities: OData supports a wide range of query options through URL parameters, allowing developers to perform complex data queries directly within the URI. This reduces network bandwidth and improves performance by retrieving only the necessary data.

Standardized: As a standardized protocol, OData offers a consistent interface for accessing and manipulating data across various platforms, making integration and maintenance more predictable.

Why Do I Need to Use OData?

Using OData services can streamline data integration, especially for beginners and simpler operations. By leveraging existing HTTP request methods, applications can integrate OData functionality without needing new libraries or frameworks. For practical implementation, explore DBSync’s Cloud Workflow Tool which supports user-provided queries to access data as needed.

How to Use OData with Dynamics 365

Using OData services can streamline data integration, especially for beginners and simpler operations. By leveraging existing HTTP request methods, applications can integrate OData functionality without needing new libraries or frameworks. For practical implementation, explore DBSync’s Cloud Workflow Tool which supports user-provided queries to access data as needed.

How to Use OData with Dynamics 365

Set up Dynamics365 (or Business Central) flow on DBSync

You can follow the following steps to get started quickly:

Sign up and start free trial

1. Create a free trial account by navigating to the DBSync website.
2. Click on ‘Start Free Trial’.
3. You will be redirected to the DBSync product suite. Select the product ‘Cloud Workflow’ and click ‘Start Trial’.
4. Provide your business email and click ‘Go’.
5. Activate your account and utilize these credentials to log in to DBSync Cloud Workflow.
6. To log in, go to the DBSync homepage and click on ‘Login’  and choose ‘Cloud Workflow’.
7. Provide your ‘User Name’ and ‘Password’ setup during signup and click ‘Login’.

Create a new flow from scratch using DBSync’s Flow Builder
(For detailed steps and video tutorial, follow Flow Builder tutorial or use one of the predefined templates)

1. Click the Apps icon from the left navigation toolbar to add desired applications
2. Click on Dynamics365 CRM Connector and then Enter Username and Endpoint and then choose ‘Connect to Dynamics 365 CRM’
3. You need to enter Credentials(Username, Password), and then you can ‘Test Connection’.
4. On the home screen, click ‘Create’, enter a project name, and click enter or press ‘Next’.
5. Add a new Task by clicking the ‘+ Task’ button and clicking ‘Create’.
6. Name the task as needed and change Agent to ‘Assign to bot’.
7. Click on the ‘configure’ button. 
8. On this page, you can create a flow using Actions available on the left-hand side.
9. For this tutorial, drag and drop ‘Query’ to the center.
10. Click on the query to open the ‘Configure’ section on the right.
11. From the source select your application linked earlier. From the Object dropdown, select any object. This will open the ‘Configure Query’ section.
12. Select the gear settings icon and select ‘Advanced Query Builder’.
13. In the space, you can start writing your OData queries and click on the preview button to get the results.
14. Details on how to write OData queries is given in the ‘Requesting Data’ section below.

To effectively use OData with Dynamics 365, understanding the metadata is crucial. The metadata endpoint provides a detailed description of available entities, their properties, relationships, and supported operations.

Accessing Metadata

Retrieve metadata using the ‘$metadata’ endpoint provided by Dynamics 365 OData services:

Unset
GET baseUrl/api/data/v9.0/$metadata

Java
<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx" Version="4.0">
 <edmx:DataServices>
   <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="YourOrg">
     <!-- Entity Types -->
     <EntityType Name="account">
       <Key>
         <PropertyRef Name="accountid" />
       </Key>
       <Property Name="accountid" Type="Edm.Guid" Nullable="false" />
       <Property Name="name" Type="Edm.String" />
       <!-- Other properties -->
     </EntityType>
    
     <!-- Navigation Properties -->
     <EntityContainer Name="YourOrgEntities">
       <EntitySet Name="accounts" EntityType="YourOrg.account" />
       <!-- Other entity sets and navigation properties -->
     </EntityContainer>
   </Schema>
 </edmx:DataServices>
</edmx:Edmx>

We’re going to focus on the two primary ways to fetch the data – 

1. by requesting data (using entities)  
2. by querying data (using queries)

OData services support HTTP GET requests for fetching data.

Requesting Data

For our target server at (say) baseUrl, assume we have an entity Account.

# Requesting Entity Collections:

Java
GET baseUrl/accounts

The response payload returned from this request typically comprises a structured data format, commonly JSON or XML, containing information about user accounts stored within the system.  Additionally, the payload may contain metadata elements such as entity identifiers, versioning information, and edit links.

Try Out:

(The baseUrl is not needed since we’ve already established the connection to an application.)

# Requesting an Individual Entity by ID:

Java
GET baseUrl/accounts('12345')

The response would be similar to the previous request but would contain just one ‘account’ with account id ‘12345’.

Try Out:

# Requesting an Individual Property:

To address an entity property clients append a path segment containing property name to the URL of the entity.

Java
GET baseUrl/accounts('12345')/Name

Response:

{
    "@odata.context": "serviceRoot/$metadata#Account('12345')/Name",
    "value": "DBSync Account Name"
}

# Requesting an Individual Property Raw Value:

To get the raw value of a primitive property, append a path segment containing the string $value to the URL.

Java
GET baseUrl/Account('12345')/Name/$value

Response:

Unset
DBSync Account Name

Querying Data

System Query Option $filter

Users can append $filter query options to the URL to filter data before retrieval.

The $filter system query option enables clients to refine a collection of resources referenced by a request URL. Within this option, the provided expression undergoes evaluation for each resource within the collection. Subsequently, only those items for which the expression yields true are incorporated into the response.

Java
GET baseUrl/accounts?$filter=address1_postalcode eq 'M6N 1K8'

SQL Equivalent: WHERE address1_postalcode = ‘M6N 1K8’
Try out:

Java
GET baseUrl/accounts?$filter=contains(address1_postalcode, 'M6N') 

SQL Equivalent: WHERE address1_postalcode LIKE ‘%M6N%’
Try out:

# System Query Option $orderby

The $orderby system query option allows users to request resources in either ascending order or descending order using asc (default) or desc.

Java
GET baseUrl/accounts?$orderby=versionnumber desc

SQL Equivalent: ORDER BY versionnumber DESC

Try out:

# System Query Option $top and $skip

The $top system query option specifies how many items to include from the queried collection in the result. Conversely, the $skip query option indicates how many items to skip from the queried collection, excluding them from the result.

Java
GET baseUrl/accounts?$top=3

SQL Equivalent: LIMIT 3
The above request fetches the first twenty people from the Accounts entity set.
Try out:

Java
GET baseUrl/accounts?$skip=15

SQL Equivalent: OFFSET 15
The above request returns the people starting with the 16th one from the People entity set.

System Query Option $count

The $count system query option permits clients to ask for a count of the matching resources alongside the resources in the response.

Java
GET baseUrl/accounts?$count=true

SQL Equivalent: SELECT COUNT(*) FROM …

System Query Option $expand

The $expand system query option specifies the related resources to be included in line with retrieved resources. The request below returns people with navigation property Friends of a Person.

Java
GET baseUrl/salesorders?$expand=order_details

SQL Similar: JOIN operations
Try out:

System Query Option $select

The $select system query option allows the clients to request a limited set of properties for each entity.

Java
GET baseUrl/salesorders?$select=statecode,order_details&$expand=order_details

SQL Equivalent: SELECT Name, Phone FROM …
Try out:

Conclusion

Utilizing OData with Dynamics 365 offers robust capabilities for fetching and querying data. By understanding the metadata and employing various query options, you can effectively interact with your data. For more, schedule a demo with our experts to see how DBSync’s employs the open data protocol for Dynamics 365 .