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:
https://api.rambase.net/sales/orders?$filter=Status lt 9 and (Assignee eq CurrentUser() or SalesAssistant eq CurrentUser() or AccountManager eq CurrentUser())
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:
https://api.rambase.net/sales/orders?$filter=CreatedAt ge 2024-01-01T00:00:00
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():
- now(+1d)
- now(2d)
- now(-4d)
- now(+1w,+1d)
- now(+1M,-1d)