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')