Working with OData Protocol in Dynamics 365 Finance and Operations

OData (Open Data Protocol) is a standardized protocol for creating and consuming RESTful APIs. It enables seamless data exchange between applications and is widely used in Microsoft technologies, including Dynamics 365 Finance and Operations (D365FO). OData allows developers to access and manipulate data using standard HTTP methods such as GET, POST, PUT, and DELETE.

Importance of OData in D365FO

OData plays a crucial role in D365FO by providing a way to:

• Retrieve and manipulate data through API endpoints.
• Integrate with external applications.
• Enable reporting and analytics tools to access real-time business data.

OData services

We provide an OData REST endpoint that exposes all data entities designated as IsPublic in the Application Object Tree (AOT), meaning only data entities with the IsPublic property set to Yes are accessible through OData endpoints. This endpoint fully supports CRUD (Create, Read, Update, and Delete) operations, enabling users to insert and retrieve data from the system.

OData Endpoints

Service endpoint: It will return the list of all the OData entities available in the system.

Entity: It is used as a URL to trigger a specific entity, such as the CustomersV2 entity, which returns customer master data.

OData supported

• CRUD support is handled through HTTP verb support for POST, PATCH, PUT, and DELETE.
• The OData service supports serving driven paging with a maximum page size of 10,000.
• Available query options are:

1. $filter
2. $count
3. $skip
4. $orderby
5. $top
6. $expand
7. $select

these above 7 queries expression used in the OData URL. Let learn more above it with an example.

1) $filter

In an OData endpoint URL, $filter is a query option used to apply filters and retrieve specific data based on defined conditions. It allows users to limit the results returned by the query by specifying criteria for filtering data entities.

Syntax Example:

https://your-d365fo-instance/data/CustomersV2?$filter=CustomerAccount eq 'CUST00003'        

Explanation:

• $filter: Indicates that a filter condition is being applied.
• CustomerAccount eq 'CUST00003': Retrieves only the records where Customer Id is equal to CUST00003.

Common Operators in $filter

$filter - Calling through POSTMAN tool

2) $count

In an OData endpoint URL, $count is a query option used to retrieve the total number of records that match a query, without fetching the full dataset. It is useful for pagination, reporting, or when you need to display record counts.

Get the total count of all records in the CustomersV2 entity

https://your-d365fo-instance/data/CustomersV2?$count=true        

$count - Calling through POSTMAN tool

This returns the total number of records in the CustomersV2 entity. Here 26 is our total count.

3) $Skip

In an OData endpoint URL, $skip is a query option used to skip a specified number of records before returning results. It is commonly used for pagination, allowing users to fetch data in chunks rather than retrieving all records at once.

Usage Examples

Example 1: Skip the first 10 records and retrieve the next set.

https://your-d365fo-instance/data/CustomersV2?$skip=10
        

Example 2 Use $skip with $top: This returns 10 records after skipping the first 20 records.

https://your-d365fo-instance/data/CustomersV2?$top=10&$skip=20
        

Example 3 Use $skip with $count: This returns 2 records after skipping the first 2 records and also return the total number of records count.

https://your-d365fo-instance/data/CustomersV2?$count=true&$top=2&$skip=2        

$Skip - Calling through the POSTMAN

In the Postman tool screenshot above, the response returns the total record count along with two customer records—specifically, the third and fourth records—after skipping the first two customer records.

4) $orderby

In an OData endpoint URL, $orderby is a query option used to sort the results based on one or more fields. It allows you to specify the sorting order (ascending or descending) for the data returned from the query.

Syntax

https://your-d365fo-instance/data/CustomersV2?$orderby=CustomerName asc
        

Common Sorting Options:

• Ascending (asc): Sorts from smallest to largest, or alphabetically from A to Z.
• Descending (desc): Sorts from largest to smallest, or alphabetically from Z to A.

$orderby - Calling through the POSTMAN

5) $top

In an OData endpoint URL, $top is a query option used to specify the number of records to retrieve from the dataset. It allows you to limit the amount of data returned by the query.

Syntax

https://your-d365fo-instance/data/CustomersV2?$top=10        

This retrieves the first 10 records from the CustomersV2 entity.

I have already covered the example of $top in conjunction with the $skip query in point 3 above

6) $expand

In an OData endpoint URL, $expand is a query option used to include related entities (also known as navigation properties) in the response. This allows you to retrieve associated data along with the primary entity in a single request, rather than making separate queries for each related entity.

Syntax

https://your-d365fo-instance/data/SalesOrderHeadersV2?$expand=SalesOrderLines
        

"SalesOrderLines" is the public collection name of the entity. You can find the public collection name by opening the entity in Visual Studio, as shown in the figure below.

Use Cases for $expand:

• Retrieve related data in a single API call, reducing the need for multiple requests.
• Optimize data fetching by fetching both primary and related entities at once (e.g., sales order header and lines).

Use of $expand with $filter

Scenario: We need to fetch the list of all invoiced status sales orders with sales line details.

Syntax

https://your-d365fo-instance//data/SalesOrderHeadersV2?$expand=SalesOrderLines&$filter=SalesOrderStatus eq Microsoft.Dynamics.DataEntities.SalesStatus'Invoiced'        

$expand with $filter - Calling through POSTMAN

7) $select

In an OData endpoint URL, $select is a query option used to specify which fields (or properties) of the entity you want to include in the response. By using $select, you can limit the data returned to only the fields you need, which helps improve performance by reducing the amount of data transferred.

Syntax

https://your-d365fo-instance/data/Customers?$select=CustomerAccount, NameAlias, dataAreaId        

This retrieves only the CustomerAccount, NameAlias and dataAreaId fields from the CustomersV2 entity, excluding all other fields.

Use Cases for $select:

• Retrieve only specific fields: When you need data from only a few fields and want to minimize the response size.
• Optimize performance: Reduces data payload when dealing with large entities with many properties.

$select - Calling through POSTMAN

This is the end of this blog.

Happy Learning,

Syed Amir Ali.