Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

x++ query - sort by summed field

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 224,750 Super User 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 224,750 Super User 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.

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,663 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,750 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans