Skip to content
Developerhome
Product homepage
Sage 200c

How to query data

  Less than to read

Query parameters

To add query parameters to a request URL the question mark character (?) must be appended to the URL first. Each query parameter is expressed as a key-value pair and multiple query parameters must be separated by an ampersand character (&).

Sage 200 API Query Parameters

The Sage 200 API query parameters follow a subset of the OData standard (see table below). To find out more about the OData Standard go to http://www.odata.org/.

$select /Products?$select=Id,Name,Brand,Barcode

$select enables you to limit the fields that are returned within a collection request. This example will only return Id, Name, Brand and Barcode fields.
$filter /Products?$filter=Code eq 'PRD05' or SalesPrice gt 3000

$filter returns only the entities which match the criteria set by the filter specified. This example request returns the products where the value of the Code field is equal to ‘PRD05’ or the value of the SalesPrice field is greater than 3000. Note that clauses are combined with logical operators such as "and" or "or", and that values of type string should be in single quotes.

Additionally, the functions 'contains', 'startswith' and 'endswith' can be applied to strings. For example:
/Products?$filter=contains(Name, 'H')
/Products?$filter=startswith(Barcode, '08')
/Products?$filter=endswith(Code, 'x')

Filters can be applied to date fields. For example:
/SalesOrders?$filter=DeliveryDate gt 2016-02-23
/Products?$filter=Timestamp gt 2019-12-16T09:03:10.325Z
Note: when including the time, you must specify the UTC timezone (Z at the end as in the example).

Filters can be applied to enum fields. For example:
/SalesOrders?$filter=Status eq 'Pending'
Note: value must be inside single quote marks, and must be enum value.

Filters can be nested using brackets. For example:
/SalesOrders/?$filter=(IssueDate ge 2019-12-16 and DeliveryDate le 2019-12-31) or Status eq 'Pending'
$orderby /Products?$orderby=Name asc

$orderby enables you to return a collection in ascending or descending order. The order is based on the field name you specify and the key words asc for ascending and desc for descending. This example orders the products list by the Name field in ascending order.
$top /Products?$orderby=Name asc & $top=10

$top returns the maximum number of JSON resources in a request. If the '$orderby' parameter is not specified, then the default is used. When 'top' is not specified, the default value is 50. This example request returns the first 10 JSON resources as a collection.
$skip /Products?$skip=10

$skip requests the number of items in the collection be skipped and not included in the result. The example request returns the products list starting with the 11th product.
$expand /Products?$expand=ExtendedProperties
/SalesOrders?$expand=Lines
/Customers?$expand=MainContact

$expand enables you to include child JSON resources that are linked to the parent JSON resource within your results.
$count /Products?$count=true

The $count returns the number of entities within the collection.
Multiple It is possible to combine multiple OData commands within a request and create quite complex and insightful results.

It is possible to filter on expanded entities. The example below expands the sales order on the customer, but only returns the customer name:
/Customers('15879')?$expand=MainContact&$select=MainContact.Name

The $select can also be applied to reduce the fields in both the parent and child entity like this:
/SalesOrders('15879')?$expand=MainContact&$select=Id,BusinessName,MainContact.Id,MainContact.Name

This example returns the products list ordered by id, returning only the top 15:
/Products?$orderby=Brand&$top=15