Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / How to add Order Total...
Finance forum
Unanswered

How to add Order Totals to sales order form, but the fields must be able to filter on.

Posted on by 235

I have the requirement to add the Order Total to the SalesTable form in the grid.  I was able to add the field using a display method. see code below:  However the client has a further requirement that they must be able to filter on this new field.  It is not possible to filter on a display message so I have proposed to create a new form with a view that contains the totals and then they can navigate to the SalesTable from there.

But now I am struggling to create a view with a column displaying the OrderTotal.  Is it possible to do this with a computed column.  Is there a different/better way to do this.  The problem is all the examples of computed columns refer only to values in the current view, what do you do if you want to sum() of column in a different table.

[SysClientCacheDataMethodAttribute(true)]

public static display AmountCur PBFOrderTotal(SalesTable _salestable)

{

AmountCur totalOrder,

invoiceAmount;

SalesTotals salesTotals;

CustInvoiceTrans custInvoiceTrans;

salesTotals = SalesTotals::construct(_salestable);

totalOrder = salesTotals.totalAmount();

select sum(LINEAMOUNTMST) from custInvoiceTrans

     where custInvoiceTrans.SalesId == _salestable.SalesId;

totalOrder = totalOrder + custInvoiceTrans.LINEAMOUNTMST;

return totalOrder;

}

  • Martin Dráb Profile Picture
    Martin Dráb 225,588 Super User on at
    RE: How to add Order Totals to sales order form, but the fields must be able to filter on.

    Sorry, I overlooked that you're querying invoice lines and not order lines.

    The rest of my reply still applies (e.g. that you can't use SalesTotals in computed columns and that replacing it with a custom query may be tricky).

  • Sophia.r Profile Picture
    Sophia.r 235 on at
    RE: How to add Order Totals to sales order form, but the fields must be able to filter on.

    Hi Martin  Are you referring to  CustInvoiceTrans.LineAmountMst above.  If I use the LineAmountMst from CustInvoiceTrans then I do not get totals of the open orders.  I need to show the Total of the order whether it as been invoiced or not.

  • Sophia.r Profile Picture
    Sophia.r 235 on at
    RE: How to add Order Totals to sales order form, but the fields must be able to filter on.

    Hi Martin  Thank you for your quick response.  Orignally the requirement was to show the same value as when you select the Order total on the sales order but they wanted to see it in the grid.  However this calculation does not include the invoiced value, so I added the invoiced values.  I will see if I can solve this with the example you ave provided.

  • Martin Dráb Profile Picture
    Martin Dráb 225,588 Super User on at
    RE: How to add Order Totals to sales order form, but the fields must be able to filter on.

    Here is a simple example using SUM(): Subqueries in views.

    Nevertheless I don't understand your code. Using SalesTotals gives you the most precise numbers, but it's slow and you can't use it in computed columns. Summarizing LineAmountMst is efficient and can be used in computed columns, but it's not necessarily the same thing as a total amount of the order (for example, think about a discount applied to the total order, not lines). But I don't understand why you add these two numbers together.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,989 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans