NAV 2013 & NAV 2013 R2 - How to - Using Filter Expressions in OData URIs.

Hi all,

In Previous post related to ODATA we saw how to publish and consume a Odata Web Services from NAV 2013 & 2013 R2.

Below are some link Related to Old Articles Published for Odata -
NAV RTC - Introduction to Web Services.
Introduction of ODATA Services In NAV 2013.
What is the ODATA?
Max. Page Size in ODATA Parameter.
Consuming Output of ODATA - Part 1
How to Use OData to Return OR Obtain a Service Metadata (EDMX) Document
How to - Use OData to Return OR Obtain a JSON Document

Today we will see how to Using Filter Expressions in OData URIs.

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



Rules for Filter Expressions

1. String values must be delimited by single quotation marks.
2. Numeric values require no delimiters.
3. DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.

Below are sample Filter Expression Commanly used. The Examples are based on Customer Page (Published as NAVCustomers) & General Ledger Entry (published as GLEntry).

Filter Type - Select a range of values
URL Addition on Base - $filter=Entry_No gt 610 and Entry_No lt 615
Expression Result - Query on GLEntry service. Returns entry numbers 611 through 614.

Filter Type - And
URL Addition on Base - $filter=Country_Region_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS'
Expression Result - Query on NAVCustomers service. Returns customers in Spain where Payment_Terms_Code=14 DAYS.

Filter Type - OR
URL Addition on Base - $filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US'
Expression Result - Query on Customer service. Returns customers in Spain and the United States.

Filter Type - Less Than
URL Addition on Base - $filter=Entry_No lt 610
Expression Result - Query on GLEntry service. Returns entry numbers that are less than 610.

Filter Type - Greater Than
URL Addition on Base - $filter= Entry_No gt 610
Expression Result - Query on GLEntry service. Returns entry numbers 611 and higher.

Filter Type - Greater than or equal to
URL Addition on Base - $filter=Entry_No ge 610
Expression Result - Query on GLEntry service. Returns entry numbers 610 and higher.

Filter Type - Less than or equal to
URL Addition on Base - $filter=Entry_No le 610
Expression Result - Query on GLEntry service. Returns entry numbers up to and including 610.

Filter Type - Different from (not equal)
URL Addition on Base - $filter=VAT_Bus_Posting_Group ne 'EXPORT'
Expression Result - Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.

Filter Type - endswith
URL Addition on Base - $filter=endswith(VAT_Bus_Posting_Group,'RT')
Expression Result - Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.

Filter Type - startswith
URL Addition on Base - $filter=startswith(Name, 'S')
Expression Result - Query on Customer service. Returns all customers names beginning with “S”.

Filter Type - substringof
URL Addition on Base - $filter=substringof(‘urn’, Name)
Expression Result - Query on Customer service. Returns customer records for customers with names containing the string “urn”.

Filter Type - length
URL Addition on Base - $filter=length(Name) gt 20
Expression Result - Query on Customer service. Returns customer records for customers with names longer than 20 characters.

Filter Type - indexof
URL Addition on Base - $filter=indexof(Location_Code, ‘BLUE’) eq 0
Expression Result - Query on Customer service. Returns customer records for customers having a location code beginning with the string BLUE.

Date & Time Related -

Filter Type - day
URL Addition on Base - $filter=day(FDateTime) eq 12

Filter Type - month
URL Addition on Base - $filter=month(FDateTime) eq 12

Filter Type - year
URL Addition on Base - $filter=year(FDateTime) eq 2010

Filter Type - hour
URL Addition on Base - $filter=hour(FDateTime) eq 1

Filter Type - minute
URL Addition on Base - $filter=minute(FDateTime) eq 32

Filter Type - second
URL Addition on Base - $filter=second(FDateTime) eq 0

I needed it and thought of sharing so that anyone else can use it, if required.

Regards,
Saurav Dhyani
www.sauravdhyani.com