CLOSE
CLOSE
https://www.sikich.com

How to Filter Microsoft Dynamics 365 Entities with OData

OData is a very powerful tool to use in the realm of Microsoft 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 Microsoft 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

filtering invoices by invoice date

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’

changing the system string

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

limiting number of fields returned in OData

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

counting number of records returned

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

using top query parameter

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

using multiple query parameters

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’

how to use enums in OData filters

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

filter by order by parameter

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’

using complex filters in OData

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

For more D365 tech advice, connect with us here.

Post originally published here: https://markedcode.com/index.php/2020/04/20/how-to-filter-dynamics-365-entities-via-odata-get-requests/

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author