Storing Summary Data

[felicity]

I have a 'Customer' table with a related (one table away) 'Tests' table. I want to store in FM summary data (tests per year/month) for each customer. What is the best way to do this - i have explored many but none seem to work.

[The Gardener]

Hello,

I too, have wanted to do this very same thing. Some time ago I came across a useful demo file at formulationspro.com which offers a great many demo files. You can register free for access to the demos.

I hope it is ok. for me to post this demo file, unaltered in any way.

I will quote in part from the readme info which is part of the file:

'FileMaker Pro 8 has added a two new functions; Exit Script and Get(ScriptResult). These scripts are designed to work together with a parent script and a subscript. Together they offer several advantages:

1. Eliminate the need for global storage fields.

2. Eliminate the need for multiple (3 or more) scripts to pass script parameters.

3. Exit Script can acquire and pass data easily from non-related tables. No relationships are required.

4. The Exit Script results can be stored in local and global variables for further processing.

5. Exit Script can pass more than one variable.

6. Can be used to control the logic of the parent or additional subscripts.

These two functions have almost limitless capabilities. Your imagination is your only limitation. This demo offers an introductory look at the use of these functions. The scenario for the demo is an accounting sub-summary report where you want to know the number of transactions and the total amount of the accounts receivable transactions for each month in the first quarter. As you might imagine the A/R table usually has a lot of records. Running a sub summary report could be very taxing on the performance of system. So I decided to create a separate report table that holds just the fields for the report. The Q1 Report (parent script) tells the Accounts Receivable (sub script) which records to find. After the sub script finds the records it passes the result back to the parent script as part of the Exit Script calculation. The parent script then parses the data using the Get (ScriptResult) function and places the result in the appropriate field. Although this is a very basic example, I think you can image the possibility of being able to reduce the report time from 10 or 20 minutes to just a few seconds. Not a bad tradeoff considering the alternative sub summary report.'

I think this will be of use to you.

If I may add a query of my own to expand upon your query - and perhaps some one will offer pointers as to how to do this.

Lets say you have a home budget database which has a journal or ledger file.

Each record entry is an expenditure or an income record, with date and amount. In addition each record entry has these fields:

main category, sub category, subsub category. these are filled in

by using conditional value lists, dependant on your choices of main and sub category.

I am wondering how you could use as this demo file does, exit script and get(scriptresult) with some additional scripting - that is the question for me - to loop [or by some other manner] through a set of records found with in a date range, finding each different kind of record by category and summing them and then go on to do the same for the records by sub category and then subsub category. Hope this might by understandable.

Perhaps you may know how to do this!

ExitScript.fp7.zip

[comment]

I'd suggest you study the "Fast Summaries" technique by Mikhail Edoshin:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

which might also provide a better answer to the original question.

[The Gardener]

Felicity,

Just wondering if you came up with a solution to your question.

Were the replies of any help?