Dynamics 365 Business Central: How to use filter expressions in API related resource ($expand)

Dynamics 365 Business Central

Hi, Readers.
Today I would like to discuss a question I was asked recently, how to use filter expressions in API related resource ($expand).

With Business Central you can create Connect apps. Connect apps establish a point-to-point connection between Dynamics 365 Business Central and a 3rd party solution or service and is typically created using standard REST API to interchange data. More details: Welcome to the API (v2.0) for Business Central
For example, salesOrder resource type (standard API): Represents a sales order in Business Central.


In this standard API, we can use some navigation, which can expand the next layer of data.

NavigationReturn TypeDescription
customercustomerGets the customer of the salesOrder.
dimensionValuedimensionValueGets the dimensionvalue of the salesOrder.
currencycurrencyGets the currency of the salesOrder.
paymentTermpaymentTermGets the paymentterm of the salesOrder.
shipmentMethodshipmentMethodGets the shipmentmethod of the salesOrder.
dimensionSetLinesdimensionSetLinesGets the dimensionsetlines of the salesOrder.
salesOrderLinessalesOrderLinesGets the salesorderlines of the salesOrder.
attachmentsattachmentsGets the attachments of the salesOrder.
documentAttachmentsdocumentAttachmentsGets the documentattachments of the salesOrder.

$expand: Retrieves related resources.

Let’s see two examples.




And we can use $filter to filter results (rows): Please note that the single quotation marks below may be in Japanese font, please switch to English font when using. More details: Microsoft REST API guidelines

Comparison operators  
eq Equal?$filter=category eq ‘Expense’
ne Not equal?$filter=unitPrice ne 0
gtGreater than?$filter=unitPrice gt 1000
geGreater than or equal?$filter=unitPrice ge 1000
ltLess than?$filter=unitPrice lt 1000
leLess than or equal?$filter=unitPrice le 1000
Logical operators  
andLogical and?$filter=number ge ‘50000’ and number lt ‘60000’
orLogical or?$filter=category eq ‘Expense’ or category eq ‘Income’
notLogical negationNot supported
()Precendence grouping?$filter=(category eq ‘Expense’ or category eq ‘Income’) and (number ge ‘40000’ and number lt ‘50000’)
Comparison operators  
containsSearch for substring?$filter=contains(displayName, ‘red’)
endswithTest if first string ends with second string?$filter=endswith(email,’contoso.com’)
startswithTest if first string starts with second string?$filter=startswith(email,’aj’)
concatReturns a string that appends the second paramter to the firstNot supported

For example,

https://api.businesscentral.dynamics.com/v2.0/c8558e71-6ad8-4fcd-9f29-3ca165b41bbe/Sandbox240/api/v2.0/companies(37052e92-60e6-ee11-a200-6045bdac9711)/salesOrders?$filter=customerNumber eq '40000'&$expand=salesOrderLines

PS: Use ‘&‘ to use multiple methods at the same time

https://api.businesscentral.dynamics.com/v2.0/c8558e71-6ad8-4fcd-9f29-3ca165b41bbe/Sandbox240/api/v2.0/companies(37052e92-60e6-ee11-a200-6045bdac9711)/salesOrders?$orderby=customerNumber desc&$select=number,postingDate,customerNumber,customerName&$expand=salesOrderLines

More details: Standard API for Dynamics 365 Business Central SaaS (Get, Post, Patch, Delete and Basic query parameters – filter, orderby, select…)

So can the filter expressions be used in API related resource ($expand)? Yes, of cource.
But if you add filter expressions directly, the following error will be prompted.

    “error”: {
        “code”: “BadRequest”,
        “message”: “Term ‘salesOrderLines$filter=lineType eq ‘Account” is not valid in a $select or $expand expression.  CorrelationId:  43b92d5e-de1c-4d16-8105-84b6a52436be.”

And if you use ‘&‘, the system will recognize that this is a header field and will prompt the following error.

    “error”: {
        “code”: “BadRequest”,
        “message”: “Could not find a property named ‘lineType’ on type ‘Microsoft.NAV.salesOrder’.  CorrelationId:  25218cfc-4377-4e07-9261-d8a831a09da7.”

So what should we do? It’s really simple, we just need to add a bracket “()“. For example:

https://api.businesscentral.dynamics.com/v2.0/c8558e71-6ad8-4fcd-9f29-3ca165b41bbe/Sandbox240/api/v2.0/companies(37052e92-60e6-ee11-a200-6045bdac9711)/salesOrders?$expand=salesOrderLines($filter=lineType eq 'Account')

Give it a try!!!😁

1. Using filter expressions in OData URIs

2. Using filters with OData/API calls

3. Standard API for Dynamics 365 Business Central SaaS (Get, Post, Patch, Delete and Basic query parameters – filter, orderby, select…)


Hope this will help.

Thanks for reading.



Copied title and URL