Paging in a Query(Query run)/Paging while retrieving the records from a query
Today, I have a found a new function in QueryRun class and thought of sharing it here in this post. Its use can be found while integrating AX with some external applications.
I am sure that everyone has used queryrun class in x++ code. I have found out a special function in QueryRun Class with which we can do paging while retrieving the records.i.e., retrieve a range of rows from a query i.e., For example, if we get some 14000 records from a query but we want to retrieve the records from 11501 to 12000 only then this could be a useful tip. you can follow the below code snippet to perform paging for a query.
Query query = new Query();
QueryBuildDataSource qbds;
QueryRun qr;
tableid tableid;
tableid = tablename2id(‘InventTable’);
//Add a datasource to a query
qbds = query.addDataSource(tableid);
//Order by item id
qbds.addOrderByField(fieldname2id(tableid,’ItemId’));
//Instantiate a Queryrun class
qr = new QueryRun(Query);
qr.literals(true);
// Enable position paging for the queryrun object
qr.enablePositionPaging(true);
//Add a range by providing the parameters as starting record number and number of records
qr.addPageRange(11501,500);
xmlDoc = XMLDocument::newBlank();
// Create first line containing version info
rootNode = xmlDoc.documentElement();
xmlElement = xmlDoc.createElement(‘OUTPUT’);
rootNode = xmlDoc.appendChild(xmlElement);
while (qr.next())
{
common = qr.getNo(1);
xmlElement = xmlDoc.createElement(‘ITEMID’);
NodeItemId = rootNode .appendChild(xmlElement);
xmlText = xmlDoc.createTextNode(common.(Fieldname2id(tabid, ‘ItemId’)));
NodeItemId .appendChild(xmlText);
}
Now, thus we can get the list of required 500 itemids into an xml and thus we can pass it to some external application.
Although it has huge advantage, it has a disadvantage too, i.e., this cannot be used on a temporary table.
An important point to remember is that an ordering must be done for the query before paging is done. For Ex: qbds.addOrderByField(fieldname2id(tableid,’ItemId’)) or qbds.addOrderByField(fieldname2id(tableid,’ItemId’), sortorder::descending) or any other ordering method. Therefore, Paging will not work if the query is not in a sorted order.
Hi Giridhar,
It is very useful.
actualy m new to ax; i was searching use of QBDS and its examples.could u help us on QBDS with simple example.