Sum of Amount for the same document no

sankar

Hi Experts,

If same document number exist more than one time, how can i get the sum of Amount. Say in Vendor Ledger entry it consist of same documents may be twice or thrice how can i find the total amounts of the document..

Thanks in advance.

kriki

You must loop the entries, calculate the total per entry (with CALCFIELDS) and add them up.

sankar

Thanks Kriki,
Can you give some example for it..

kriki

decTotalAmount := 0;
recVendorLedgerEntry.RESET;
recVendorLedgerEntry.SETCURRENTKEY("Document No.");
recVendorLedgerEntry.SETRANGE("Document No.",'Your Document');
IF recVendorLedgerEntry.findset THEN
  REPEAT
    recVendorLedgerEntry.CALCFIELDS(Amount);
    decTotalAmount := decTotalAmount + recVendorLedgerEntry.Amount;
  UNTIL recVendorLedgerEntry.NEXT = 0;
MESSAGE('Total Amount : %1',decTotalAmount);

sankar

Thanks Kriki.
i have tried your code in on after get record of Vendor Ledger Entry, but only one document number is displaying even though lot of documents with same document number is there in table..
can you please tell me why kriki.

Mbad

Code looks correct - maybe your just messing up your filters.. Just use a report for testing this. You wouldnt put this code on a form. If you really need it on the form put in a new text box and make the source expression a function on the table, which returns the result.

sankar

Hi,
i am trying this in report. Vendor Ledger entry as a dataitem. in that only one record is displaying but if i remove the above code it's giving whole Vendor ledger entries in Report. i have inserted the code in on after get record....

Any help is appreciated...

Mbad

decTotalAmount := 0; 
recVendorLedgerEntry.RESET; 
recVendorLedgerEntry.SETCURRENTKEY("Document No."); 
recVendorLedgerEntry.SETRANGE("Document No.","document no."); 
IF recVendorLedgerEntry.findset THEN 
  REPEAT 
    recVendorLedgerEntry.CALCFIELDS(Amount); 
    decTotalAmount := decTotalAmount + recVendorLedgerEntry.Amount; 
  UNTIL recVendorLedgerEntry.NEXT = 0; 

For your report. Use this and make sure your dataitem isnt called recVendorLedgerEntry. In body section of your report just print dectotalamount. Can make recvendorledgerentry a local variable and ditch the reset while your at it.

David_Cox

sankar wrote:

Hi Experts,

If same document number exist more than one time, how can i get the sum of Amount. Say in Vendor Ledger entry it consist of same documents may be twice or thrice how can i find the total amounts of the document..

Thanks in advance.

If you sure that the document number is used for only one Vendor??

Why not just use a Document Number Group Total section, and supress the body sections, no code required.

Create a report with the wizard use the key "Document No.","Document Type","Vendor No." group on the 1st level only "Document No." and have totals for the fields you need, then run and have a look at the group totals, delete sections you do not need and set Body to SHOWOUTPUT(FALSE).

sankar

[solved] Thanks Experts the purpose is solved...

kabir

RecCLE.RESET;
RecCLE.SETRANGE("Document No.","Document No.");
RecCLE.SETRANGE("Global Dimension 2 Code","Vendor Ledger Entry"."Global Dimension 2 Code");
IF Docno <> "Vendor Ledger Entry"."Document No." THEN BEGIN//add this line in your code
IF RecCLE.FINDFIRST THEN BEGIN
REPEAT
RecCLE.CALCFIELDS("Amount (LCY)","Debit Amount","Credit Amount");
TotalBalance +=RecCLE."Amount (LCY)";
CreditBalance1 += RecCLE."Credit Amount";
DebitBalance1 += RecCLE."Debit Amount";
TotalCreditBalance += RecCLE."Credit Amount";
TotalDebitBalance += RecCLE."Debit Amount";
UNTIL RecCLE.NEXT = 0;
Docno := "Vendor Ledger Entry"."Document No.";//add this line in your code
END;
END;


Hope it will help you

kriki

PS: NEVER use FINDFIRST - REPEAT - UNTIL!
PS2:Instead of CALCFIELDS in every loop, use SETAUTOCALCFIELDS.

use FINDFIRST or FIND('-') depending on your needs (see https://mibuso.com/howtos/how-to-work-with-record-variables-version-3)