Count Dates in A Portal (FMP 13 Advanced)
We’re trying to put together a loan payment layout. We have two tables “Loans” and “Loan Payments” joined on a “LoanID” match field. The Loan Layout has a portal showing “Loan Payments” for each LoanID.
We would like to have a field in the portal that showed the number of missed payments since the last payment. And then be able to sum the missed payments to calculate an accrual.
I’m assuming we need to set up a field in the “Loan Payments” table that has a calculated value that counts the number of months between the current records “PayDate” and the previous records “PayDate”?
I found this formula to calculate the number of months:
(Year(Date2 + 1) - Year(Date1 + 1)) * 12 + (Month(Date2 + 1) - Month(Date1 + 1)) - If (Day (Date2 + 1) = Day (Date1), 0, If (Day (Date2 + 1) < Day (Date1+1), 1, 0))
Our question is how to get the field calculation to find the prior record’s “PayDate” for a given “LoanID”