OData Web Services – About Using Filter Expressions

OData Web Services – About Using Filter Expressions

I want to report some information on the use and properties of OData with Business Central, very useful and I would say indispensable if we use the Business Central Cloud environment.

The Open Data Protocol (OData) is a web protocol that is designed for querying tabular data and provides you with an alternative to SOAP-based web services.

OData builds on web technologies such as HTTP, the Atom Publishing Protocol (AtomPub), and JavaScript Object Notation (JSON) to provide access to information from different applications, services, and stores.

OData uses URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with resources.

SCENARIO

You can use OData web services to show Business Central data, and you can update data in a Business Central database using OData web services.

OData – Filter Expressions

Using Filter Expressions in OData URIs

You can use filter expressions in OData URIs to limit the results that are returned in an AtomPub document. This topic identifies the filter expressions that you can use, describes the equivalent field or table filter that you can use in C/AL, and presents examples to show the syntax for using filter expressions in OData web service URIs and applications.

Filter Expressions

To add a filter to an OData URI, add $filter= to the end of the name of the published web service.

For example, the following URI filters the City field in the Customer page to return all customers who are located in “Milano”:

Example: (Access with USER\SERVICE KEY)

Customer > Published Odata Service

https://api.businesscentral.dynamics.com/v2.0/tenant-ID/Production/ODataV4/Company(‘CompanyName’)/Customer?$filter=City%20eq%20%27Milano%27

Available Filters

The following table shows the filters that are supported in Business Central OData web services and the equivalent C/AL filter expressions.

All examples are based either on page 21, Customer (published as Customer), or on page 20, General Ledger Entry (published as GLEntry).

Filter Expressions
Definition Example and explanation Equivalent C/AL expression
Select a range of values filter=Entry_No gt 610 and Entry_No lt 615

Query on GLEntry service. Returns entry numbers 611 through 614.

..
And filter=Country_Region_Code eq ‘ES’ and Payment_Terms_Code eq ’14 DAYS’

Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.

&
Or filter= Country_Region_Code eq ‘ES’ or Country_Region_Code eq ‘US’

Query on Customer service. Returns customers in Spain and the United States.

Alert: You can use OR operators to apply different filters on the same field. However, you cannot use OR operators to apply filters on two different fields.

|
Less than filter=Entry_No lt 610

Query on GLEntry service. Returns entry numbers that are less than 610.

<
Greater than filter= Entry_No gt 610

Query on GLEntry service. Returns entry numbers 611 and higher.

>
Greater than or equal to filter=Entry_No ge 610

Query on GLEntry service. Returns entry numbers 610 and higher.

>=
Less than or equal to filter=Entry_No le 610

Query on GLEntry service. Returns entry numbers up to and including 610.

<=
Different from (not equal) filter=VAT_Bus_Posting_Group ne ‘EXPORT’

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.

<>
endswith filter=endswith(VAT_Bus_Posting_Group,’RT’)

Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.

*
startswith filter=startswith(Name, ‘S’)

Query on Customer service. Returns all customers names beginning with “S”.

substringof filter=substringof(Name, ‘urn’)

Query on Customer service. Returns customer records for customers with names containing the string “urn”.

indexof filter=indexof(Location_Code, ‘BLUE’) eq 0

Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.

replace filter=replace(City, ‘Miami’, ‘Tampa’) eq ‘CODERED’
substring filter=substring(Location_Code, 5) eq ‘RED’

Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.

tolower filter=tolower(Location_Code) eq ‘code red’
toupper filter=toupper(FText) eq ‘2ND ROW’
trim filter=trim(FCode) eq ‘CODE RED’
concat filter=concat(concat(FText, ‘, ‘), FCode) eq ‘2nd row, CODE RED’
round filter=round(FDecimal) eq 1
floor filter=floor(FDecimal) eq 0
ceiling filter=ceiling(FDecimal) eq 1

Referencing Different Data Types in Filter Expressions

You must use the appropriate notation for different data types with filter expressions.

  • String values must be delimited by single quotation marks.
  • Numeric values require no delimiters.

Known Limitations

Filters

You can specify filters in OData web services in general that are not supported in Business Central , such as using an OR operator to filter on “two different fields.

In those cases, you will see the following error:

“An error occurred while processing this request. The ‘OR’ operator is not supported on distinct fields on an OData filter”

Lambda operators

Lambda operators are not supported by Business Central OData APIs. If lambda operators are used, the filter expression will be ignored.

Deep insert

Business Central supports deep insert, but not deep patching. Multiple requests will need to be issued when patching nested entities.

Source https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/odata-web-services

4 thoughts on “OData Web Services – About Using Filter Expressions

  • 27 September 2020 at 2:23 PM
    Permalink

    Very shortly this website will be famous amid all blog users, due
    to it’s pleasant articles

    Reply
  • 26 February 2021 at 9:25 AM
    Permalink

    concat and more other function not supported with D365BC onCloud and onPrem
    {
    “error”: {
    “code”: “BadRequest_MethodNotImplemented”,
    “message”: “Client requests that contain \”concat\” filter options are not supported by Dynamics 365 Business Central OData web services. CorrelationId: f5b555ed-b193-4408-ba73-1ea5e2fd8d72.”
    }
    }

    Reply
  • 26 February 2021 at 9:59 AM
    Permalink

    D365BC onCloud and onPrem

    {
    “error”: {
    “code”: “BadRequest_MethodNotImplemented”,
    “message”: “Client requests that contain \”concat\” filter options are not supported by Dynamics 365 Business Central OData web services. CorrelationId: 9be73af4-4418-4456-a9ac-2e55aed45459.”
    }
    }

    Reply
  • 6 January 2022 at 11:06 PM
    Permalink

    Hi,

    Now that we know how to use query expressions in an Odata request, I’d like to examine the expression from within the Odata API page so I can do some other work with it. Is that possible? We’re using Odata v2.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.