How can I sum up "Amount (LCY)" from Cust Ledg Entry table by code programming?

Business Central/NAV User Forum
1

Dear all,

I have a question as I put in the subject.

Could you please help me?

Right now, I try to create a report which contains data of “Customer No.”, “Customer Name”, " Amount (LCY)" and “Credit Limit (LCY)”.

Q1. How can I sum up “Amount (LCY)” from Cust. Ledg Entry table by code programming?

I want to show “Amount (LCY)” as Net balance not like Gross.(I need to make “Amount (LCY)” as just 1 line.)

Q2. How can I show the calculated balance From Q1 in Customer section body?

Regards,

Kz

2

Hi KKaz,

Try using the following code ,

For Q1,

In Customer - OnAfterGetRecord

// START
CLEAR(Amount); // Declare Amount as a Decimal
CustLedgerEntry.RESET; // Declare CustLedgerEntry as a Record → Customer Ledger Entry (Table 21)
CustLedgerEntry.SETRANGE(“Customer No.”,“No.”);
IF CustLedgerEntry.FINDSET THEN BEGIN
REPEAT
CustLedgerEntry.CALCFIELDS(“Remaining Amt. (LCY)”);
Amount += CustLedgerEntry.“Remaining Amt. (LCY)”;
UNTIL CustLedgerEntry.NEXT=0;
END;

// STOP

And Display ‘Amount’ variable in the customer Body

For Q2

In Customer - OnAfterGetRecord

// START
CLEAR(Amount); // Declare Amount as a Decimal
CustLedgerEntry.RESET; // Declare CustLedgerEntry as a Record → Customer Ledger Entry (Table 21)
CustLedgerEntry.SETRANGE(“Customer No.”,“No.”);

CustLedgerEntry.SETRANGE(“Posting Date”,StartDate,EndDate);// Here u can enter the start Date and the End Date for your Q1
IF CustLedgerEntry.FINDSET THEN BEGIN
REPEAT
CustLedgerEntry.CALCFIELDS(“Remaining Amt. (LCY)”);
Amount += CustLedgerEntry.“Remaining Amt. (LCY)”;
UNTIL CustLedgerEntry.NEXT=0;
END;

// STOP

Hope this helps…

Rgds,

Sid

3

Hi, Sid

Thank you for your help.

The solution you provided me worked perfectly!![:D]

May I ask you additional question?

How can I add a FILTER to above Q1 calculation which performs like DATE Filter?

Now I want to show an amount which calculated as “First day of the system date” through “a Date which entered by user”.

I want to narrow down the date by POSTING DATE.

Table Example(3 examples for calculation):

Date Customer No. Amount

01/01/2001 1169G10000 100 <-------Let’s say This is the the first day in the system( 0D )

01/01/2004 1169G10000 100

01/01/2005 1169G10000 100

If a user entered 01/01/2004 or other date like 12/31/2004,

I want NAV to calculate amounts From “0D … 01/01/2004” or “0D … 12/31/2004”.

The result of calculation will be 200.

Appreciate your help.

Regards,

Kz

If a user want to check balance by November 25th, 2010.

4

CustLedgerEntry.SETRANGE(“Posting Date”,StartDate,EndDate);

replace the above code with

CustLedgerEntry.SETRANGE(“Posting Date”,0D,EndDate); // EndDate will be entered by User in request form

5

Thanks for your reply Mohana.

Actually, The Source Code for Q1 Provided by Sid worked, but the Source Code for Q2 did not work.[:(]

If I add the code “CustLedgerEntry.SETRANGE(“Posting Date”,StartDate,EndDate);” or "CustLedgerEntry.SETRANGE(“Posting Date”,0D,EndDate); "

all of the calculated amount would be ZERO.

Anything wrong with my Date Filter entry?

RIght now, I set EndDate as a variable in “C/AL Globals”, and I enter only “10/10/10” in Date Filter.

Could you tell me if anything wrong with my setting???

Regards,

Kz

6

You should assign 101010 to EndDate like

EndDate := 101010D;

Before CustLedgerEntry.SETRANGE(“Posting Date”,0D,EndDate);

Dont give it in DateFilter…

I mentioned in my previous post that EndDate should be given from Request Form…

7

Hi, Mohana

Thank you so much for you help.[Y]

I could solve all of the problems I had.[:D]

Regards,

Kz

8

welcome…

Please Don’t forget to VERIFY the post(s) that solved your problem.

9

welcome[Y]

Rgds

Sid