how to group 3 fields using dynamic query

Finance and Operations/AX Developer Forum
1

Hi all ,

I need to group the Project id , Section and Act Code in single line using dynamic queries. and sum the total Hours of all lines.

i write the code like this. but not working.

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

pastedimage1525677031484v2.png

Suggest me any one.

2

Are there more fields in the query object or in the form than the 3 that you placed a group by on?

3

Do you have code that says do sum on total hours field?

Example - qbdsProjectLines.addSelectionField(fieldNum(AttendanceProjectLines,TotalHours),SelectionField::Sum);

4

Hi

i need to group the Project Id, Section and ACT Code, and sum the total hours in a single line.

5

Hi kranthi,

But here grouping of all fields are not working and its coming again repeated values

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

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

6

If possible, can you show your total code?

7

Hi kranthi,
This is my code.

private void loadingTimesheetData(FromDate _fromdate,Todate _todate,CostAllocationId _allocationId)
{
Query query;
QueryBuildDataSource qbdsMaster, qbdsProjectLines, qbdsLines;
QueryRun qr;
real totalWorkingHrs, test ;

//super();
query = new Query();
qbdsMaster = query.addDataSource(tableNum(AttendanceMaster));
qbdsProjectLines = qbdsMaster.addDataSource(tableNum(AttendanceProjectLines));
qbdsProjectLines.relations(true);
qbdsProjectLines.joinMode(JoinMode::InnerJoin);

fromDate = _fromdate;
toDate = _todate;
//If from date is not specified
if(!fromDate)
{
fromDate = dateNull();
}
if(!toDate)
{
toDate = today();
}
progress = new SysOperationProgress(1, NoYes::Yes);
progress.setAnimation(#AviUpdate);
progress.setTotal(row, 60000);
qbdsProjectLines.addRange(fieldNum(AttendanceProjectLines, projectId)).value(strFmt(’!""’));

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

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

qbdsMaster.addRange(fieldNum(AttendanceMaster, TimeSheetdate)).value(queryRange(fromDate, toDate));
delete_from costAllocationTrans
where costAllocationTrans.CostAllocationId == _allocationId;
qr = new QueryRun(query);

select sum(TotalWorkingHours) from projectLinesBuf
where projectLinesBuf.ProjectId != ‘’
join payTimeAndAttendanceMasterBuf
where payTimeAndAttendanceMasterBuf.TimeSheetdate >= fromDate
&& payTimeAndAttendanceMasterBuf.TimeSheetNumber == projectLinesBuf.TimeSheetNumber
&& payTimeAndAttendanceMasterBuf.TimeSheetdate <= toDate;

totalWorkingHrs = projectLinesBuf.TotalWorkingHours;

while(qr.next())
{
payTimeAndAttendanceMaster = qr.get(tableNum(AttendanceMaster));
payTimeAndAttendanceProjectLines = qr.get(tableNum(AttendanceProjectLines));

costAllocationTrans.clear();
costAllocationTrans.CostAllocationId = _allocationId;
costAllocationTrans.ProjectID = AttendanceProjectLines.ProjectId;
costAllocationTrans.Sections = AttendanceProjectLines.Sections;
costAllocationTrans.ActivityCode = AttendanceProjectLines.Activity1Code;
costAllocationTrans.TotalHours = AttendanceProjectLines.TotalWorkingHours;

select payTimeAndAttendanceLines
where payTimeAndAttendanceLines.TimeSheetNumber == payTimeAndAttendanceMaster.TimeSheetNumber;

costAllocationTrans.Division = AttendanceLines.Division;
costAllocationTrans.Department = AttendanceLines.Department;

costAllocationTrans.TotalHoursPercentage = (payTimeAndAttendanceProjectLines.TotalWorkingHours/totalWorkingHrs) * 100;
costAllocationTrans.TotalHoursPercentage = (costAllocationTrans.TotalHours + costAllocationTrans.SubContractHours)/100;
row = row+1;
progress.setCaption(strFmt(‘Fetching data from line %1’,row));
costAllocationTrans.insert();
}

info(‘Loading completed’);

}

Thanks for giving reply…