How many times have you wished you could do something like this,
GET ~/api/domain/odata/Crm_Customers?$filter=contains(Party,'John Doe')
Or in other words- to find all customers, whose party (name) contains "John Doe" (as in the example).
But it all ends with the following error:
No function signature for the function with name 'contains' matches the specified arguments.
To achieve this it was necessary to either specify the specific attribute behind the reference. E.g.,
GET ~/api/domain/odata/Crm_Customers?$filter=contains(Party/PartyName,'John Doe')
OK, that's kind of a solution, but what if the reference isn't filterable? E.g., if you want to filter by CustomerType or DefaultPriceList, SalesPerson...
Nothing. There's is no direct way to do it other than making several requests:
GET ~/api/domain/odata/Crm_CustomerTypes?$filter=contains(Name,'TypeX')&$select=Id
GET ~/api/domain/odata/Crm_Customers?$filter=CustomerType eq 'Crm_CustomerTypes(0df4841f-97e2-e011-a64d-00155d00050d)'
But that's no more!
Filter by reference
Starting with v.2023 you can filter by an entity reference. The customer's party as in the example above.
Now a request like this one,
GET ~/api/domain/odata/Crm_Customers?$filter=contains(CustomerType,'TypeX')
is perfectly fine.
Thanks to this new improvement, use cases where you need to filter an entity by one or more of its references, are now greatly simplified.
Actually, when you filter by reference, behind the scenes you're filtering by the display text attribute of the corresponding entity.
Filter by enum attribute
Another improvement is that filtering by enum type is now also possible.
Here's an example of how you can filter out all sales orders that have a status "plan".
GET ~/api/domain/odata/Crm_Sales_SalesOrders?$filter=contains(cast(State, Edm.String),'plan')
Why "plan"?
This is not a typo. It will return all documents that have both Planned and FirmPlanned state.
The only requirement is that you have to explicitly cast the enum attribute as Edm.String.
Real-life example
ERP.net web client actively uses filtering by reference in all navigators.
Here's an example
The picture above shows all customers filtered by their DefaultPriceList containing "online".
And here's the corresponding URL in the browser,
https://demodb.my.erp.net/cl/forms/Crm_Customers?$filter=contains(DefaultPriceList,'online')
Does it look familiar?
---
More information is available in our official documentation:
https://docs.erp.net/dev/domain-api/query-options/filter.html
https://docs.erp.net/tech/advanced/data-objects/display-format.html
https://support.erp.net/hc/en-us/articles/5443711124636
0 Comments