SBX - Search With Button

SBX - Forum Post Title

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

Microsoft Dynamics AX Forum

Rana Anees asked a question on 8 Nov 2018 5:26 AM
My Badges

Question Status

Verified

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

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.

Thanks,

Reply
Sohaib Cheema responded on 8 Nov 2018 6:09 AM
My Badges

Would not adding the keyword Display in the signature of your  methods, would help to get the needed results...?

Reply
Rana Anees responded on 8 Nov 2018 6:16 AM
My Badges

Hii Shoaib,

I tried but and getting error..

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

or

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

Error: The table CustTrans does not contain the field remainAmountMST.

Reply
Rana Anees responded on 8 Nov 2018 6:17 AM
My Badges

I did't get your point

"Would not adding the keyword Display in the signature of your  methods, would help to get the needed results...?" ..?

Where to add...code example please.

Reply
Sohaib Cheema responded on 8 Nov 2018 6:25 AM
My Badges

I am referring to the blog-post of Martin that you have presented in your original thread. Which means if you copy paste same method and add the keyword display in top line.

about your error, system is saying that CustTrans Table is not having a field named as remainAmountMST. Have you checked if the field exists in CustTrans

Reply
Rana Anees responded on 8 Nov 2018 6:36 AM
My Badges

remainAmountMST is a display method in CustTrans table. Even adding display signature in not the solution.

In Martin example code, he is using a direct field in a table, but I need to use a display method instead of a field.

Is there some other solution....?

Reply
Sohaib Cheema responded on 8 Nov 2018 6:52 AM
My Badges

Well, you cannot use display methods with addSelectionField

You need to adopt some other approach such as x++ method that can return you sum of remaining amount all invoices.

The basic underlying mathematics is as under

Remaining Amount = AmountMST + ExchAdjustment - SettleAmountMST;

Reply
Rana Anees responded on 8 Nov 2018 7:30 AM
My Badges

Hii,

As Shoaib suggested, I am getting sum of all CustTrans rows.  Its showing all Total Balance and showing in control TotalARBalance at grid bottom.

But when I filter in the grid, its not updating....showing the same amount on grid filtration as well.

Please suggest any better way to get it done..

public void updateTotalARBalance()
{
    real TotalBalance;

    CustTrans summedCustTrans;

    select sum(AmountMST), sum(ExchAdjustment), sum(SettleAmountMST) from summedCustTrans
        where summedCustTrans.DataAreaId == curext();

    TotalBalance = (summedCustTrans.AmountMST + summedCustTrans.ExchAdjustment - summedCustTrans.SettleAmountMST);

    TotalARBalance.realValue(TotalBalance);
}

Thanks,

Reply
Martin Dráb responded on 8 Nov 2018 7:43 AM
Suggested Answer

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.

Reply
Rana Anees responded on 8 Nov 2018 8:01 AM
My Badges

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.

Reply
Martin Dráb responded on 8 Nov 2018 8:09 AM
Verified Answer

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);
Reply
Rana Anees responded on 8 Nov 2018 8:38 AM
My Badges
Verified Answer

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,

Reply
Rana Anees responded on 8 Nov 2018 8:49 AM
My Badges

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:

Reply
Suggested Answer

Hi Rana Anees!

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

Reply
Rana Anees responded on 11 Nov 2018 6:57 AM
My Badges

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..

Thanks,

Reply
Rustem Galiamov responded on 11 Nov 2018 12:59 PM
Suggested Answer

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

Reply
Martin Dráb responded on 8 Nov 2018 8:09 AM
Verified Answer

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);
Reply
Rana Anees responded on 8 Nov 2018 8:38 AM
My Badges
Verified Answer

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,

Reply
Rustem Galiamov responded on 12 Nov 2018 3:42 AM
Verified Answer

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;
}


  

Reply
Martin Dráb responded on 8 Nov 2018 7:43 AM
Suggested Answer

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.

Reply
Suggested Answer

Hi Rana Anees!

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

Reply
Rustem Galiamov responded on 11 Nov 2018 12:59 PM
Suggested Answer

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

Reply

SBX - Two Col Forum

SBX - Migrated JS