FlowField For Unapplied Customer Payments

Jonathan2708
Jonathan2708 Member Posts: 552
edited 2009-09-21 in NAV/Navision Classic Client
Hi,

I want to be able to show a figure on the Customer Card for the amount of unallocated cash sitting on their account. I thought I could just copy the "Balance (LCY)" flowfield and modify it to just look at document type 'Payment' as follows :

Sum("Detailed Cust. Ledg. Entry"."Amount (LCY)" WHERE (Customer No.=FIELD(No.),Document Type=CONST(Payment),Initial Entry Global Dim. 1=FIELD(Global Dimension 1 Filter),Initial Entry Global Dim. 2=FIELD(Global Dimension 2 Filter),Currency Code=FIELD(Currency Filter)))

But this does not give the correct unapplied amount if there are several payments, some allocated some not. Is it possible to create the flowfield I am after or is the "Detailed Cust. Ledg. Entry" table just not designed to calculate this figure?

Any help appreciated,

Jonathan

Comments

  • mariussw
    mariussw Member Posts: 52
    The filter for the remaining amount on the Customer Ledger Entries is

    Sum("Detailed Cust. Ledg. Entry"."Amount (LCY)" WHERE (Cust. Ledger Entry No.=FIELD(Entry No.),Posting Date=FIELD(Date Filter)))

    Hence by that logic, if you Filter by Customer No. and exclude date you should have it.
    I.e. by omitting the Customer Ledger Entry, you should have remaining amount sum for all entries, but have to be for the specific customer.

    If you try to go by dimensions as in your example, you will run into difficulties.
  • Jonathan2708
    Jonathan2708 Member Posts: 552
    Hi, thanks for the reply. However I am only interested in cash receipts that are unallocated, nothing else so I have to filter by Document Type=Payment. I think it is this filter that causes the problem because adding up all detailed customer ledger entry amounts for this filter does not give you the total remaining amount on all payments - it only works per customer ledger entry, not per customer.

    Jonathan
  • matttrax
    matttrax Member Posts: 2,309
    It is not designed to calculate that via a flowfield, at least not looking at the data in our tables. Think of the details as everything that comes in. Doesn't matter if it is applied to something or not, it has to relfect on the total balance of the customer. So every invoice, payment, credit, penny that involves that customer goes in there.

    The way NAV knows if there is a remaining amount on a document is to sum these details for a specific customer ledger entry (usually one per document posted).

    When a payment is made, it does make a Customer Ledger Entry, though (every detailed entry has to have a main entry). So you could look at the main table, filter on entry type = payment, applies-to doc. no. = 0 and, I believe, get the data you need.
    Matt Traxinger - ArcherPoint

Categories