OData query options with D365F&O (Part 1)

As we all know, OData provides a standardized way for applications like D365F&O to expose their data, making it easier for developers and IT pros to work with, integrate, and leverage the data for a wide range of purposes.

Though Odata querying is familiar to us, I will give a brief walkthrough of various CRUD operations with D365F&O.

FILTER and COUNT

Below query filters the Customer records having 90 CustomerGroup id along the count.

/data/CustomersV3?$filter= CustomerGroupId eq '90'&$count=true

Another example of filter

{{url}}/data/CustomerPostalAddresses?$filter=CustomerAccountNumber eq 'US-028'

{{url}}/data/CustomersV3?$filter= SalesCurrencyCode eq 'EUR'

Fetch the count of entire table from the default company

{{url}}/data/SalesInvoiceHeadersV2?$count=true

CROSS-COMPANY

Fetches the data from non-default company of user.

{{url}}/data/CustomerGroups?cross-company=true&$filter=dataAreaId eq 'usrt'

METADATA

Fetches the metadata of the Odata entities.

ENUMS

Syntax to pass the Enum values in the query. Below query fetches the data from SalesTable for Invoiced orders.

{{url}}/data/SalesOrderHeadersV2?$filter= SalesOrderStatus eq Microsoft.Dynamics.DataEntities.SalesStatus'Invoiced'&$count=true

TOP and ORDER BY

Fetches top 5 customers and orders by Country in descending order

{{url}}/data/CustomersV3?$top= 5&$orderby=AddressCountryRegionId desc

EXPAND

This option queries the related data from the tables. For instance, below syntax fetches corresponding Purchlines from the Purch Table.

D365FO supports only one level of Expansion. For instance , it is not possible to add InventDim to fetch the details of dimensions.

{{url}}/data/PurchaseOrderHeaders?$expand=PurchaseOrderLines&$top=1

{
    "@odata.context": "https://psandbox01.sandbox.operations.dynamics.com/data/$metadata#PurchaseOrderHeaders",
    "value": [
        {
            "@odata.etag": "W/\"JzAsMjI1NjU0MjMxODA7MCwwOzAsMjI1NjU0MjY5ODk7MCwwOzAsMCc=\"",
            "dataAreaId": "usmf",
            "PurchaseOrderNumber": "000002",
            "ExpectedStoreAvailableSalesDate": "1900-01-01T12:00:00Z",
            "VendorInvoiceDeclarationId": "",
            "OrderVendorAccountNumber": "US-102",            
            "TotalDiscountPercentage": 0,            
            "BuyerGroupId": "10",
            "DeliveryAddressCountryRegionISOCode": "US",
            "CashDiscountCode": "2%D30",
            "PaymentScheduleName": "",
            "IntrastatTransactionCode": "",
            "URL": "",
            "CurrencyCode": "USD",
            "ConfirmingPurchaseOrderCodeLanguageId": "",
            "InvoiceType": "Invoice",
            "ArePricesIncludingSalesTax": "No",
            "DeliveryAddressLocationId": "000001098",
            "GSTSelfBilledInvoiceApprovalNumber": "",
            "IsDeliveredDirectly": "No",
            "IntrastatStatisticsProcedureCode": "",
            "InvoiceVendorAccountNumber": "US-102",
            "DeliveryAddressStreet": "213 South Street, Gate 1",            
            "TotalDiscountVendorGroupCode": "03",
            "PurchaseOrderLines": [
                {
                    "@odata.etag": "W/\"JzAsNTYzNzE0NjA3NjswLDIyNTY1NDI2OTg5OzAsMDswLDA7MCw1NjM3MTQ0NTgxOzAsMDswLDAn\"",
                    "dataAreaId": "usmf",
                    "PurchaseOrderNumber": "000002",
                    "LineNumber": 1,
                    "ProcurementProductCategoryName": "",
                    "Tax1099SAddressOrLegalDescription": "",
                    "FixedAssetNumber": "",
                    "Tax1099GTaxYear": 0,
                    "ProjectSalesUnitSymbol": "",
                    "OrderedPurchaseQuantity": 2480,
                    "FormattedDelveryAddress": "213 South Street, Gate 1\nGalvin, WA 98544\nUSA",
                    "ProjectCategoryId": "",
                    "ItemNumber": "M0005",
                    "AccountingDistributionTemplateName": "",
                    "DeliveryAddressDescription": "Site 1"                   
                },
                {
                    "@odata.etag": "W/\"JzAsNTYzNzE0NjA3NzswLDIyNTY1NDI2OTg5OzAsMDswLDA7MCw1NjM3MTQ0NTgyOzAsMDswLDAn\"",
                    "dataAreaId": "usmf",
                    "PurchaseOrderNumber": "000002",
                    "LineNumber": 2,
                    "ProcurementProductCategoryName": "",
                    "Tax1099SAddressOrLegalDescription": "",
                    "FixedAssetNumber": "",
                    "Tax1099GTaxYear": 0,
                    "ProjectSalesUnitSymbol": "",
                    "OrderedPurchaseQuantity": 12,
                    "FormattedDelveryAddress": "213 South Street, Gate 1\nGalvin, WA 98544\nUSA",
                    "ProjectCategoryId": "",
                    "ItemNumber": "M0005",
                    "AccountingDistributionTemplateName": "",
                    "DeliveryAddressDescription": "Site 1"                    
                }                
            ]
        }
    ]
}

In my next post, I will cover on the Arithematic operations with Odata.