Monday, May 18, 2015

Count records in Query Vs Cound Loops in Query

Sometime we do need to know the number of records fetching through X++ query and this can be achieved by SysQuery::CountRecords(QueryRun) function. 

This function works similar to this X++ logic;

ProjTable   projTable;    
select count(RecId) from projTable;info(strFmt("%1 total records", projTable.RecId)); // total tecords 1130

Lets see with X++ query;

static void CountProjTableRecords(Args _args)
{      
    Query                           query = new Query();    
    QueryRun                     queryRun;    
    QueryBuildDataSource qbd;    
    qbd = query.addDataSource(tablenum(ProjTable));
   
    queryRun = new QueryRun(query);
   
    info(strfmt("Total Records in Query %1", SysQuery::countTotal(queryRun))); 
// total tecords 1130
}

However, this SysQuery::CountRecords(QueryRun) function count only the number of records of the first datasource. Let's assume if we need to count number of records of a query with more than one datasource, we need to use SysQuery::CountLoops(QueryRun) function.

static void CountProjTableRecords(Args _args)
{      
    Query                           query = new Query();    
    QueryRun                     queryRun;    
    QueryBuildDataSource qbds, qbds1;    
    qbds = query.addDataSource(tablenum(ProjTable));
    
    qbds1 = query.addDataSource(tablenum(ProjTable)).addDataSource(tablenum(ProjForecastTable));    
    queryRun = new QueryRun(query);
   
    info(strfmt("Total Records in Query %1", SysQuery::countLoops(queryRun))); 
}

1 comment:

  1. What if we have multiple dataSources in a query, If we get the count from queryRun it will get the count of all the records.

    ReplyDelete

I will appreciate your comments !

How to enable new Microsoft teams - Public Preview!

New Microsoft Teams is just AWESOME, quick but useful post below shows how you have this preview feature to make your life EASY!  Open Micr...