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

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.
Endpoint:

https://api.businesscentral.dynamics.com/v2.0/c8558e71-6ad8-4fcd-9f29-3ca165b41bbe/Sandbox240/api/v2.0/companies(37052e92-60e6-ee11-a200-6045bdac9711)/salesOrders

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

Navigation	Return Type	Description
customer	customer	Gets the customer of the salesOrder.
dimensionValue	dimensionValue	Gets the dimensionvalue of the salesOrder.
currency	currency	Gets the currency of the salesOrder.
paymentTerm	paymentTerm	Gets the paymentterm of the salesOrder.
shipmentMethod	shipmentMethod	Gets the shipmentmethod of the salesOrder.
dimensionSetLines	dimensionSetLines	Gets the dimensionsetlines of the salesOrder.
salesOrderLines	salesOrderLines	Gets the salesorderlines of the salesOrder.
attachments	attachments	Gets the attachments of the salesOrder.
documentAttachments	documentAttachments	Gets the documentattachments of the salesOrder.

$expand: Retrieves related resources.

Let’s see two examples.
customer:

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

salesOrderLines:

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

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

Operator	Description	Example
Comparison operators
eq	Equal	?$filter=category eq ‘Expense’
ne	Not equal	?$filter=unitPrice ne 0
gt	Greater than	?$filter=unitPrice gt 1000
ge	Greater than or equal	?$filter=unitPrice ge 1000
lt	Less than	?$filter=unitPrice lt 1000
le	Less than or equal	?$filter=unitPrice le 1000
Logical operators
and	Logical and	?$filter=number ge ‘50000’ and number lt ‘60000’
or	Logical or	?$filter=category eq ‘Expense’ or category eq ‘Income’
not	Logical negation	Not supported
Grouping
()	Precendence grouping	?$filter=(category eq ‘Expense’ or category eq ‘Income’) and (number ge ‘40000’ and number lt ‘50000’)
Comparison operators
contains	Search for substring	?$filter=contains(displayName, ‘red’)
endswith	Test if first string ends with second string	?$filter=endswith(email,’contoso.com’)
startswith	Test if first string starts with second string	?$filter=startswith(email,’aj’)
concat	Returns a string that appends the second paramter to the first	Not 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!!!😁

PS:
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…)

END

Hope this will help.

Thanks for reading.

ZHU