Progressive SUM Flowfield

Alx · 2005-12-19

Hi everyone,

What I want is a column on e.g. Cust. Ledger Entries that will display the progressive SUM of Amount for a specific Customer, up to that Entry No.
I tried to do it but couldn't implement the "Up to Entry No.", so I wrote the following code to display those totals on a customer Invoice report:

CustLedgEntry.SETRANGE("Customer No.", "Sales Invoice Header"."Sell-to Customer No.");
CustLedgEntry.SETRANGE("Document No.", "Sales Invoice Header"."No.");
IF CustLedgEntry.FIND('+') THEN BEGIN
  LastEntryNo := CustLedgEntry."Entry No.";
  CustLedgEntry.RESET;
  CustLedgEntry.SETRANGE("Customer No.", "Sales Invoice Header"."Sell-to Customer No.");
  CustLedgEntry.SETFILTER("Document No.", '<> %1', "Sales Invoice Header"."No.");
  CustLedgEntry.SETFILTER("Entry No.", '< %1', LastEntryNo);
  IF  CustLedgEntry.FIND('+') THEN
    PrevEntryNo := CustLedgEntry."Entry No.";
END;

CustLedgEntry.RESET;
CustLedgEntry.SETRANGE("Customer No.", "Sales Invoice Header"."Sell-to Customer No.");
IF CustLedgEntry.GET(LastEntryNo) THEN REPEAT
  CustLedgEntry.CALCFIELDS(Amount);
  NewBalanceAmount := NewBalanceAmount + CustLedgEntry.Amount;
UNTIL CustLedgEntry.NEXT(-1) = 0;
IF CustLedgEntry.GET(PrevEntryNo) THEN REPEAT
  CustLedgEntry.CALCFIELDS(Amount);
  OldBalanceAmount := OldBalanceAmount + CustLedgEntry.Amount;
UNTIL CustLedgEntry.NEXT(-1) = 0;

It works allright but I need 2 loops over Cust.LedgEntry, Which could become very slow. In addition, the results are limited to within the report. Is there ANY way to implement the loopy part of my code using a flowfield?

Thanks in advance,

Alx

ara3n · 2005-12-19

I think you should use cust. detail ledger entry. Once you find your entries, set the filters and do a calcsum. You will need to do create a key and sumindex the amount field.

Alx · 2005-12-19

I tried to create a FlowField on Cust. Ledger Entry as the SUM of DetCustLedgEntry.Amount, with a Table Filter of :

Entry Type=FILTER(Initial Entry|Unrealized Loss|Unrealized Gain|Realized Loss|Realized Gain|Payment Discount|'Payment Discount (VAT Excl.)'|'Payment Discount (VAT Adjustment)'|Payment Tolerance|Payment Discount Tolerance|'Payment Tolerance (VAT Excl.)'|'Payment Tolerance (VAT Adjustment)'|'Payment Discount Tolerance (VAT Excl.)'|'Payment Discount Tolerance (VAT Adjustment)'),

Customer No.=FIELD(Customer No.),

Posting Date=FIELD(Date Filter)

But the result I get is the same on all Customer Ledger Entries, and is probably equal to the SUM of Amount. What I want is a FlowField that displays the SUM of Amount up to the Date of the current Ledger Entry or, more correctly, up to the current Cust. Ledger Entry... :x

ara3n · 2005-12-19

you need to create a flowfilter on cust. ledger entry. Called "Entry No. Flowfilter" and in your flowfield set cust. ledger entry no. = Field(Entry No. Flowfilter);

Alx · 2005-12-20

Thanks a lot!!!!
It took me a while but it works, and now I understand FlowFields and FlowFilters better!

Merry Christmas

Progressive SUM Flowfield

Comments

Categories