Using Filter Expressions in OData URIs
You use filter expressions in OData URIs to limit the results in a returned document. This article lists the filter expressions, and describes the equivalent field or table filter in AL. It provides examples of syntax for using filter expressions in OData URIs and applications.
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 Miami:
https://localhost:7048/BC130/OData/Company('CRONUS International Ltd.')/Customer?$filter=City eq 'Miami'
The following table shows the filters that are supported in Business Central OData web services and the equivalent 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).
Filters that do not have equivalent AL expressions might take longer to process compared to filters that do have equivalent AL expressions. The reason is that filters that do not have equivalent AL expressions are processed on the Business Central Server tier, while filters that do have equivalent AL expressions are processed on the Business Central database tier.
|Definition||Example and explanation||Equivalent AL expression|
|Select a range of values||
Query on GLEntry service. Returns entry numbers 611 through 614.
Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.
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.
Query on GLEntry service. Returns entry numbers that are less than 610.
Query on GLEntry service. Returns entry numbers 611 and higher.
|Greater than or equal to||
Query on GLEntry service. Returns entry numbers 610 and higher.
|Less than or equal to||
Query on GLEntry service. Returns entry numbers up to and including 610.
|Different from (not equal)||
Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.
Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.
Query on Customer service. Returns all customers names beginning with “S”.
Query on Customer service. Returns customer records for customers with names containing the string “urn”.
Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.
Query on Customer service. Returns true for customers with the string RED in their location code starting as position 5.
Referencing Different Data Types in Filter Expressions
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.
Nested function calls
Nested function calls in filter clauses are not supported in the current OData implementation. This means that filter clause expressions like
contains(tolower(field), 'some') do not return the expected results - in this case a partial case-insensitive text search - but will instead either throw an error or return an undefined result.