Filtering and sorting

1. Sorting

It is possible to specify custom ordering when making GET requests to API resources listing objects. The fields to order by is specified in prioritized order in the &$orderby query parameter. When providing multiple fields, each field should be seperated by a comma.

The default ordering is ascending, but is is possible to add desc for descending order.

2. Filtering / searching

It is possible to specify a filter when making GET requests to API resources listing objects. The filter is specified in the &$filter query parameter and is based on OData filter syntax.

2.1. Filter operators

eq equals	Equality statement. Requires equal value between left and right side. Ex: $filter=Name eq 'Nilsen' or Name eq FirstName
ne not equals	Not equal statement. Requires different value between the left and right side. Ex. $filter=Name ne 'Hatteland'
lt less than	Less than statement. Requires left side to have a lower value than right side. Ex: $filter=TotalAmount lt 100
le less or equal	Less than or equal statement. Requires left side to have an equal or lower value than right side. Ex: $filter=Price le 20
gt greater than	Greater than statement. Requires left side to have a higher value than right side. Ex: $filter=TotalAmount gt Price
ge greater or equal	Greater than or equal statement. Requires left side to have an equal or higher value than right side. Ex: $filter=TotalAmount ge 200
in equals any in list	In statement. Requires equal value between field on the left side and any of the elements on the right side. Ex: $filter=Name in ('Nilsen' 'Olsen', 'Pettersen')

2.2. Filter functions

startswith(fieldname, value) returns boolean	Requires field specified in fieldname to start with the value specified in value. Ex. $filter=startswith( Name, 'Hatte')
endswith(fieldname,value) returns boolean	Requires field specified in fieldname to end with the value specified in value. Ex. $filter=endswith( Name, 'Land')
substringof(value, fieldname) returns boolean	Requires field specified in fieldname to contain the value specified in value. Ex. $filter=substringof( 'telan', Name)
length(fieldname) returns integer	Returns the number of characters in fieldname. Note that some API resources only supports eq and ne. Ex. $filter=length(ScheduledShippingDate) eq 2
indexof(fieldname, value) returns integer	Returns the index of value in fieldname. Note that some API resources only supports eq and ne. Ex. $filter=indexof(Name, 'sen') eq 5
year(fieldname) month(fieldname) day(fieldname) hour(fieldname) minute(fieldname) returns integer	These functions returns different time components from fieldname. Ex. $filter=year(ScheduledShippingDate) eq 2024 or month(RequestedDeliveryDate) eq 12
round(fieldname) ceiling(fieldname) floor(fieldname) returns integer	These functions returns different rounded values of fieldname. Ex. $filter=round(Price) eq 5

2.3. Filter logic

It is possible to combine multiple filter functions, named filters and operators to make the filtering as specific as possible. The different parts of the filter is separated with the keywordsand and/or or. Clauses of filter expressions can be created by adding parentheses around one expression.

2.4. Named filters

Named filters, also called predefined filters, are filters that can be applied by using a name directly in the $filter string. Named filters is different for each API resource and can be found in the documentation.

The named filter "MyOpenOrders" found in the API resource for listing sales orders is a good example. The two filters below yields the same results:

Note that CurrentUser() is not a named filter, but a macro.

2.5. Filter macros

Filter macros can be used to generate certain values in the filter string. In the example above we saw the macro CurrentUser() which will be replaced by the user id (PID) when evaluating the filter. In the example below the macro startOfYear() will be replaced by the first date of the year at 00:00. The two filters yields the same results:

2.5.1. Date time 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.2. 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 field being filtered by.

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.3. Period macros

These are RamBase specific macros used for filtering fields representing accounting periods.

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.4. Other filter macros

currentUser()	Returns the user identifier (PID) of the current user
currentDB()	Returns the database/company used in the current API request

2.5.5. The "inc"-parameter

The date-, interval- and period macros have an increment parameter called "inc". This parameter can be used to adjust the result of the 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 "inc"-parameter 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" in the macro now():