$filter Parameter

The interworks.cloud Platform API provides filtering options on all basic methods that return a collection of resources, using the $filter parameter.

Filtering enables targeting only items that are important for our implementation, using certain criteria to fetch them.

Getting a collection of resources (e.g. /api/accounts, /api/orders, /api/subscriptions, etc) usually return a large number of items. Fetching only the important items is crucial for the efficiency of any implementation that needs to take into consideration the bandwidth and processing power utilization.

$filter parameter accepts an expression that is evaluated to fetch only the resources matching the provided criteria. The language used for this expression is based on OASIS OData Version 4.01 Protocol.

The following table contains some common use cases of the $filter parameter:

Query DescriptionExample
Select accounts with code equal to ‘ICP1’/api/accounts?$filter=code eq ‘ICP1’
Select accounts that ‘test’ word is part of their name/api/accounts?$filter=contains(name, ‘test’)
Select accounts that are enabled for reselling/api/accounts?$filter=resellingEnabled eq true
Select orders that their payment method is Paypal/api/orders?$filter=paymentMethod eq ‘Paypal’
Select invoices created after 01/01/2020/api/invoices?$filter=createdAt gt datetime’2020-01-01′

Data Τypes

Please note that you must use the appropriate notation for different data types with filter expressions.

Value TypeNotationExample
Stringdelimited by single quotation marks$filter=name eq ‘john’
Numericno delimiters$filter=quantity gt 5
DateTimedelimited by single quotation marks with template yyyy-MM-dd. Time part is optional and it is using the default timestamp when omitted (T00:00:00.000). If needed it can be queried using the following template yyyy-MM-ddThh-mm-ss.fff$filter=createdAt gt datetime’2020-12-25′ $filter=createdAt gt datetime’2020-12-25T20:05:08.000′
Booleanno delimiters$filter=IsActive eq true or $filter=IsActive eq false

Operators

The set of operators, currently supported by the interworks.cloud Platform API filtering options, are described in the following table:

You can apply advanced filtering options by aggregating together all your conditions using a boolean expression with AND and OR operators.

OperatorDescriptionExample
Logical Operators
andLogical and/Products?$filter=Price le 200 and Price gt 3.5
orLogical or/Products?$filter=Price le 3.5 or Price gt 200
Comparison Operators
eqEqual/api/accounts?$filter=name eq ‘John’
neNot equal/api/accounts?$filter=country ne ‘Greece’
gtGreater than/api/invoices?$filter=Price gt 20
geGreater than or equal/Products?$filter=Price ge 10
ltLess than/Products?$filter=Price lt 20
leLess than or equal/Products?$filter=Price le 100
Arithmetic Operators
addAddition/Products?$filter=Price add 5 gt 10
subSubtraction/Products?$filter=Price sub 5 gt 10
mulMultiplication/Products?$filter=Price mul 2 gt 2000
divDivision/Products?$filter=Price div 2 gt 4
modModulo/Products?$filter=Price mod 2 eq 0
Grouping Operators
( )Precedence grouping/Products?$filter=(Price sub 5) gt 10

Built-In Functions

The set of built-in functions, currently supported by the interworks.cloud Platform API filtering options, are described in the following table.

FunctionExample
String Functions
bool contains(string property, string searchText)/api/accounts?$filter=contains(name,’Ltd.’)
bool endswith(string property, string suffixText)/api/accounts?$filter=endswith(name,’Ltd.’)
bool startswith(string property, string prefixText)/api/products?$filter=startswith(name,’Office 365′)
int length(string property)/api/products?$filter=length(name) eq 20
int indexof(string property, string searchText)/api/products?$filter=indexof(name,’Dynamics’) eq 15
string replace(string property, string targetText, string replaceText)/api/products?$filter=replace(name,’Dynamics’, ‘’) eq ‘Office 365’
string toLower(string property)/api/products?$filter=toLower(name) eq ‘office 365 dynamics’
string toUpper(string property)/api/products?$filter=toUpper(name) eq ‘OFFICE 365 DYNAMICS’
string trim(string property)/api/products?$filter=trim(name) eq ‘Office365Dynamics’
string substring(string property, int start)/api/products?$filter=substring(name,1) eq ‘ffice365Dynamics’
string substring(string property, int start, int length)/api/products?$filter=substring(name,7,3) eq ‘365’
bool substringof(string searchText, string property)/api/products?$filter=substringof(‘Office 365’,name)
string concat(string propertyOrText, string propertyOrText)/api/products?$filter=concat(concat(name, ‘,’), code) eq ‘Office365 Dynamics,Dynamics365’
Date Functions
int day(DateTime datetimeValue)day(BirthDate) eq 8
int hour(DateTime datetimeValue)hour(BirthDate) eq 1
int minute(DateTime datetimeValue)minute(BirthDate) eq 0
int month(DateTime datetimeValue)month(BirthDate) eq 12
int second(DateTime datetimeValue)second(BirthDate) eq 0
int year(DateTime datetimeValue)year(BirthDate) eq 1948
Math Functions
double round(double doubleValue)round(Freight) eq 32
decimal round(decimal decimalValue)round(Freight) eq 32
double floor(double doubleValue)floor(Freight) eq 32
decimal floor(decimal datetimeValue)floor(Freight) eq 32
double ceiling(double doubleValue)ceiling(Freight) eq 33
decimal ceiling(decimal datetimeValue)ceiling(Freight) eq 33

Please refer to the page Search Criteria for a list of $filter parameters.