Forwarding a balance
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??