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; }
No, it's not possible.
Then the alternative is putting aggregated data to a temporary table and sorting this table.
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?
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.
André Arnaud de Cal...
292,111
Super User 2025 Season 1
Martin Dráb
230,934
Most Valuable Professional
nmaenpaa
101,156