Small and medium business | Business Central, N...

Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

(0) Share

Report

Posted on by Lino Asmolo Jr

Hi All,

Im new to Business Central al programming and i was tasked to make a report. In the report i was able to list down the invoices of a customer. So my problem is how sum the Amount column to get the total amount. Also, i am able to sum all the amount, but then it ended up having 1 record of invoice. So, im doing it wrong, can you please help to me setup this correctly. Thanks. Here is how i do it:

This results:

To sum all the the Amount, i add this,

And i get the result:

I guess, i was able to sum the Amount, but i ended having one record.

So, how i set this up so I can get all records as well as the Total Amount? or if this is another way to achieve this, i need guide

Thank you.

I have the same question (0)

All responses (13)

Answers (0)

Sort by

Thomas Vetrugno 30 on at

Like (1)

Report
Copy link

Link copied!

Hi Lino,

the trigger called OnAfterGetRecord runs each time a record is retrieved from the data item.

In your case, for each record from cust. ledger entry, your are looping again through entire table. You have to remove the 'repeat until' and the report should works

Was this reply helpful? Yes No
Lino Asmolo Jr 90 on at

Like (0)

Report
Copy link

Link copied!

Hi Thomas Vetrugno,

Thanks for your reply.

You mean like this?

trigger OnAfterGetRecord()

         begin

                if MainItem.FindSet(false, false) then

                    TotalAmount := TotalAmount + MainItem.Amount;

end;

I tried it and i got this result when running the report:

Am i doing it correctly, because the Rows generated: 14242, doesnt stop increasing in number

Was this reply helpful? Yes No
THE Italian Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

This should be enough

trigger OnAfterGetRecord()

begin

TotalAmount := TotalAmount + Amount;

end;

Was this reply helpful? Yes No
Lino Asmolo Jr 90 on at

Like (0)

Report
Copy link

Link copied!

Hi @Duilio Tacconi,

This one doesn't solve my problem, i tried it and the result is, it listed the rows but then the Total Amount field is 0 again.

Was this reply helpful? Yes No
Suggested answer

Mohsin Abbas 525 on at

Like (0)

Report
Copy link

Link copied!

Hi Lino

Please note that Amount is a flowField flowField so you'll need to do CalcFields(Amount) to get the value of amount. Otherwise it'll always come as 0.

Was this reply helpful? Yes No
Mohsin Abbas 525 on at

Like (0)

Report
Copy link

Link copied!

Hi Lino

Please note that Amount is a FlowField so you'll need to do CalcFields(Amount) to get the value of amount. Otherwise it'll always come as 0.

Was this reply helpful? Yes No
Lino Asmolo Jr 90 on at

Like (0)

Report
Copy link

Link copied!

Hi Mohsin,

I tried it, and it just get the first row Amount of the MainItem.

Was this reply helpful? Yes No
Mohsin Abbas 525 on at

Like (0)

Report
Copy link

Link copied!

In which trigger are you writing this code?

Was this reply helpful? Yes No
Lino Asmolo Jr 90 on at

Like (0)

Report
Copy link

Link copied!

Hi Mohsin Abbas,

This code triggered in report object.

This is my code.

I got this result, i just snipped the last page of the report. With this it just get the first Amount record.

I just wanted to sum all the Amount column of the report. How do i acheive it? How do i get the total Amount?

Was this reply helpful? Yes No
Suggested answer

Mohsin Abbas 525 on at

Like (0)

Report
Copy link

Link copied!

There are multiple ways to do that:

1. CurrReport.CreateTotals in OnPreDataItem Trigger(docs.microsoft.com/.../createtotals-function--report-)

2. In RDLC Layout, simply add a field for sum(Amount) it will give you the total.

Was this reply helpful? Yes No