OData v4 Support
OData defines a system of querystring options that can help construct complicated queries to get the resource you want. As an architecture that's built on top of the current features of the Web, ITAS APIs support OData v4 query strings. OData (Open Data Protocol) is an OASIS Standard that defines best practice for building and consuming RESTful APIs.
When querying a collection (array or list) of, for example Nominal Accounts, the collection is specified though a URL. Query operations such as filter, sort, paging and projection can be specified as querystring options. The names of all system query options are prefixed with a dollar ($) character. The Querystring is proceeded with a question mark (?) and each separated with an ampersand (&) sign.
For example, to page a result set the $skip and $top querystring parameters could be included as follows:
| /ReferenceData/QA/NominalLedger/NominalAccount/?$skip=50&$top=20 |
The request returns items 51 to 70 in the list of nominal accounts.
Odata Parameters
The full list of querystring parameters are:
| Parameter | Purpose | Example |
| $filter* | The filter system query option restricts the set of items returned. | Return all NominalAccounts whose DetailDescription does not contain the word ‘HEADER’ $filter=substringof(‘HEADER’, DeatilDescription) eq false |
| $orderby | Specifies an expression for determining what values are used to order the returned collection. | OrderBy values in the DetailDescription field in descending order. $orderby=DetailDescription desc |
| $skip | Returns a subset of entries (starting with Entry N+1). | Return items from entity 51 onwards. $skip=50 |
| $top | Returns only the first N items of the set | Return the first 20 $top=20 |
| $select** | Specifies a subset of properties to return | Return Properties NominalAccountId, BankAccount, PrivilegeLevel and Status $select=NominalAccountId, BankAccount, PrivilegeLevel, Status |
* See below for more details on this very powerful filter.
** Available from API v2.17
The real power of these filters come when combining the above parameters. Note that combinations are separated with the & character. For example: return all NominalAccounts who's DetailDescription does not contain 'HEADER', in descending order and only return items 301 to 400.
| /ReferenceData/QA/NominalLedger/NominalAccount/?$filter=contains('HEADER', DetailDescription) eq false&$orderby=DetailDescription desc&$skip=300&$top=100 |
The $filter Parameter
The URI with a $filter query option identifies a subset of the Entries from the collection of entries identified by the Resource Path section of the URI. The subset is determined by selecting only the entries that satisfy the predicate expression specified by the query option.
The expression language that is used in $filter operators supports references to properties and literals. The literal values can be strings enclosed in single quotes, numbers and Boolean values (true or false) or any of the additional literal representations shown in the Abstract Type System section.
The operators supported in the expression language are shown in the following table.
| Operator | Description | Example |
| Local Operators | ||
| Eq | Equal | /?$filter=TraderCode eq ‘JT’ |
| Ne | Not equal | /?$filter=TraderCode ne ‘JT’ |
| Gt | Greater than | /?$filter=PrivilegeLevel gt 5 |
| Ge | Greater than or equal | /?$filter=PrivilegeLevel ge 6 |
| Lt | Less than | /?$filter=PrivilegeLevel lt 9 |
| Le | Less than or equal | /?$filter=PrivilegeLevel le 8 |
| And | Logical and | /?$filter=PrivilegeLevel le 9 and PrivilegeLevel gt 5 |
| Or | Logical or | /?$filter=PrivilegeLevel le 4 or PrivilegeLevel gt 8 |
| Not | Logical negation | /?$filter=not endswith(ClientAccountId, ‘P’) |
| 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 |
In addition to operators, a set of functions are also defined for use with the filter query string operator. The following table lists the available functions. NOTE: ISNULL and COALESCE operators are not defined. Instead, there is a null literal which can be used in comparisons.
| Function | Example |
| String Functions | |
| bool contains(string po, string p1) | /Customers?$filter=contains('Alfreds', CompanyName) eq true |
| string p0 bool(string p1, string p2) | /Customers?$filter=CompanyName in('Alfreds', 'Freds') |
| bool endswith(string p0, string p1) | /Customers?$filter=endswith(CompanyName, 'Futterkiste') eq true |
| bool startswith(string p0, string p1) | /Customers?$filter=startswith(CompanyName, 'Alfr') eq true |
| int length(string p0) | /Customers?$filter=length(CompanyName) eq 19 |
| int indexof(string p0, string p1) | /Customers?$filter=indexof(CompanyName, 'lfreds') eq 1 |
| string replace(string p0, string find, string replace) | /Customers?$filter=replace(CompanyName, ' ', '') eq 'AlfredsFutterkiste' |
| string substring(string p0, int pos) | /Customers?$filter=substring(CompanyName, 1) eq 'lfreds Futterkiste' |
| string substring(string p0, int pos, int length) | /Customers?$filter=substring(CompanyName, 1, 2) eq 'lf' |
| string tolower(string p0) | /Customers?$filter=tolower(CompanyName) eq 'alfreds futterkiste' |
| string toupper(string p0) | /Customers?$filter=toupper(CompanyName) eq 'ALFREDS FUTTERKISTE' |
| string trim(string p0) | /Customers?$filter=trim(CompanyName) eq 'Alfreds Futterkiste' |
| string concat(string p0, string p1) | /Customers?$filter=concat(concat(City, ', '), Country) eq 'Berlin, Germany' |
| Date Functions | |
| DateTime | /Employees?$filter=BirthDate eq 1977-01-27T00:00:00Z |
| Date | /Employees?$filter=Date(BirthDate) eq 1977-01-27 |
| int day(DateTime p0) | /Employees?$filter=day(BirthDate) eq 8 |
| int hour(DateTime p0) | /Employees?$filter=hour(BirthDate) eq 0 |
| int minute(DateTime p0) | /Employees?$filter=minute(BirthDate) eq 0 |
| int month(DateTime p0) | /Employees?$filter=month(BirthDate) eq 12 |
| int second(DateTime p0) | /Employees?$filter=second(BirthDate) eq 0 |
| int year(DateTime p0) | /Employees?$filter=year(BirthDate) eq 1948 |
| Math Functions | |
| double round(double p0) | /Orders?$filter=round(Freight) eq 32d |
| decimal round(decimal p0) | /Orders?$filter=round(Freight) eq 32 |
| double floor(double p0) | /Orders?$filter=round(Freight) eq 32d |
| decimal floor(decimal p0) | /Orders?$filter=floor(Freight) eq 32 |
| double ceiling(double p0) | /Orders?$filter=ceiling(Freight) eq 33d |
| decimal ceiling(decimal p0) | /Orders?$filter=floor(Freight) eq 33 |
| Type Functions | |
| bool IsOf(type p0) | /Orders?$filter=isof('NorthwindModel.Order') |
| bool IsOf(expression p0, type p1) | /Orders?$filter=isof(ShipCountry, 'Edm.String') |