Table of Contents
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 Description | Example |
|---|---|
| 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 Type | Notation | Example |
|---|---|---|
| String | delimited by single quotation marks | $filter=name eq ‘john’ |
| Numeric | no delimiters | $filter=quantity gt 5 |
| DateTime | delimited 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′ |
| Boolean | no 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.
| Operator | Description | Example |
|---|---|---|
| Logical Operators | ||
| and | Logical and | /Products?$filter=Price le 200 and Price gt 3.5 |
| or | Logical or | /Products?$filter=Price le 3.5 or Price gt 200 |
| Comparison Operators | ||
| eq | Equal | /api/accounts?$filter=name eq ‘John’ |
| ne | Not equal | /api/accounts?$filter=country ne ‘Greece’ |
| gt | Greater than | /api/invoices?$filter=Price gt 20 |
| ge | Greater than or equal | /Products?$filter=Price ge 10 |
| lt | Less than | /Products?$filter=Price lt 20 |
| le | Less than or equal | /Products?$filter=Price le 100 |
| Arithmetic Operators | ||
| add | Addition | /Products?$filter=Price add 5 gt 10 |
| sub | Subtraction | /Products?$filter=Price sub 5 gt 10 |
| mul | Multiplication | /Products?$filter=Price mul 2 gt 2000 |
| div | Division | /Products?$filter=Price div 2 gt 4 |
| mod | Modulo | /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.
| Function | Example |
|---|---|
| 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.