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
@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
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

Aliaksandr Maksimau Aliaksandr Maksimau · Accepted Answer · 2019-02-05T11:09:12

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 Answers