9 Replies Latest reply on Apr 25, 2010 5:56 PM by ErichWetzel

    Need "find open balances" method suggestions



      Need "find open balances" method suggestions


      Using : FileMaker Pro 11, Server Advanced 10, Mac OS 10.6.3.


      Problem : 

      Current method of record keeping, dating to before FileMaker was relational, puts all information about a set of services provided, information about the client, payments and notations into a single record. Currently payments and notations are put in discrete fields with a maximum of twenty entries.  We are frequently over running the available payment and notation space.


      Resolution : I am in the process of shifting to two tables in one database :

      Master, includes items sold and information about the buyer.  Over 25,000 records.

      Financial, includes payments and notations, individually related to a record in the master table by a serial number.  Summary fields give total payments, refunds etc for use by the Master record.


      New problem : 

      Balance Due field is now calculated on the fly in the new setup because of the related fields and records.  Searching for open balances in the single record method would give results in less than a second.  Searching open balances in the related records method requires the system to analyze all 25,000 records for open balances and takes about 3 minutes.  The time delay is a problem due to the way we use the information.


      The issue appears to be that the total of purchases is in a single record in the Master table and each payment is a separate related record in the Financial table. Since the two items cannot be reconciled directly in a calculation it requires fresh calculation on every record repeatedly since all of our users frequently need to find open balance records.


      I am hoping for general suggestions using the structure I have chosen to eliminate the search time to find open balances or alternate structure suggestion to resolve this issue.


      Thanks for your time.



        • 1. Re: Need "find open balances" method suggestions

          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.

          • 2. Re: Need "find open balances" method suggestions

            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.

            • 3. Re: Need "find open balances" method suggestions


              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.



              • 4. Re: Need "find open balances" method suggestions


                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.



                • 5. Re: Need "find open balances" method suggestions

                  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.

                  • 6. Re: Need "find open balances" method suggestions

                    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.


                    • 7. Re: Need "find open balances" method suggestions

                      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.

                      • 8. Re: Need "find open balances" method suggestions

                        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.


                        Thanks again.

                        • 9. Re: Need "find open balances" method suggestions

                          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.