FlowField For Unapplied Customer Payments

Jonathan2708

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

mariussw

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

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

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.