Pagination

The notion of pagination is fundamental.

For example, if your application wishes to display a list of customers, you will define a pagination to offer pages of lists of customers consistent with the user interface and not a page displaying all the customers.
You can thus decide to display 10, 25, 50 or 100 customers per page but never 1000, 5000 or 10000, these numbers not being consistent with the user interface.

In addition, your developments must be in phase with the maximum number of records that Sage Active Public API V2 returns on each call.

By default, the pagination value is set to 20, which means that if you don’t specify a different value, 20 records will be returned per request.

It is important to know that Sage Active Public API V2 returns a maximum of 500 records for each request.

So if you wish, for example, to retrieve all of the accounting entries for a financial year in order to calculate consolidations, you must anticipate that the number of entries to be retrieved is likely to probably exceed 500 and you will have to implement an iteration in order to retrieve by batch of 500 records all entries.

Why is Sage Active Public API V2 limited to 500 records per call?

The data requested by Sage Active Public API V2 will pass between the Sage Active application and your application.
If a table in your database contains tens of thousands of records, you cannot send them in a single request at the same time for volumetric and security reasons.
To avoid this problem, the server can limit the number of records it sends with each request.

For Sage Active Public API V2 this limit has been set to 500.

Notion of pagination

Example

Let’s imagine that your application wants to display a list of customers by pages of 100 according to filter criteria:

  • Your file contains 6500 customers,
  • the currently user-defined filter was passed through your request to the server, the server locally found 175 clients meeting the filter criteria,
  • on the application side you will request a maximum of 100 records for each, the request will return 100 records and not 175,
  • for the user to be able to consult the 175 results, it is necessary that you define a pagination,
  • for this, you can provide a Next button so that your user can consult the first 100 results then the following 75.

Pagination using Cursor

To manage the pagination, you will use the notion of cursor.
In cursor-based pagination, a cursor is used to keep track of where in the data set the next items should be fetched from.

Forward pagination

To enable forward pagination, two arguments are required.

  • first: Specifies the maximum number of first items to return
  • after: Specifies the end cursor of the previous page of items

The following information, returned in the response, will also be used.

  • pageInfo
    • endCursor: The cursor of the last item in the result set
    • hasNextPage: Indicates whether there is a next page of items
Backward pagination

To enable backward pagination, two arguments are required.

  • last: Specifies the maximum number of last items to return
  • before: Specifies the start cursor of the next page of items

The following information, returned in the response, will also be used.

  • pageInfo
    • startCursor: The cursor of the first item in the result set
    • hasPreviousPage: Indicates whether there is a previous page of items

Note that you can also get the total number of records in the response by using totalCount.

Example
  • The total is 257 records,
  • the maximum per call is forced to 100 records via first:100 or last:100,
  1. the first call is made without after: and with first:100, records from 1 to 100 are returned,
    hasNextPage is true, hasPreviousPage is false, endCursor and startCursor are filled in.
  2. the second call is made with after:$endcursor and with first:100, the records from 101 to 200 are returned, hasNextPage is true, hasPreviousPage is true, endCursor and startCursor are filled in.
  3. the third call is made with after:$endcursor and with first:100, the records from 201 to 257 are returned, hasNextPage is false, hasPreviousPage is true, endCursor and startCursor are filled in.
  4. to go backward a call is made with before:$startcursor and with last:100, records from 101 to 200 are returned, hasNextPage is true, hasPreviousPage is true, endCursor and startCursor are filled in.

Thanks to hasNextPage and hasPreviousPage, it is easily possible to disable or enable buttons Previous and Next and to associate with each of these buttons a process to assign after: with the value of endCursor when the user clicks on the Next button or assign before: with the value of startCursor if he clicks on Previous button.

Important Notes
  • If order: is defined to sort the result, it always applies on the server side and therefore applies to the entire result and not only to the first batch of records returned by the API.
  • On the other hand, if you apply a sort to the result in your application, this sort will be local to this result.
    If, for example, you retrieve the 100 most recent invoices by estimating that this number is sufficient for the user, if you then apply a sort to the collection, you only sort the 100 records retrieved from the inverse of order: which applies to the entire result on the server side.
  • Identical remark if you apply a filter on the result, the filter will only apply to the 100 records unlike where which applies to all the data on the server side.

Recommendations

Tests organization with enough data

We recommend that you use an organization with enough data to avoid being trapped if for some requests the threshold of 500 records is not reached.
For example, if you are testing with a demo organization Sage Active, as this folder is likely to have a number of customers, suppliers, accounting entries less than 500, you might believe, if you failed to manage pagination, that your development is correct.
But once put in production, your application used with a file containing real data with more than 500 customers, or suppliers or accounting entries, would prevent the user from accessing the data beyond the first 500 records.

Or emulate the limit to less than 500 records

A trick is also to define a global parameter named for example maxPaginate with a value lower than the average number of records in your test folder.
For example you can assign maxPaginate with the value 10. Then when making your queries, always assign the first and last arguments with maxPaginate, so your queries will only return the maximum number mentioned in maxPaginate.
You will then be alerted more quickly when designing your application that you must manage pagination to allow access to all the data and not to a truncated view of the data.
Once pagination is tested and your development is working, you can assign maxPaginate with the value 500 and your development will continue to work.

<  2. Operators & keywords for queries4. Updating records  >