Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

x++ query - sort by summed field

(0) ShareShare
ReportReport
Posted on by 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;
}

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,934 Most Valuable Professional on at
    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.

  • eilev brustugun Profile Picture
    eilev brustugun 5 on at
    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?

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,934 Most Valuable Professional on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,111 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,934 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans