SearchTemplates for the API - Adding Pagination

The following issue has been identified and replicated in all releases of EXO Business OnTheGo.

Issue

Using the Pagination instructions for the EXO API from the Retrieving Data page does not work correctly for Search Template endpoints - https://developer.myob.com/api/exo/exo-api-overview/retrieving-data/

Instead calling a Search Template will always return all results.

Resolution

Customise the Search Template to add a Pagination parameter.

A workaround possible by adding a CTE to the Search Template and calling for Row Numbering inside of CTE, then using the PageNo against that. (You can even work in dynamic page sizing if needed)

An example of this is below:

Add a PageNo variable directly to the query (DECLARE it at start)
Add a CTE containing the original Search Template
Add a ROW_NUMBER column to the SearchTemplate referring to calculate the page number a row should display on.
Move the original ORDER BY to the end of the search template. (Note, the ORDER BY needs to be replicated between the CTE and at the end of the query to ensure absolutely consistent results. (Though the ROW_NUMBER () call will generally enforce result ordering, the explicit ordering at the end doesn't hurt.)

DECLARE @LOCNO INTEGER

--Add a line to establish @PAGENO as a variable in the stock template

DECLARE @PAGENO INTEGER --Added to allow Pagination.

--Add a CTE wrapped to the original search template

;WITH PRECALC AS ( --CTE wrapper so could add a Pageno Column. Note, Pages start at 0 in this example.
SELECT
SI.STOCKCODE Stockcode,
DESCRIPTION Description,
CASE
WHEN ISNULL(UPDATEITEM_QTY, 1) = 1 THEN SLI.QTY*ISNULL(UPDATEITEM_QTY, 1)
ELSE SLI.QTY
END AS Quantity,
CASE
WHEN STATUS = 'S' THEN 'SKU'
WHEN STATUS ='L' AND LEN(UPDATEITEM_CODE) > 0 THEN 'LINK PLU'
ELSE 'PLU'
END AS Status,
ROUND(TOTALSTOCK, 2) AS [Total Quantity]

-- ADD a column to calculate PageNo (In this example is 100)

,ROW_NUMBER() OVER(PARTITION BY SLI.LOCATION ORDER BY SLI.LOCATION, SI.STOCKCODE )/100 AS PageNo –Note, because the ROW_NUMBER is an Integer, dividing it by 100 will always result in Integer results of 100 results.

FROM STOCK_LOC_INFO SLI
JOIN STOCK_ITEMS SI ON SI.STOCKCODE = SLI.STOCKCODE
AND ((SI.STATUS = 'S' AND SLI.QTY > .01) OR (SI.STATUS = 'L'))
WHERE /* FILTER SLI */
SLI.LOCATION = @LOCNO AND (SI.ISACTIVE ='Y')

--Add CTE Wrapper and select from the CTE to return results. Note the @PageNO variable is called to force filtering by page.
) –End of the Precalculation CTE.
select * from PRECALC –Select the whole CTE content.
WHERE PageNo=@PageNO --And bring me just the page number of results I ask for.

ORDER BY STOCKCODE ASC –Original Order By clause has been moved to the end, and matches the PageNo row-numbering, to make sure results are consistent.

You can replace the division during the ROW_NUMBERING with an extra variable called something like @PageSize if you add an extra variable to the call, this allows for dynamic page sizing.