2 Replies Latest reply on May 21, 2010 12:12 PM by wyrd9

    Forwarding a balance



      Forwarding a balance


      Hi all,


      I'm using FileMaker Pro 10 in Mac OS 10.5.8.  I'm not very experienced with either FileMaker or databases (or programing/software development of any kind).  As of right now, the scheduling/billing solution I'm developing is single-user and is not shared--although it is in use (populated with hundreds of records).


      I am having trouble forwarding a balance from one record to another within the same (Invoices) table.  I created a new blank database in order to troubleshoot this problem.  It is currently comprised of 3 main tables.  The Invoices table is related to the Appointments table by Account ID (unique) and Quarter (text field representing an invoice period--all Appointments have a Quarter designation, e.g., First Quarter, 2010, and each Invoice is ).  The Appointments table is related to the Accounts table by Account ID.


      In the Invoices table are the following fields:  

      Balance this Quarter (Number; Auto Calculation = a Summary field)

      Forwarded Balance (Number; ?(help))

      Total (Number; Calc = Balance this Quarter + Forwarded Balance)

      Balance to forward (Number; ?)


      I can't for the life of me figure out how to forward a balance from an Invoice record marked Fourth Quarter, 2009 to another record (same Account) marked  First Quarter, 2010. I currently have a series of Payments fields in the Invoices table...do I need to have a separate table to make the balance forward??  


        • 1. Re: Forwarding a balance

          You can either lookup the previous balance or use it over a relationship. The former will not update automatically if you edit prior entries, the latter will get progressively slower as the number of records grows.


          In either case, you need a relationship to link to the "previous" record. I didn't quite get your description, but it should be something like:


          Invoices::AccountID = Invoices 2::AccountID


          Invoices::InvoiceID > Invoices 2::InvoiceID


          with records from Invoices 2 sorted by InvoiceID, descending.

          • 2. Re: Forwarding a balance

            Thanks, comment.  I used the first option -- looking up Invoices 2::Balance to Forward from the context of Invoices after relating my tables in the way you suggested and it works in my test solution (which is pretty bulky by now)!