3 Replies Latest reply on May 23, 2014 8:36 AM by philmodjunk

    Count Dates in A Portal  (FMP 13 Advanced)

    PhilSlaughter

      Title

      Count Dates in A Portal  (FMP 13 Advanced)

      Post

           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”

            

        • 1. Re: Count Dates in A Portal  (FMP 13 Advanced)
          philmodjunk

               We would like to have a field in the portal that showed the number of missed payments since the last payment.

               I'm not sure that I follow why this would be in the portal and not defined in the Loan table and placed on this layout outside of the portal?

               I'm also wondering if you'd find it helpful, if you set up a script that, at the time the loan was approved, generated a record for each scheduled payment in a related table and then you used a field in this table to record when the scheduled payment was actually made. A separate table, with one record for each payment received would likely also be needed as you will have people getting caught up on a loan by sending in payments that pay off more than one payment. That's just a bit of brainstorming here that may or may not be useful to you.

          • 2. Re: Count Dates in A Portal  (FMP 13 Advanced)
            PhilSlaughter

                 If I understand what you are saying it would be like having an amortization schedule in a table for each loan?  That may be a better solution.  My issue is that I'm trying to model historical data and set up an extremely flexible solution.  The payee may miss years worth of payments.  Then, when they do pay, it is applied to interest first then any remainder to principle. Then they may not pay for several more years.  With your scenario I could just sum the interest from the amortization schedule on the date they pay to the prior date they paid and apply the payment.  I could also easily count the number of missed payments between payments (needed for negotiation purposes).  

                 I'm sure there is a script somewhere to create an amortization schedule but if you know of one, that would be helpful.

                 Thank you

                 Phil

            • 3. Re: Count Dates in A Portal  (FMP 13 Advanced)
              philmodjunk
                   

                        it would be like having an amortization schedule in a table for each loan?

                   Or you might think of it as an "electronic payment coupon book".

                   Back in Junior college days I used Fortran to produce an amortization schedule as a class assignment. This can be a looping script, but I forget the math used to produce the values for principle and interest to put into the fields of the schedule.