Flow Field in Customer table with last payment and amount

Business Central/NAV Developers Forum
1

I’m trying add two flowfields to the customer tables. I want Last payment amount and last payment date. So basically I need to make a flowfield that looks up the lastest posting date with a document type of “Payment”. I can get each one of those but I can’t get the lastest date, it gets the first one that is in the table. Any thoughts of how I can get the last payment? The SQL would be something like this.

SELECT Amount, Posting Date FROM Cust. Ledger Entry WHERE Customer No. = “No.”, Document Type = ‘Payment’ ORDER BY Posting Date Desc LIMIT 1

2

Did you try with “Max” method in Flowfield Calculation formula?

3

I thought that might be it but I need to see the latest payment date, and the amount that is tied to that. So if I pick the max date, that will work, but how do I pick the amount that is along with that date? Maybe I should just make a function that fills it in and do a FIND(’+’)

4

You will need 3 flowfields. 1. Last Entry No. 2.Last Payment Date 3. Last Payment Amount.

Last Entry No. - Max(“Cust. Ledger Entry”.“Entry No.” WHERE (Customer No.=FIELD(No.)))

Last Payment Date - Lookup(“Detailed Cust. Ledg. Entry”.“Posting Date” WHERE (Cust. Ledger Entry No.=FIELD(Last Payment Entry)))

Last Payment Amount - Lookup(“Detailed Cust. Ledg. Entry”.Amount WHERE (Cust. Ledger Entry No.=FIELD(Last Payment Entry)))

I am not sure if this will work in earlier versions of NAV but should work from 5.x onwards.

If this doesn’t help, then you might have to calculate the values using Global Var and display/use them appropriately.

5

You can do FindLast for Amount

or

you can do as suggested by Raj but include Document Type = Payment in Entry No.

6

Standard NAV: Customer Card → Customer → Entry Statistics.

Here you find the same info, your’e looking for. So - take a look at how it’s done…[:)]

7

Form 302 - Customer Entry Statisitics [Y]

For a flowfield - you can create a new field in the customer table.
Type Flowfield. When the formular would be -(example) for last invoice date.

Max(“Cust. Ledger Entry”.“Posting Date” WHERE (Document Type=FILTER(Invoice),Customer No.=FIELD(No.)))

You can then do this for other fields if you wish. (last payment date, Last credit date, etc.)

PS above he was not sure if it works on eariler versions than 5 - yes it does by the way.