AX 2012 - Use Paging feature in the AIF Document Service

Hi All

As you know, the Document Service doesn't support the Paging, only Query Service.
Take a look here

 

Below the trick to enable this feature also for the Document Service:

1-      Take a look to the standard class AxdPricelist, prepareForQuery method

2-      Practically, the service use a temporary table that it’s filled every time the service is called

3-      So, the idea is use a temp table and fill it with the proper data coming from a “direct” query to SQL

4-      The Temp Table must have two extra fields, like PageNumber and RowNumber

5-      So, in the prepareForQuery method execute a query directly to SQL using Paging SQL feature like :

a.       Select Field1, Field2, Fieldn from Table\View Order By Field Desc OFFSET ((' + PageNumber + ' - 1) *' + RowsPage + ') ROWS FETCH NEXT ' + RowsPage + ' ROWS ONLY

6-      From my side I used the Statement class and fill the temp table like :

    ResultSet = statement.executeQuery(SQL);

 

    while ( ResultSet.next() )

    {

        TempTable.clear();

 

        TempTable.Field1                = ResultSet.getXX(1);

        TempTable.Field2                = ResultSet.getXX(2);

        …..

 

        TempTable.PageNumber  = PageNumber;

        TempTable.RowsPage    = RowsPage;

 

        TempTable.insert();

    }

 

7-   Before to execute the query you have to retrieve the “range” fields, so PageNumber and RowNumber like :
    Qbr1 = _query.dataSourceNo(1).findRange( fieldNum(TempTable, PageNumber ) );

    Qbr2 = _query.dataSourceNo(1).findRange( fieldNum(TempTable, RowsPage   ) );

 

8-      In a complex queries, we have to create an AX View and use it in the above query.

9-      I have used the OFFSET clause. This feature is available for the SQL Server 2012 and above version. Otherwise you have to use the ROW_NUMBER clause.

Stay Tuned!