Need "find open balances" method suggestions
Using : FileMaker Pro 11, Server Advanced 10, Mac OS 10.6.3.
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.