Unstored Calculations Taking Forever....
I have a database with quarterly financial records dating back to 2007 for approximately 8000 institutions. I download updates from a governmental regulatory agency. To make it easy on myself, I modeled my table structure after the way they offer the data files. This leads me to the following table structure:
Table1: Contains the name, unique id, cycle data, basic contact information of reporting institutions, and a set of calculations that determine financial ratios, and a set of calculations that determine a score based on the ratios
Table2: Contains the unique id, cycle date, and financial data for reporting institutions
The tables are related via the id and cycle date
Each quarter I upload the latest versions for both tables, appending the new data to the appropriate tables in the database. As an aside, the reason I choose to append rather than update the data is so that past quarter calculations are correct.
I have a third table that I use to average the scores for each quarter. The table contains a unique record id, cycle date, and average score calculation for each quarter. This table is related to Table 1 via the cycle date.
The idea is that I can import the latest data and review the scores and performance for any given institution in the database via Table1, and then enter the cycle date in the Table3 to calculate and view the score averages - and then graph the data.
This works as intended - however because Table3 is based on an underlying set of calculations and scores based on nested If..Then statements, any changes to the layout based on Table3 (such as resorting a column) takes about 5 minutes or more to finalize as Filemaker recalculates all of the score average data.
I was thinking that I could try storing Table3 calculations in a fourth table to use for analysis, graphing, reporting, etc. but was not sure how best to do that.
Any recommendations to speed up the calculations, or store them in a way that makes it easy to use them without perpetual recalculations?
Filemaker Pro 11
MacBook 2.2 GHz Core 2 Duo with 4 GB RAM