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 Miami:

  Copy Code 
http://localhost:7048/DynamicsNAV70/OData/Company('CRONUS International Ltd.')/Customer?$filter=City eq 'Miami'

The following table shows the filters that are supported in Microsoft Dynamics NAV 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).

Note
Filters that do not have equivalent C/AL expressions might take longer to process compared to filters that do have equivalent C/AL expressions. The reason is that filters that do not have equivalent C/AL expressions are processed on the Microsoft Dynamics NAV Server tier, while filters that do have equivalent C/AL expressions are processed on the Microsoft Dynamics NAV database tier.

Definition Example and explanation Equivalent C/AL expression

Select a range of values
  Copy Code 
filter=Entry_No gt 610 and Entry_No lt 615

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

..

And
  Copy Code 
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
  Copy Code 
filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US'

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

Note
An Or filter between different fields is supported. However, processing time may increase because part of the processing is performed on the Microsoft Dynamics NAV Server tier.

|

Less than
  Copy Code 
filter=Entry_No lt 610

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

<

Greater than
  Copy Code 
filter= Entry_No gt 610

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

>

Greater than or equal to
  Copy Code 
filter=Entry_No ge 610

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

>=

Less than or equal to
  Copy Code 
filter=Entry_No le 610

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

<=

Different from (not equal)
  Copy Code 
filter=VAT_Bus_Posting_Group ne 'EXPORT'

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

<>

endswith
  Copy Code 
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
  Copy Code 
filter=startswith(Name, 'S')

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

substringof
  Copy Code 
filter=substringof(‘urn’, Name)

Query on Customer service. Returns customer records for customers with names containing the string “urn”. [Need working example.]

length
  Copy Code 
filter=length(Name) gt 20

Query on Customer service. Returns customer records for customers with names longer than 20 characters.

indexof
  Copy Code 
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
  Copy Code 
filter=replace(City, 'Miami', 'Tampa') eq 'CODERED'

substring
  Copy Code 
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
  Copy Code 
filter=tolower(Location_Code) eq 'code red'

toupper
  Copy Code 
filter=toupper(FText) eq '2ND ROW'

trim
  Copy Code 
filter=trim(FCode) eq 'CODE RED'

concat
  Copy Code 
filter=concat(concat(FText, ', '), FCode) eq '2nd row, CODE RED'

day
  Copy Code 
filter=day(FDateTime) eq 12

month
  Copy Code 
filter=month(FDateTime) eq 12

year
  Copy Code 
filter=year(FDateTime) eq 2010

hour
  Copy Code 
filter=hour(FDateTime) eq 1

minute
  Copy Code 
filter=minute(FDateTime) eq 32

second
  Copy Code 
filter=second(FDateTime) eq 0

round
  Copy Code 
filter=round(FDecimal) eq 1

floor
  Copy Code 
filter=floor(FDecimal) eq 0

ceiling
  Copy Code 
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.
  • DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.

For more information about data types and other information about conventions and standards for OData URIs, see Atom Publishing Protocol: URI Conventions. Conventions for data types are addressed in section 2.2.2, "Abstract Type System."