Calculate the Amount Vendor Ledger Entry And Insert Table

vnproc

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;

I cannot insert data into the table. Please help me with 1 solution.
Thanks very much

AlexeyShamin

Hello!
Why you don't use Query to get amounts group by vendor and currency?

Tony_NCD

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

vnproc

AlexeyShamin wrote: »

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

vnproc

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)

nav_student

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)