We are trying to implement a Item search functionality using the Business Central API.
For example, the user may type "PLATE" in the search box, and we want to search by Item No. and Description; so we take this to the API with a request like this:
GET https://
SERVER:7048/BC210/api/v1.0/companies(5b9a4086-229b-ed11-b874-9a7fd23bdbb9)/items?$filter=number eq 'PLATE*' or displayName eq '*PLATE*'
However, this is not a valid request, as the service returns this error:
{
"error": {
"code": "BadRequest_MethodNotImplemented",
"message": "The 'OR' operator is not supported on distinct fields on an OData filter. CorrelationId: 00c5784c-da54-4622-b452-a85648391af9."
}
}
This is quite limiting, as we can't figure out a way to implement a powerful search engine, and in SaaS BC where we don't have a SQL database access.
We would like to avoid the approach of implementing event subscribers in the Item table, and be constantly updating data in a external database that we would use for our search engine, but that's the only path we have come up with so far.
I've noticed that list pages in BC have a search functionality that is able to search in all fields; wondering if there's a way to access this control through API
Another approach could be using multiple API request and combining them, but this could have a poor performance, as on top of multiple request, we would need to filter out duplicates
