OData filter function

Follow

Odata filter function

It is possible to have Odata return only selected data via filter For example, a debtor 
https://odata.uniconta.com/odata/DebtorClient?$filter=Account eq '1101798'
  E.g. debit transactions in date range 
https://odata.uniconta.com/odata/DebtorTransClient?$filter=Date ge datetime'2021-01-01T00:00:00' and Date le datetime'2021-12-31T00:00:00'
  E.g. Finance in date range 
https://odata.uniconta.com/odata/DebtorTransClient?$filter=Date ge datetime'2021-01-01T00:00:00' and Date le datetime'2021-12-31T00:00:00'

https://odata.uniconta.com/odata/GLTransClient?$filter=Date ge datetime'2021-01-01T00:00:00' and Date le datetime'2021-12-31T00:00:00' 


https://odata.uniconta.com/odata/GLTransSumClient?$filter=Date ge datetime'2021-01-01T00:00:00' and Date le datetime'2021-12-31T00:00:00' 


https://odata.uniconta.com/odata/GLBudgetLineClient?$filter=Date ge datetime'2021-01-01T00:00:00' and Date le datetime'2021-12-31T00:00:00'
  Example of Filters in PowerQuery "Advanced Editor". The underline shows the relationship between Line 2 and Line 3 
let
Source = OData.Feed("https://odata.uniconta.com/odata/GLTransSumClient?$filter=Date ge datetime'2021-01-01T00:00:00'", null, [Implementation="2.0"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Account] = "1012"))
in
#"Filtered Rows"
   
Definition Example and explanation Equivalent C/AL expression
Select a range of values filter=Entry_No gt 610 and Entry_No lt 615 Query on GLEntry service. Returns entry numbers 611 through 614. ..
And 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. &
Gold filter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US' Query on Customer service. Returns customers in Spain and the United States. Important 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. |
Less than filter=Entry_No lt 610 Query on GLEntry service. Returns entry numbers that are less than 610. <
Greater than filter= Entry_No gt 610 Query on GLEntry service. Returns entry numbers 611 and higher. >
Greater than or equal to filter=Entry_No ge 610 Query on GLEntry service. Returns entry numbers 610 and higher. >=
Less than or equal to filter=Entry_No on 610 Query on GLEntry service. Returns entry numbers up to and including 610. <=
Different from (not equal) filter=VAT_Bus_Posting_Group ne 'EXPORT' Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT. <>
endswith 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 filter=startswith(Name, 'S') Query on Customer service. Returns all customers names beginning with "S".
substringof filter=substringof(Name, 'urn') Query on Customer service. Returns customer records for customers with names containing the string "urn".
length filter=length(Name) gt 20 Query on Customer service. Returns customer records for customers with names longer than 20 characters.
index of 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 filter=replace(City, 'Miami', 'Tampa') eq 'CODERED'
subring 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 filter=tolower(Location_Code) eq 'code red'
toupper filter=toupper(FText) eq '2ND ROW'
trim filter=trim(FCode) eq 'CODE RED'
concat filter=concat(concat(FText, ', '), FCode) eq '2nd row, CODE RED'
day filter=day(FDateTime) eq 12
month filter=month(FDateTime) eq 12
year filter=year(FDateTime) eq 2010
hour filter=hour(FDateTime) eq 1
minute filter=minute(FDateTime) eq 32
second filter=second(FDateTime) eq 0
round filter=round(FDecimal) eq 1
floor filter=floor(FDecimal) eq 0
ceiling 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'2021-01-01T00:00:00:00'.   You can break your queries and select a range of items not overlapping. For example, here for transactions you must find out how many intervals you need and then you do not need to worry about that you have many transactions. Item <= '02': 
https://odata.uniconta.com/odata/InvTransClient?$filter=Item le '02'
  Item > '02' and <= '05': 
https://odata.uniconta.com/odata/InvTransClient?$filter=Item gt '02' and Item le '05'
  Item > '05' and <= '11': 
https://odata.uniconta.com/odata/InvTransClient?$filter=Item gt '05' and Item le '11'
  Item > '11': 
https://odata.uniconta.com/odata/InvTransClient?$filter=Item gt '11'

Related articles