Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Show Total at the bott...
Microsoft Dynamics AX (Archived)

Show Total at the bottom of Grid from a Display Method column?

(0) ShareShare
ReportReport
Posted on by Rana Anees 1,811

Hii,

I want to show the total or sum of all lines in a grid, but the column is a display method.

I followed this article of Martin Dráb and successfuly showed total of CustInvoiceJour InvoiceAmountMST field in the grid bottom.
http://dev.goshoom.net/en/2015/11/summarized-values-in-ax-form/

But don't understand how to do it with a display method?

Here is the detail..

Form Data source
0257.datasource.png

This is my form. Here Balance column is CustTrans remainAmountMST() display method. I want to show total of Balance column at the bottom of grid.

4150.form.png

Thanks,

*This post is locked for comments

  • Verified answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    The RemainAmountMST() method returns value that equal to (custVendTrans.AmountMST + custVendTrans.ExchAdjustment - custVendTrans.SettleAmountMST) for selected transaction.

    If you want to display sum of all transaction what displayed on the form via display method you should create display method on CustTrans data source and set this method as a property on form control

    display AmountMST remainAmountMST_test()
{
    Query                query = new Query(CustTrans_DS.queryRun().query());
    QueryBuildDataSource qbds = query.dataSourceTable(tableNum(CustTrans));
    QueryRun             qr;
    CustTrans            summedTrans;

    qbds.addSelectionField(fieldNum(CustTrans, AmountMst), SelectionField::Sum);

    qr = new QueryRun(query);
    qr.next();

    summedTrans = qr.get(tableNum(CustTrans));

    return summedTrans.AmountMst;
}


    Screenshot-2018_2D00_11_2D00_12-at-12.42.33.png 

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    I set DisplayMethod to RemainAmountMST and Datasource to CustTrans to RealEdit control on my custom form same like on Customer transactions form, But the remain amount in not updating to the control at the bottom of the grid..

    Am I missing some setting...?

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    The display method is called every time that the form is redrawn.

    https://docs.microsoft.com/en-us/dynamicsax-2012/developer/using-the-display-method-modifier

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Here is debugging... But still I am not understanding how it is calling RemainAmountMST display method... If you could help me to understand the execution..

    7271.debug1.png

    7271.debug1.png

    Thanks,

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Hi Rana Anees!

    This method is on the CustTrans table. Try to put breakpoint and debug.

  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    As problem is solved, but curious to know. There is a same scenario on Customer transactions form. This form has a Balance text box at the bottom of the grid, showing total and update on grid filtration. This Balance text box data source property is CustTrans table and Data Method property is RemainAmountMST. Same scenario.

    I study CustTrans datasource executeQuery method but could'nt understand, how they are populating and updating... I tried in my case but showing 0 value.

    If you cold help me how it works will make my weekend..

    Customer transactions Form:

    updatebalance.png

    updatebalance.png

  • Verified answer
    Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Thank you Martin. You are simply a genius, without you we cant understand AX.

    Here is the complete code.

    public void updateTotalARBalance()
{
    real TotalBalance;
    Query query = new Query(CustTrans_ds.queryRun().query());

    QueryBuildDataSource qbds = query.dataSourceTable(tableNum(CustTrans));
    QueryRun qr;
    CustTrans summedCustTrans;

    qbds.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
    qbds.addSelectionField(fieldNum(CustTrans, ExchAdjustment), SelectionField::Sum);
    qbds.addSelectionField(fieldNum(CustTrans, SettleAmountMST), SelectionField::Sum);

    qr = new QueryRun(query);
    // Run the query
    qr.next();

    // Get the data
    summedCustTrans = qr.get(tableNum(CustTrans));

    // Set the new sum to the control
    TotalARBalance.realValue(summedCustTrans.AmountMST + summedCustTrans.ExchAdjustment - summedCustTrans.SettleAmountMST);

}

    Thanks,

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Your syntax is completely wrong. You're trying to get a field number of AmountMST+ExchAdjustment, SettleAmountMST, which clearly isn't a valid field name.

    Also, you already know how to write the select statement, therefore follow the same approach. You don't have a single SUM function for the aggregation; you have three SUM functions, each for a single field. Therefore call addSelectionField() three times, once for every field you want to sum:

    qbds.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
qbds.addSelectionField(fieldNum(CustTrans, ExchAdjustment), SelectionField::Sum);
qbds.addSelectionField(fieldNum(CustTrans, SettleAmountMST), SelectionField::Sum);
  • Rana Anees Profile Picture
    Rana Anees 1,811 on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Hii Martin Dráb,

    I was really waiting for your reply and am happy you are here.

    I got your point and trying to do like this and having errors....

    qbds.addSelectionField(fieldNum(CustTrans, AmountMST+ExchAdjustment, SettleAmountMST), SelectionField::Sum);

    If you please elaborate the correct syntax...will make my day.

    Thanks again.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: Show Total at the bottom of Grid from a Display Method column?

    Your method uses a hard-coded query, completely independent on the query of the form. Therefore it a user filter the form, it has no impact on your method; it'll always return the same thing.

    If you go back to the block post, you'll see how the query is copied from the form and the copy is aggregated. This is what you must do too, instead of using your hard-coded select statement. The only difference is that you'll add SUM aggregations for all three fields and you'll calculate the balance before setting the value of TotalARBalance.

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,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans