#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Calculate the Amount Vendor Ledger Entry And Insert Table

VietNamMember Posts: 38
Hi Everybody,

I create a table with the following fields: Table Name: Cal Balance Vendor

1. Vendor Code
2. Ending Date
3. Currency Code
4. Total Amount
5. Line No

I use this table to save the results of the calculations at the end month. The purpose of reporting liabilities of Vendor.

* I'm new 1 report on the data source Cal Balance Vendor use calculation by Ending Date
```VendLedEntries.SETCURRENTKEY("Vendor No.","Posting Date","Currency Code");
VendLedEntries.SETRANGE("Vendor No.","Vendor Code");
VendLedEntries.SETRANGE("Posting Date",0D, EndingDate);
VendLedEntries.SETRANGE("Currency Code","Currency Code");

IF  VendLedEntries.FINDSET THEN BEGIN
REPEAT
VendLedEntries.CALCSUMS(Amount);
TotalBalAmount:=VendLedEntries.Amount;

UNTIL VendLedEntries.NEXT =0 ;
LineNo+=1000
"Cal Balance Vendor".INIT
"Cal Balance Vendor"."Amount":=TotalBalAmount;
"Cal Balance Vendor"."Ending Date":=EndingDate;
"Cal Balance Vendor"."Currency Code":=VendLedEntries."Currency Code";
"Cal Balance Vendor"."Line No":= LineNo;
"Cal Balance Vendor".INSERT
END;
```

Thanks very much

• Member Posts: 37
Hello!
Why you don't use Query to get amounts group by vendor and currency?
• Member Posts: 7
Given your table definition, my best guess is that the Vendor Code is your key, unless you defined the Line No as your key. Regardless, I would say the Vendor Code is important but you left that out in your code below. Nevertheless, I'm not sure why you chose this method but you're probably over complicating this by creating a separate table to store your report values.

"Cal Balance Vendor".INIT
"Cal Balance Vendor"."Amount":=TotalBalAmount;
"Cal Balance Vendor"."Ending Date":=EndingDate;
"Cal Balance Vendor"."Currency Code":=VendLedEntries."Currency Code";
"Cal Balance Vendor"."Line No":= LineNo;
"Cal Balance Vendor".INSERT
• VietNamMember Posts: 38
Hello!
Why you don't use Query to get amounts group by vendor and currency?

I know the Query, but when the Currency Code group is on the Query I won't be able to call the Ending Date on report. When can you give me samples

Thanks
• VietNamMember Posts: 38
Tony_NCD wrote: »
Given your table definition, my best guess is that the Vendor Code is your key unless you defined the Line No as your key. Regardless, I would say the Vendor Code is important but you left that out in your code below. Nevertheless, I'm not sure why you chose this method but you're probably over complicating this by creating a separate table to store your report values.

"Cal Balance Vendor".INIT
"Cal Balance Vendor"."Amount":=TotalBalAmount;
"Cal Balance Vendor"."Ending Date":=EndingDate;
"Cal Balance Vendor"."Currency Code":=VendLedEntries."Currency Code";
"Cal Balance Vendor"."Line No":= LineNo;
"Cal Balance Vendor".INSERT

Thank you for replying. On this table, I use 2 Key (Vendor Code, Currency Code)
• Member Posts: 175
If "Vendor Code" is part of the PK shouldn't you add it to the code?
"Cal Balance Vendor"."Vendor Code" := ???

I believe you can use the CALCSUMS without the REPEAT loop (example below)