Retrieving Data

HTTP GET

Let me GET the content

Use the HTTP GET method to retrieve data from the database. This includes both lists and individual records. For example, you would use the GET method to retrieve a list of sales made in a particular month, or the contact details of a customer. By default, successful API responses are returned in JSON format.

Filtering

OData-supported query string filters

The Open Data Protocol (OData) is a data access protocol which uses the REST model and simple HTTP requests to provide standard CRUD access to a data source through a website. For more information, including an introduction to OData, see here.

The MYOB Business API supports four query options: 

• $orderby - Order results in ascending or descending order, by a specified field.
• $top - Retrieve the first x records in a list.
• $skip - Retrieve all records in a list, except for the first x records.
• $filter - Retrieve all records matching a specified expression.

See the sections below for details about using these query options with the AccountRight API, or see here for more information.

Pagination

Paging: a common requirement for applications is to expose partial results from larger sets of data i.e. transaction based collections like JournalTransaction and AccountRegister.

The MYOB Business API default page size is set to 400 however a maximum of 1000 results can be set and returned per page. To do so use the $top filter like so in your call to return page 1 of 1000 records /GeneralLedger/JournalTransaction/?$top=1000

To make pagination easier for you, the JSON response information returned provides paging parameters, including a URI NextPageLink. Calling this URI allows you to retrieve the next set of records. This URI will show you the usage for $top and $skip parameters.

We highly recommend that when used $skip is a multiple of $top for example /GeneralLedger/JournalTransaction/?$top=500&$skip=500

$orderby

Use the $orderby filter to specify which field you want lists of items to be ordered by. For example, you could choose to order a list of accounts by AccountName or by AccountNumber, or a list of tax codes by TaxCodeType or Rate.

By default, results are listed in ascending order, by the chosen field. You can specify whether to sort results in ascending or descending order using the 'asc' and 'desc' keywords.

Use the syntax shown in the following examples to retrieve the results in the order you want.

Ascending order

The default syntax. This query will return the entire list of Accounts, sorted in ascending order by the Account Number:

{domain}/{cf_guid}/GeneralLedger/Account?$orderby=Number

You can also use the 'asc' keyword to specify that the results should be returned in ascending order. This will return the same result as the above query:

{domain}/{cf_guid}/GeneralLedger/Account?$orderby=Number asc

Descending order

This query uses the 'desc' keyword to return the entire list of Accounts, sorted in descending order by Account Number:

{domain}/{cf_guid}/GeneralLedger/Account?$orderby=Number desc

$top

Use the $top filter to select the first x items in a list, where x is a positive integer specified in the query (ie. $top=x). If x is less than 0, the URI is not valid.

Examples

This query retrieves the first 5 sale invoices from a collection of sales:

{domain}/{cf_guid}/Sale/Invoice/?$top=5

The $top filter can be used in combination with other filters, such as $orderby (see above). This query retrieves the first 5 sale invoices from a list of sales sorted by Date:

{domain}/{cf_guid}/Sale/Invoice/?$top=5&&orderby=Date

$skip

Use the $skip query to skip the first x list entries and return all remaining entries, starting from entry x+1. The value of x must be a positive integer specified in the query (ie. $skip=x). If x is less than 0, the URI is not valid.

Examples

This query returns all customers starting from the fourth entry in a list of customers:

{domain}/{cf_guid}/Contact/Customer/?$skip=4

The $skip filter can be used in combination with other filters, such as $orderby (see above). This query returns all customers, starting from the fourth entry in a list of customers sorted by ABN:

{domain}/{cf_guid}/Contact/Customer/?$skip=4&$orderby=ABN

$filter

Use the $filter query to select only entries that match an expression specified in the query. Available expressions are Eq (equal to) and Gt (greater than).

Expressions can include references to both properties and literal values. Literal values can include strings (enclosed in single quotes), numbers, boolean values (true or false), or any of the additional literal representations. For a full list of supported properties and literals, along with operators and functions, see here.

eq (equal to)

The Eq expression returns all records where the specified property is equal to a specified literal value.

This query returns contacts where the Type is equal to supplier or customer:

{domain}/{cf_guid}/Contact/?$filter=Type eq 'Customer' or Type eq 'Supplier'

The Eq expression can be used in combination with other filters, such as $orderby (see above). This query returns a set of contacts, sorted by DisplayID, where the Type is equal to Personal and Employee:

{domain}/{cf_guid}/Contact/?$filter=Type eq 'Personal' or Type eq 'Employee'&$orderby=DisplayID

gt (greater than)

The Gt expression returns all records where the specified property is greater than a specified literal value.

This query returns a set of accounts where the Account DisplayID is greater than 5-0000:

{domain}/{cf_guid}/GeneralLedger/Account?$filter=DisplayID gt '5-0000':

The Gt expression can be used in combination with other filters, such as $orderby (see above). This query returns a set of accounts, sorted by Name, where the DisplayID is greater than '4-0000':

{domain}/{cf_guid}/GeneralLedger/Account/?$filter=DisplayID gt '4-0000'&$orderby=Name

any and all operators

Support for 'any' and 'all' operators is now available with OData v3 protocols click here for more information.

The following query returns a customer contact where an email address is equal to 'Davis@yahoo.com.au' from within an array of contact addresses.

{domain}/{cf_guid}/Contact/Customer/?$filter=Addresses/any(x: x/Email eq 'Davis@yahoo.com.au')

Primitive Data Types

To further extend the $filter query option, primitive data types such as DateTime can be implemented, for more information on how to represent this see here

This query returns a set of transactions where the DateOccurred is greater than or equal to 01/4/2013 and less than or equal to 30/04/2013:

{domain}/{cf_guid}/GeneralLedger/JournalTransaction/?$filter=DateOccurred ge datetime'2013-04-01' and DateOccurred le datetime'2021-04-30'

More Examples

There is a lot more that you can do using oData to filter the data in the MYOB Business API. From finding transactions for a specific month, to items that contain a specific string. You can do matches on items that start with a specific string or do partial match filters. To learn a little more we've written some blog posts.

• oData $filter tips & tricks
• Digging into oData with MYOBapi