AX 2012 Group by with common table

Question

How to group by a common table with a specific field?

I'm getting a syntax error on (dt.fieldname2Id('BatchNo'))

Here's my code:

Common          common;
SysDictTable    dt;

dt        =    SysDictTable::newName('Table1');
common    =    dt.makeRecord();

while select count(RecId) from common
    group by common.(dt.fieldname2Id('BatchNo'))  //syntax Error here
    where common.(dt.fieldname2Id('flag'))==1
{
    info(int642str(Common.Recid));
}

You can't do a `group by` with this method. You'll have to do what is suggested below. — Alex Kwitny, Feb 05 '19 at 18:35
@AlexKwitny Yup thanks, that worked. I have been trying all sorts of things with Common, trying to treat it like a normal buffer table object as I'm unable to find any good documentation on Common and other generic types. Please share if you know any. — Mohammad Yusuf, Feb 06 '19 at 07:01
Common is super useful, but I think the real issue is a limitation of the compiler's ability to parse the `select` statement and determine the `group by` field. I think it usually expects only the field name and not `Table.Field`. If I'm right, then it's technically impossible unless you use Macros to define every possible table and have it switching around at runtime (yuck). — Alex Kwitny, Feb 06 '19 at 21:54

score 4 · Accepted Answer · answered Feb 05 '19 at 11:09

You can use Query instead:

Common                  common;
SysDictTable            dt;
Query                   query = new Query();
QueryBuildDataSource    qbds; 
QueryRun                queryRun;

dt     = SysDictTable::newName('SalesTable');
common = dt.makeRecord();

qbds = query.addDataSource(common.TableId);
qbds.addGroupByField(dt.fieldname2Id('CustAccount'));

queryRun = new QueryRun(query);

while (queryRun.next())
{
    ...
}

AX 2012 Group by with common table

1 Answers1