Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AX query - sorting wit...
Microsoft Dynamics AX (Archived)

AX query - sorting with sum field

(0) ShareShare
ReportReport
Posted on by Sona Jee 430

Hi all,

I am trying to sort by a sumation of field, but its not working.
My sql query is working okay, but X++ not 
SQL - 

select  sum(NETAMOUNT),CUSTACCOUNT
from RETAILTRANSACTIONTABLE
group by CUSTACCOUNT
order by sum(NETAMOUNT) desc

AX query -

            qbds.addGroupByField(fieldNum(RetailTransactionTable, CustAccount));   
            qbds.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);  
            qbds.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);


Suggest me how to achieve this.

Thanks

*This post is locked for comments

  • Suggested answer
    udaY-ch Profile Picture
    udaY-ch 4,624 on at
    RE: AX query - sorting with sum field

    Hi,

    I have tried the same in AX 2012 and job looks like,

    static void job1(Args _args)

    {

       Query                   query = new Query();

       QueryRun                queryRun;

       QueryBuildDataSource    qbdsRetailTransaction;

       RetailTransactionTable  retailTransactionTable;

       qbdsRetailTransaction = query.addDataSource(tableNum(RetailTransactionTable));

       qbdsRetailTransaction.addGroupByField(fieldNum(RetailTransactionTable, InvoiceId));

       qbdsRetailTransaction.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);

       qbdsRetailTransaction.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);

       queryRun = new QueryRun(query);

       while (queryRun.next())

       {

           retailTransactionTable = queryRun.get(tableNum(RetailTransactionTable));

           info(strFmt("Invoiceid %1 -- Net amount %2",retailTransactionTable.invoiceId,retailTransactionTable.netAmount));

       }

    }

    The query seems to be same as what you got to see in the SQL

    SELECT SUM(netAmount) FROM RetailTransactionTable(RetailTransactionTable_1) GROUP BY RetailTransactionTable.invoiceId ORDER BY RetailTransactionTable.netAmount DESC

    I have used InvoiceId instead of customer.

  • Suggested answer
    udaY-ch Profile Picture
    udaY-ch 4,624 on at
    RE: AX query - sorting with sum field

    Hi,

    I have tried the same in AX 2012 and job looks like,

    static void job1(Args _args)

    {

       Query                   query = new Query();

       QueryRun                queryRun;

       QueryBuildDataSource    qbdsRetailTransaction;

       RetailTransactionTable  retailTransactionTable;

       qbdsRetailTransaction = query.addDataSource(tableNum(RetailTransactionTable));

       qbdsRetailTransaction.addGroupByField(fieldNum(RetailTransactionTable, InvoiceId));

       qbdsRetailTransaction.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);

       qbdsRetailTransaction.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);

       queryRun = new QueryRun(query);

       while (queryRun.next())

       {

           retailTransactionTable = queryRun.get(tableNum(RetailTransactionTable));

           info(strFmt("Invoiceid %1 -- Net amount %2",retailTransactionTable.invoiceId,retailTransactionTable.netAmount));

       }

    }

    The query seems to be same as what you got to see in the SQL

    SELECT SUM(netAmount) FROM RetailTransactionTable(RetailTransactionTable_1) GROUP BY RetailTransactionTable.invoiceId ORDER BY RetailTransactionTable.netAmount DESC

    I have used InvoiceId instead of customer. I

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: AX query - sorting with sum field

    Hi Sona Jee!

    Check this link: https://community.dynamics.com/365/financeandoperations/b/howtodynamics365/archive/2017/10/24/how-to-create-view-in-dynamics-365

  • Sona Jee Profile Picture
    Sona Jee 430 on at
    RE: AX query - sorting with sum field

    Hi Rustem Galiamov,

    Can you share some reference in standard D365 and some document, blogs regarding this.

    So for that I will try to do the same.

    Thanks

    Sona jee

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: AX query - sorting with sum field

    You can't do it like in SQL. Try to create view as i mentioned earlier.

  • Sona Jee Profile Picture
    Sona Jee 430 on at
    RE: AX query - sorting with sum field

    Hi Keshav Kumar

    It didn't worked.

  • Keshav Kumar Profile Picture
    Keshav Kumar 220 on at
    RE: AX query - sorting with sum field

    Hi

    I think your sequence is incorrect.

    Try this

    qbds.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);  

    qbds.addGroupByField(fieldNum(RetailTransactionTable, CustAccount));  

    qbds.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: AX query - sorting with sum field

    I think the best way is to create a view with customers and total netAmount by customer and then use this view in your query.

  • Sona Jee Profile Picture
    Sona Jee 430 on at
    RE: AX query - sorting with sum field

    This query for filter top customer account based on sum of netAmount and order by descending.

  • Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: AX query - sorting with sum field

    This query for lookup or something else?

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans