Find last payment date

[madman411]

Hi all

I'm looking to display to the user when the last payment was made for a lease (from the invoice table) to include date and amount paid. 

The payment table is related to the lease table by a LeaseID. Inside the invoice table I have Type (which defines the record as a "payment" or "charge"), Date, and Remittance, which is what needs to be shown. I'm assuming this would need to be an SQL statement where it gets the latest date from the invoice table where the record is marked as "payment" in the Type field. Then an additional calculation field to show the remittance amount.

Can this be done natively? 

[Ocean West]

In the payment table create a DatePaid field calculation: If ( Payments::Type = "Payment" ; Date )  then in the script just calculate Max ( DatePaid ) setting a field.

You could also do this with a WHILE function and avoid having to add extra calculation fields. 

[comment]

Actually, you do not need to add anything to your schema for this. Just place a one-row portal to the payments table on the lease layout, sort it by date, descending (if the relationship is not sorted that way already) and filter it to show only records whose type is "payment".

 

[madman411]

Both techniques solved the issue. Thank you guys.