Uniconta | OData filter function

OData filter function

The OData filter function

It is possible to get OData to return selected data only by using a filter.

For example, for a debtor:

https://odata.uniconta.com/odata/DebtorClient?$filter=Account eq ‘1101798’

For example, for debtor transactions for a certain date range:

https://odata.uniconta.com/odata/DebtorTransClient?$filter=Date ge datetime’2016-01-01T00:00:00′ and Date le datetime’2016-12-31T00:00:00′

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.	&
Or	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 OR operators can be used to apply different filters on the same field. However, OR operators cannot be used 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 le 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.
indexof	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’
substring	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

The appropriate notation for different data types must be used 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’.

Go to:

Layout
Templates
Search

unipedia

OData filter function

The OData filter function