Filtering and sorting

1. Sorting

It is sometimes required to sort the data in specific ways. This can be done by adding &$orderby to the resource path. The reserved keyword orderby takes up to 4 data field name as parameters that are separated with commas (,). Example is shown below:

my/resource?$orderby=lastname,firstname,age .

To specify ascending or descending order add asc for ascending order or desc for descending order for the field. Default ordering is always ascending ordering. NOTE: either all is ascending or descending the database does not support individual ordering of the fields.

Correct way: $orderby=lastname desc,firstname desc

Wrong way: $orderby=lastname, firstname desc

2. Filtering / searching

To add a filter to your resource simply add &$filter=[filter options] when browsing the resource. This will enable the filter where it will translate the filter options into a query used in the database. Functions and macros can be used in filters, these are described below.

2.1. Filter operators

eq equals Equality statement, asks for an equal match between the Data name (left hand side) and the argument (right hand side) in the database. Ex: a eq b
ne not equals Not equal statement, asks for a match that is not equal between the Data name(left hand side) and the argument(right hand side) in the database. Ex: a ne b
lt less than Less than statement, asks for a match where the Data name(left hand side) is less than the argument(right hand side) in the database. Ex: a lt b
le less or equal Less than or equal statement, asks for a match where the Data name(left hand side) is less than or equal to the argument(right hand side) in the database. Ex: a le b
gt greater than Greater than statement, asks for a match where the Data name(left hand side) is greater than the argument(right hand side) in the database. Ex: a gt b
ge greater or equal Greater than or equal statement, asks for a match where the Data name(left hand side) is greater than or equal to the argument(right hand side) in the database. Ex: a ge b

2.2. Filter functions

startswith(name,val) starts with Matches all fields with the name “name” that starts with the value in “val”.
endswith(name,val) ends with Matches all fields with the name “name” that ends with the value in “val”.
substringof(val, name) contains Matches all fields with the name “name” that contains the value in “val”

2.3. Filter logic

To create a filter it is needed to use several expressions so the search for a specific data entry can be as specific as possible. This can be used by separating filter operations with either with the two keywords: and, or. Clauses of filter expressions can be created by adding parentheses around one expression. Below is shown a long example how to do this:

my/resource?$filter=( startswith(address,first) and endswith(address,street) ) or age lt 25

2.4. Named filters

Named filters are filters that are accessible using a name directly in the $filter string. The available filters are specified for a specific resource and can help when building the desired filter.

Example: The resource system/api/clients contains the named filter "ActiveClients". This filters out clients that are active. The complete request would look like this:

system/api/clients?$filter=ActiveClients()

The same result without using the named filter would be to access the resource like this:

system/api/clients?$filter=Status eq 4

This would be equivalent to using the named filter "Active Clients".

Named filters may contain macros. It is possible to combine the named filters with regular filter syntax. For example:

system/api/clients?$filter=ActiveClients() and substringof('service', ClientName)

2.5. Filter Macros.

To simplify the creation of filters, we have macros. The different types are described further down.

Example of a simple macro usage:

my/resource?$filter=OrderDate ge startOfYear()

If the current year is 2020, the above would be equivalent to the following filter:

my/resource?$filter=OrderDate ge 2020-01-01T00:00:00

If the macro is within quotation marks, i.e. within a string, the macro will not be run.

2.5.1. The "inc"-parameter

The argument “inc” can be used to adjust the result of most macros. The increment is added after the macro itself has been run. For example: If the current date is 2014-01-01, then the macro startOfYear() would return 2014-01-01T00:00:00, while startOfYear(-1d) would return 2013-12-31T00:00:00.

The macro argument “inc” has the format “(+/-)n(y/M/w/d/h/m/s)”. Valid time units are y (years), M (Months), w (weeks), d (days), h (hours), m (minutes), s (seconds). If no sign (+/-) is specified, the default “+” is assumed. If no time unit is specified, the default for the specific macro is used. Note that all macros do not support all time units. Multiple arguments can be used with a macro by separating them with commas (,). Valid examples of the use of “inc”:

2.5.2. Date Macros

Date macros returns a date and time on the format "yyyy-MM-ddThh:mm:ss".

now("inc") Returns the current time (yyyy-MM-ddThh:mm:ss)
startOfYear("inc") Returns the starting date and starting time of the current year. (yyyy-01-01T00:00:00), where yyyy is the current year
startOfMonth("inc") Returns the starting date and starting time of the current month. (yyyy-MM-01T00:00:00), where yyyy-MM is the current month
startOfDay("inc") Returns the starting date and starting time of the current day. (yyyy-MM-ddT00:00:00), where yyyy-MM-dd is the current date
startOfWeek("inc") Returns the starting date and time of the current weak (Monday of the current week), (yyyy-MM-ddT00:00:00), where yyyy-MM-dd is the Monday of the current week)
endofYear("inc") Returns the last date and last time of the current year (yyyy-12-31T23:59:59), where yyyy is the current year.
endOfMonth("inc") Returns the last date and last time of the current month (yyyy-MM-31T23:59:59), where yyyy-MM is the current month
endOfDay("inc") Returns the last time of the current day (yyyy-MM-ddT23:59:59), where yyyy-MM-dd is the current date
endOfWeek("inc") returns the last second of the last day of the week (yyyy-MM-ddT23:59:59), where yyyy-MM-dd is the Sunday of the current week

2.5.3. Interval Macros

Interval Macros are like Date macros, but they define a start time and an end time. These can be used whenever the Date Macros can be used. These have the format “(x ge yyyy-MM-ddThh:mm:ss and x le yyyy-MM-ddThh:mm:ss)”, where “x” is the filtermapping.

currentYear("inc") Returns the interval between (yyyy-01-01T00:00:00 and yyyy-12-31T23:59:59), where yyyy is the current year
currentmonth("inc") Returns the interval between (yyyy-MM-01T00:00:00 and yyyy-MM-31T23:59:59), where yyyy-MM is the current month
currentDate("inc") Returns the interval between (yyyy-MM-ddT00:00:00 and yyyy-MM-ddT23:59:59), where yyyy-MM-dd is the current date
previousYear("inc") Returns the same as currentYear(-1y): returns the interval between (yyyy-01-01T00:00:00 and yyyy-12-31T23:59:59), where yyyy is the previous year from now
previousMonth("inc") Returns the same as currentMonth(-1M): returns the interval between (yyyy-MM-01T00:00:00 and yyyy-MM-31T23:59:59), where yyyy-MM is the previous month from now

2.5.4. Period Macros

These are Rambase specific macros.

currentPeriod("inc") Returns the current period (yyyyMM), where yyyyMM is the current month
previousPeriod("inc") Same as currentPeriod(-1M): returns the previous period (yyyyMM) where yyyyMM is the previous month

2.5.5. Other filter macros

currentUser() Returns the EmployeeId of the currently logged in user
currentDB() Returns the database of the currently logged in user