Calculate the Amount Vendor Ledger Entry And Insert Table

vnprocvnproc Member Posts: 41
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

Comments

  • AlexeyShaminAlexeyShamin Member Posts: 80
    Hello!
    Why you don't use Query to get amounts group by vendor and currency?
  • Tony_NCDTony_NCD Member Posts: 32
    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
  • vnprocvnproc Member Posts: 41
    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
  • vnprocvnproc Member Posts: 41
    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_studentnav_student 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)
    ipo24t0x77x5.png



Sign In or Register to comment.