selectsumaxaptax++

Select Sum(NetAmounts) in X++ and display in another Table

I have created a Table named SalesTable_DNC, in which I stored the fields of a specific Customer Account, retrieving the records from the SalesTable in Dynamics365 passing a specific CustAccount from a Dialog and a button.

Everything works fine, but now I need to make a Select sum of all the NetAmounts related to a specific SalesId of the Customer selected, and store the total amount in another record of my SalesTable_DNC called TotalNetAmount. The NetAmount is in the table SalesLine.

I'm having an issue since I can't find much documentation online and I can't find the right approach to solve this. I was trying to store the result of the Sum by doing:

total = select sum(LineAmount) from salesLine where salesLine.SalesId==salesTable_dnc1.SalesId;

which is wrong of course.

Can anyone suggest a better approach? Sorry but I'm totally new to X++ and Dynamics, and I'm stuck in this issue. Thank you

Solution

  • In this simple case you can use a select expression.

    total = (select firstonly sum(LineAmount) from SalesLine 
             where SalesLine.SalesId == salesTable_dnc1.SalesId &&
                   SalesLine.SalesStatus != SalesStatus::Canceled).LineAmount;

    I added the test of cancelled lines as this may otherwise bite you.

    Select expressions do not support joins.