Hello,
I am trying to create a report to calculate the start balance for each customer(SUM of Amount till the date I filter). How can I do that?
Please help me…
Best,
Sindorela
Hello,
I am trying to create a report to calculate the start balance for each customer(SUM of Amount till the date I filter). How can I do that?
Please help me…
Best,
Sindorela
Refer the Report # 10072 - Customer Statement in NA Version. You can reuse that report if you it matches your requirement.
I don’t have in my database the report with that ID( I am using NAV 2015 Developer Environment). Actually I did the code below:
Customer - OnAfterGetRecord()
CustLedgEntry.RESET;
CustLedgEntry.SETCURRENTKEY(“Customer No.”,“Posting Date”);
CustLedgEntry.SETRANGE(“Customer No.”,“No.”);
COPYFILTER(“Date Filter”,CustLedgEntry.“Posting Date”);
IF CustLedgEntry.FIND(’-’) THEN BEGIN
REPEAT
CustLedgEntry.CALCFIELDS(Amount);
StartBalance:= StartBalance + CustLedgEntry.Amount;
UNTIL CustLedgEntry.NEXT = 0;
CustLedgEntry.RESET;
END;
But it didn’t work.
Report # 116 - Statement. It’s available in World Wide Version. Let me know which country version of db you are using.
hi sindorela
you mean to say opening balance of each customer before the date filter
Try to use
CustLedgEntry.SETFILTER(“Date Filter”,’…%1’,InputDate);
CustLedgEntry.CALCFIELDS(Amount);
StartBalance := CustLedgEntry.Amount;
Hi sachin
Yes, Opening Balance. So I need the Opening Balance for each Customer.
hi sindorela use following code
CustLedgEntry.RESET;
CustLedgEntry.SETCURRENTKEY(“Customer No.”,“Posting Date”);
CustLedgEntry.SETRANGE(“Customer No.”,“No.”);
CustLedgEntry.SETFILTER(CustLedgEntry.“posting date”,’…%1’,startdate);
if CustLedgEntry.find(’-’) then begin
Repeat
CustLedgEntry.CALCFIELDS(Amount);
StartBalance:= StartBalance + CustLedgEntry.Amount;
UNTIL CustLedgEntry.NEXT = 0;
END;
or find my below customization where i have given option on page customer ledger entry to show opening and closing balance of customer
TotAmt:=0;
OpeningBal:=0;
StartDate:=010101D;
EndDate:=CALCDATE(’-1D’,010110D);
“Cust.Ledg.Entry”.RESET;
“Cust.Ledg.Entry”.SETCURRENTKEY(“Cust.Ledg.Entry”.“Customer No.”,“Cust.Ledg.Entry”.“Posting Date”);
“Cust.Ledg.Entry”.SETRANGE(“Cust.Ledg.Entry”.“Customer No.”,“Customer No.”);
“Cust.Ledg.Entry”.SETFILTER(“Cust.Ledg.Entry”.“Posting Date”,’%1…%2’,StartDate,EndDate);
IF “Cust.Ledg.Entry”.FINDFIRST THEN BEGIN
REPEAT
“Cust.Ledg.Entry”.CALCFIELDS(“Cust.Ledg.Entry”.“Amount (LCY)”);
TotAmt :=TotAmt+“Cust.Ledg.Entry”.“Amount (LCY)”;
OpeningBal:=TotAmt;
UNTIL “Cust.Ledg.Entry”.NEXT=0;
END;
EndDate1:=CALCDATE(’-1D’,010121D);
“Cust.Ledg.Entry1”.RESET;
“Cust.Ledg.Entry1”.SETCURRENTKEY(“Cust.Ledg.Entry1”.“Customer No.”,“Cust.Ledg.Entry1”.“Posting Date”);
“Cust.Ledg.Entry1”.SETRANGE(“Cust.Ledg.Entry1”.“Customer No.”,“Customer No.”);
“Cust.Ledg.Entry1”.SETFILTER(“Cust.Ledg.Entry1”.“Posting Date”,’%1…%2’,010110D,EndDate1);
IF “Cust.Ledg.Entry1”.FINDFIRST THEN BEGIN
REPEAT
“Cust.Ledg.Entry1”.CALCFIELDS(“Cust.Ledg.Entry1”.“Amount (LCY)”);
“Cust.Ledg.Entry1”.“Opening Balance”:=OpeningBal;
“Cust.Ledg.Entry1”.“Closing Balance”:=“Cust.Ledg.Entry1”.“Opening Balance”+“Cust.Ledg.Entry1”.“Amount (LCY)”;
“Cust.Ledg.Entry1”.MODIFY;
OpeningBal:=“Cust.Ledg.Entry1”.“Closing Balance”;
UNTIL “Cust.Ledg.Entry1”.NEXT=0;
END;