How to Add $filter, $top, $select, cross-company=true and $orderby in in OData URL

OData URLs in Dynamics 365 Finance and Operations (D365FO) provide a flexible way to query data. In this blog post, we will demonstrate how to use the $filter, $top, cross-company=true,$select and $orderby query options to retrieve specific records based on certain criteria.

Using $filter

The $filter query option allows you to specify criteria to filter the data returned by the OData service also you need to add "and" operator between 2 filter values and "&" between 2 diffrent options ($filter, $orderby). Below is an example:

{{resource}}/data/PurchaseOrderHeadersV2?$filter=PurchaseOrderNumber eq 'PO12345' and dataAreaId eq 'USMF'&Cross-company=true

This URL filters the PurchaseOrderHeadersV2 entity to only include records where PurchaseOrderNumber is 'PO12345' and dataAreaId is 'USMF', and includes data from all companies.

Using $top

The $top query option specifies the maximum number of records to return in the response. It can be combined with other query options like $filter and $orderby. Below is an example:

{{resource}}/data/VendPackingSlipTransBiEntities?$top=2&$filter=OrigPurchid eq 'PO12345' and dataAreaId eq 'USMF'&Cross-company=true

This URL returns the top 2 records from the VendPackingSlipTransBiEntities entity where OrigPurchid is 'PO12345' and dataAreaId is 'USMF', ordered by DeliveryDate in descending order, and includes data from all companies.

Using $select

The $select query option allows you to specify which properties to include in the response. This can help reduce the amount of data returned and improve performance. Below is an example:

{{resource}}/data/VendPackingSlipTransBiEntities?$select=dataAreaId,OrigPurchid,PackingSlipId&$top=2&$filter=OrigPurchid eq 'PO12345' and dataAreaId eq 'USMF'&Cross-company=true

This URL returns only the dataAreaId, OrigPurchid, and PackingSlipId properties from the VendPackingSlipTransBiEntities entity where OrigPurchid is 'PO12345' and dataAreaId is 'USMF', and includes data from all companies.

Using $orderby

The $orderby query option allows you to sort the data returned by the OData service based on specified properties. Below is an example:

{{resource}}/data/VendPackingSlipTransBiEntities?$top=2&$filter=OrigPurchid eq 'PO12345' and dataAreaId eq 'USMF'&$orderby=DeliveryDate desc&Cross-company=true

This URL returns the top 2 records from the VendPackingSlipTransBiEntities entity where OrigPurchid is 'PO12345' and dataAreaId is 'USMF', ordered by DeliveryDate in descending order, and includes data from all companies.

Conclusion

Using OData URLs to query data in D365FO is a powerful and flexible method to retrieve the exact records you need. The $filter, $top, cross-company=true, $select, and $orderby query options allow you to refine your queries and optimize data retrieval. By combining these options, you can efficiently manage and retrieve data to meet your specific requirements.