Filter D365 Entities with OData

Filter D365 Entities with OData

OData is a very powerful tool to use in the realm of Dynamics 365.  However, a GET request alone can return way more information than is required. To solve this, you would need a way to filter or organize data that gets returned. Thankfully, we can accomplish this by appending query parameters to our GET request URL.

Before using OData we need to set up Dynamics to allow for external systems to request or send data. Read more about that here!

In this article, we will only go over the most commonly used query parameters and some common mistakes made when using them. For more information about these query parameters, visit: https://docs.microsoft.com/en-us/dynamics-nav/using-filter-expressions-in-odata-uris.

Filtering:

We can trim down the results returned to us with the filter parameter. Simply add ? to the end of your D365 URL to start, then $ to denote which parameter we are using. Here we are using filter, then set that filter equal to a string denoting what filter to use. Here is an example of getting invoices with the InvoiceDate field greater than or equal to a specified date.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$filter=InvoiceDate ge 2020-04-14T12:00:00Z

We can change ‘ge’ to a few different variables. less that (lt), Greater than (Gt), less than or equal to (le), equal to (eq), not equal to (ne), etc. Note that the type we are filtering on plays an important role in build the filter string. For example, the above was a date, but if we wanted to filter on a type that is System.String, we could not use gt/ge/lt/le, and we would need single quotations.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$filter=InvoiceNumber eq ‘X-00000213’

Selecting:

To limit the number of fields that get returned per record, we can use the select query parameter. Here is an example of getting the invoice number, date, and customer account from the SalesInvoiceHeaderV2 entity. Note that there can not be a space on either side of the equals sign when setting this select value.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$select=InvoiceNumber, InvoiceDate, InvoiceCustomerAccountNumber

Counting:

If you want to know how many records are getting returned from a specific GET request you can use the count query parameter. Just set it equal to true.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$count=true

Top:

If you only want a few records returned from a GET request, use the top query parameter. For example, here we only want the first record from a data entity.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$top=1

Multiple query parameters:

You can use multiple query parameters at once. Just combine with ‘&’ and make sure to add a new ‘$’ before the next query parameter.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$count=true&$orderby=InvoiceDate desc

Enums:

To use an enum value in an OData URL we need to use a special syntax. For example, here we are only getting return orders in the Open Order (Backorder) status.

https://X.sandbox.operations.dynamics.com/data/ReturnOrderHeaders?$filter=ReturnOrderStatus eq Microsoft.Dynamics.DataEntities.SalesStatus’Backorder’

Order by:

To change the order of the records in the response, we can use order by. Here is an example of ordering on invoice date. Note that if we wanted to we could change desc to asc to make the order ascending.

https://X.sandbox.operations.dynamics.com/data/SalesInvoiceHeadersV2?$count=true&$orderby=InvoiceDate desc

Multiple filters:

We can make complex filters by combining logic. For example, here we are looking for return orders that are open and have a specific value, we could have change the ‘or’ to ‘and’ to change the results.

https://X.sandbox.operations.dynamics.com/data/ReturnOrderHeaders?$filter=ReturnOrderStatus eq Microsoft.Dynamics.DataEntities.SalesStatus’Backorder’ or ReturnOrderNumber eq ‘XX-000062’

Hopefully now that you know how to filter D365 entities with OData query parameters help you get the most out of your D365 environment! 

For more tech blogs, subscribe to the marked_code news letter: http://eepurl.com/gZCMQz