Are you sure you don't need Three tables: Clients, Accounts, Payments?
Just to start the ball rolling (There may be much better ways that I'm not thinking of at the moment), You could add a stored, indexed number field that auto-enters 0 or false.
Use an onRecordCommit trigger to set this field to 1 or True if the account balance is now zero.
To find open accounts, search for 0 in this field. Using the above three table setup, this field would be defined in the accounts table.
You could store a flag field as Phil suggests, and/or store the actual balances.
As you've discovered, accounting systems bog down with unstored calculated fields. The solution is to store the numbers you need. This does require some extra work and care in designing when/how the numbers get updated.
No need for three tables, due to the nature of our business 1 client = 1 account they are essentially the same. Clients are not recurring for us.
Your first thought was the same as mine. I have always had the account status idea essentially in place already with a "paid / open" field. I just never needed to have it be reliably set since I searched for open balances using the master record table::balance due field. I am unhappy with the potential of not triggering the updated balance due to changes in what is purchased since we have around 20-30 fields where purchases are set in each master record. I do not want to set up triggering on that many fields if there is an alternative. This issue has more to do with our business than the structure of the database.
Thanks for the reinforcement on the idea.
I have been working on this database for about 17 years so work is not of concern. Did you have something in mind other than what Phil suggested?
Thanks for the input.
In thinking deeper about this, I don't actually have to worry about triggering on what is in the Master file as long as each new record in the Payment file is properly added to a summary field in the Master record. A small issue to address would be appropriate Master record recalculation in the event of record deletion in the Payment file though. That can be resolved with proper scripting though in my current read on this.
Phil's suggestion is good. I'm saying that you might want to take it a step further and store the actual numbers rather than a simple Boolean value. That way you could not only search for accounts with open balances, but for example accounts with balances greater than a certain amount.
As far as how to implement that, there's no silver bullet, every system is different. The main consideration is how data gets entered, this will determine whether you can use auto-entry options, or need to use script triggers, or non-triggered scripts controlled via the user interface. Part of that is also personal preference in how you design.
I like Fitch's suggestion. Its just as easy to store the current balance as it is to "flag" all accounts as opened or closed and this opens up some nice additional options such s a list view that displays all current account balances that doesn't take forever and a day to refresh.
Phil and Fitch. Thanks to both of you for the information.
I am leaning towards triggering the summary fields, payment, refund etc., value's to be stored values in the master record any time a new payment type entry is made.
It looks like there may be a use for this recent item http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Dynamically-change-a-field/m-p/57026#M44670 ( http://filemakertoday.com/com/entry.php?12-A-lightning-fast-alternative-to-the-Count()-function ). I only quickly read the count alternative but it seems that there may be a use for that idea here. Possibly it could be done by triggering the storage of summary payment etc. values to the newest record and then search as in the suggestion.
I'll be back with results.
Went with mix of Phil's script trigger method and Fitch's save data as needed. Script stores the needed value from related entries back into the master record anytime the field DB::type of transaction or DB::value of transaction is exited or committed suggesting that the value has changed. The script commits the entered records to assure all calculations or summaries are updated before saving to the master record.
Thanks for the help guys.