2 Replies Latest reply on Mar 14, 2010 3:19 PM by carolynhaywood

    Showing current balance in a payment record

    carolynhaywood

      Title

      Showing current balance in a payment record

      Post

      I have a payment table connected to a statement table (via statement ID) which is connected to an invoicing table via Invoice Id.

       

      I have a portal based on payments on the invoicing layout in which I post payments and interest.  I want to see a current balance on  for each record but all I get is a new balance based on the current record.  My balance field is a calculation: Principal - sum(amount paid) + sum(interest).

       

      Is there any way I can get this to work?

       

      Thanks,

      Carolyn Haywood

      Beginner

       

      FMP 10 Advanced Mac 10.6.2

        • 1. Re: Showing current balance in a payment record
          carolynhaywood

          Also, I can't get interest to calculate correctly.  It calculates from the principal amount every time when I need it to calculate from the actual amount due (principal - sum(amount paid).

           

          I'm beginning to think this is impossible.

           

          Carolyn Haywood

          Beginner

          • 2. Re: Showing current balance in a payment record
            carolynhaywood

            I finally got this to work.  Here's what I did:

             

            In Invoices, I have a cal field called 'interest",  a "principal balance" field which is 'TotalInvoice - sum (payments::payment).  Interest is calculated from this field.  Then I have a field called "Balance" which is  "principal balance +sum(payments::interest).

             

            In payments, I made interest a lookup (from invoices) number which makes it unique.  Balance forwarded is also a number lookup (from invoices - Principal balance).and therefore unique and  New balance is a cal -  "balance brought forward - payments + interest", so it is a running balance based on a unique number.

             

            Now I have a nice Payment record which posts payments and interest and maintains a running balance.  This becomes an itemized statement of accounts for past due balances.