how to add groupby and join in single query in ax 2009

Finance and Operations/AX Developer Forum
1

Hi,

I need to group items in salesline based on its revision and also needs to sum remainsalesphysical field.

It needs to display as a single record

2

hi,

you shoul try things like that:

sum group by:

while select sum(CreditMax) from custTable group by CustGroup

{

print custTable.CustGroup, " ",custTable.CreditMax;

}

join:

while select ledgerTable

join ledgerTrans

where ledgerTrans.accountNum ==

ledgerTable.accountNum

{

amountMST += ledgerTrans.amountMST;

}

3

i need to show when we open the form, i need a query to join and groupby

4 
public void init()
{

    super();
    breakpoint;
     q=new query();
     qbds=q.addDataSource(tablenum(salesline));
      qbds.addRange(fieldnum(salesline,itemid));


     qbds1=qbds.addDataSource(tablenum(InventDim));
     qbds1.addLink(fieldNum(InventDim, InventDimId), fieldNum(salesline, inventdimid));
     qbds1.joinMode(JoinMode::InnerJoin);
      qbds.addGroupByField(fieldnum(salesline,itemid));
   
    qbds1.addGroupByField(fieldnum(inventdim,inventcolorid));

    qbds.addSelectionField(fieldnum(salesline,remainsalesphysical),selectionfield::Sum);
  

}

i’m trying like this but its not doing sum for remainssalesphysical

5

Hello

Try like that

qbdsProjectLines.addGroupByField(fieldNum(AttendanceProjectLines,projectId));//, Sections, Activity1Code));
qbdsProjectLines.addGroupByField(fieldNum(AttendanceProjectLines,Sections));
qbdsProjectLines.addGroupByField(fieldNum(AttendanceProjectLines,Activity1Code));

qbdsProjectLines.addSelectionField(fieldNum(AttendanceProjectLines,WorkingHours),SelectionField::Sum);