copying a found set to another table in the same file
While producing a payment list report I need to separate the receipts from the refunds (not only one value but also the tax, discount,.. and a lot of other associated values). The different records are identified with a flag field which carries the type of transaction.
I am using multi files where the pure data tables are in one and my layouts and scripts in another.
I know I can create calculation fields in the original table and use them for reporting but I am looking for a differnet way for the reasons liste d below.
One way I am considering is to create a temporary (scratch) table with the structure and calculation fields suited for the report and copy to it the found set from the original table occurance (which woyld be in the same file).
The reasons for this are:
1- The MOST IMPORTANT REASON is that I want the actual data tables (which are at the customer site) to be unchanged and whenever I change a report in the future I only need to create new calculation fields in the temporary table that I would copy the found set to.
2- The table has typically 10,000 records and the reports are produced for aobut 50 records at a time, so I feel it is not efficient to create these calculations for the whole table.
3- The table structure will almost double, by the way does this impact the storage space and / or response time.
It would be ideal if I know all the requirements at once but in fact the system I am converting from foxpro has been in use for 10 years and I am still getting requests for new reports because every new customer has new ideas.
I WOULD APPRECIATE ANY HINTS ON HOW TO GO ABOUT THIS.