Skip to main content

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

Comments

Popular posts from this blog

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

Error After Restore SQL Backup of NAV 2013 Database

Hi all, we are facing a conman issue during restoring a SQL Database backup restored for NAV 2013. While Trying to run object from Object Designer we get below listed error (even the service is configured properly) - --------------------------- Microsoft Dynamics NAV Development Environment --------------------------- There are no NAV Server instances available for this database. You must ensure that a NAV Server instance is running and is configured to use the database before you perform this activity. --------------------------- OK    ---------------------------

Future - What Next After NAV 2016. Code Name - Madeira...

Hi All, While Browsing web I came across some article which says about next release of Microsoft Dynamics NAV. Let's see the History of Code Names Given to Previous Version  - NAV 2013 - Code Name 'Sicily' NAV 2015 - Code Name 'CRETE' NAV 2016 - Code Name 'CORFU' And as we all know that all these are the Islands.