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

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

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 - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.