how to get sum value of a field?

Finance and Operations/AX Developer Forum
1

ProjTable Form → Lines → Fee Transaction tab → Revenue field

for revenue field there is a display method called SalesAmount in projrevenuetrans table.

i wannt to get the sum value of the revenue field. can any one tell me how to get the sum value of a display method field?

2

hi,

i have done a display method on the SalesTable form to sum up the line amounts of all saleslines. i wrote the method in the SalesTable table, and made a method field for it on the form. here is the syntax used:

//begin

display Amount totalAmount()
{
SalesLine salesLine;
AmountCur amountCur = 0;
;

while select salesLine
index hint salesLineIdx
where salesLine.SalesId == this.SalesId
{
amountCur += salesLine.LineAmount;
}

return amountCur;
}

//end

i hope this helps somehow :slight_smile:

3

Lola, avoid writing such simple looping select statements if you can just use aggregate function instead (like SUM() in this case)… don’t throw the performance in rubbish bin… [;)]

4

owh well then. i’d appreciate it if you provide me with your alternative method. can you send me the syntax please…

5

This will do the same job:

display Amount totalAmount()
{
SalesLine salesLine;
;

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

return salesLine.LineAmount;
}

6

Do you mean to say that you want to sum all values which are the results of a display method.

7

Yes, Praveen, keep in mind that the code I wrote above DOES NOT answer your request, I just advised a better code for Lola’s idea.

8

I got the solution…

// BP Deviation documented
display real salesamt()
{
ProjRevenueTrans projrevenueTrans;
real revenue;
;
while select projrevenueTrans
where projrevenueTrans.ProjId == this.ProjId
{
revenue += this.salesAmount(projRevenueTrans, dateNull(), dateMax());
}
return revenue;
}

9

Is this possible to use the above code in AOT Report.

Please reply,

10

Yes, You can Write the Display Method at Report Level,But You can write the Same Display Method at table Label And you can drag it on the Report.

11

It helps me. :slight_smile:

Thanks guys

12

Hi Kranthi,

I have a issue related to this … I need Sum of a particular field (Line Amount) only for those record which are currently on the form.

Means If i have two records on form with id 001 and 002, when form is not filtered with any of these id’s then sumAll field to show sum for both of these but when i filter the form with 001 then sumAll field to show sum only for 001.

hope it is clear enough.

Please help on this…

13

Hi

You could do it follow below sample.

SalesLine m_SalesLine;

Amount m_Amount;

;

for (m_SalesLine = SalesLine_DS.getFirst(true)?SalesLine_DS.getFirst(true):SalesLine_DS.cursor();

m_SalesLine;

m_SalesLine = SalesLine_DS.getNext())

{

m_Amount += SalesLine.LineAmount;

}

14

Hi

I am trying to sum the sale qty and group by itemId in forecastSales table for a particualr project.

It works fine when I try in a job.

But I am trying to achieve this in form(Itemforecast). Where I will have two tabs

one with all the items and other new tab to show the grouped items,saleqty, costprice.

Thanks

Varry