web

You’re offline. This is a read only version of the page.

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... x++ query - sort by su...

Finance | Project Operations, Human Resources, ...

Answered

x++ query - sort by summed field

(0) Share

Report

Report

Posted on by eilev brustugun

5

Hello!

I'm trying to sort my query by the summed field.

It seems like the query does sum the fields, but sorting is not applied.

Here is my code.

Query q;
QueryRun qr;
QueryBuildDataSource qbd;
CustInvoiceTrans tt;
int i = 0;

q = new Query();

qbd = q.addDataSource(tableNum(CustInvoiceTrans));
qbd.addGroupByField(fieldNum(CustInvoiceTrans, ItemId));
qbd.addSelectionField(fieldNum(CustInvoiceTrans,Qty),SelectionField::Sum);
qbd.addSortField(fieldNum(CustInvoiceTrans,Qty),SortOrder::Descending);

qr = new QueryRun(q);

//For testing the query
while(qr.next()){
    tt = qr.get(tableNum(CustInvoiceTrans));
    info(tt.ItemId   " - "   int2str(tt.Qty));

    i  ;
    if(i > 5) break;
}

I also tried using QueryBuildFieldList, but it was still unsuccessful in sorting the table.

Query q;
QueryRun qr;
QueryBuildDataSource qbd;
QueryBuildFieldList qbfl;
CustInvoiceTrans tt;
int i = 0;

q = new Query();

qbd = q.addDataSource(TableNum(CustInvoiceTrans));
qbfl = qbd.fields();
qbfl.addField(fieldNum(CustInvoiceTrans, ItemId));
qbfl.addField(fieldNum(CustInvoiceTrans, Qty), SelectionField::Sum);
qbd.addGroupByField(fieldNum(CustInvoiceTrans, ItemId));
qbd.addSortField(fieldNum(CustInvoiceTrans, Qty), SortOrder::Descending);

qr = new QueryRun(q);
while(qr.next()){
    tt = qr.get(tableNum(CustInvoiceTrans));
    info(tt.ItemId   " - "   int2str(tt.Qty));

    i  ;
    if(i > 5) break;
}

I have the same question (0)

All responses Img

All responses (3)

Answers Img

Answers (1)

Suggested answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: x++ query - sort by summed field

Your code sorts by Qty, not by SUM(Qty). Therefore it won't give you what you want. And you can't order by functions in AX queries.

But it doesn't mean that you can't achieve your goal. Summarize the data in a view and then sort the view.

Was this reply helpful? Yes No
eilev brustugun 5 on at

Like (0)

Report

RE: x++ query - sort by summed field

Thanks Martin.

I have created a view that sorts by the summed field, and it works.

The only problem is that later i probably want to filter the lines by a date range, which means i have to filter the data before applying the sum function. Is this possible to do with views?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: x++ query - sort by summed field

No, it's not possible.

Then the alternative is putting aggregated data to a temporary table and sorting this table.

Was this reply helpful? Yes No

Join the conversation

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1

CA Neeraj Kumar 2,004

#2

André Arnaud de Cal... 857 Super User 2025 Season 2

#3

Sohaib Cheema 548 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans