4 Replies Latest reply on Sep 8, 2011 7:22 PM by med

    copying a found set to another table in the same file

    med

      Title

      copying a found set to another table in the same file

      Post

      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.

        • 1. Re: copying a found set to another table in the same file
          philmodjunk

          I really wouldn't go this way unless I really had to. You can use import records to copy the current found set in one table to another, but this will be much slower for many reports than just performing a find on the original table to display the needed data for your report. Yes this does require issues for deploying updates, but those issues will happen fairly infrequently and can be fully controlled by update scripts so that the update takes place automatically after the user clicks a button, but with the alternative, your users will be confronted with a significant delay every time they pull up a report and that will occur much more often.

          • 2. Re: copying a found set to another table in the same file
            med

            Thank you Phil for the reply. However the thing is that I do not want to find each kind of transaction at a time.

            I will give an example: I want to print a receipt audit list and want to show the amount paid in one column if it is a receipt, and in another if it is a refund, along with that will be the tax associated etc. I also want to have a grand total where the total refunds will be subtracted from the total receipts. This can be achieved easily with calculation fields but instead of having a calculated field for a 1000 records table I am thinking that copying the 50 records of a found set to a temp table which have the calculation fields.

            I will try your way to save on performance but I need an answer to two qustions:

            1. Howw can I import records from a table to another in the same file, any way.

            2. For updates, can I change a table structure using a script or do I have to send another table with the new structure, then write a script to import to it the records then delete the old table (how) then rename the new one to the original name (how).

            Thank you.

            • 3. Re: copying a found set to another table in the same file
              philmodjunk

              1) was already answered in my last post. Import records can copy the found set from one table to another--same file or different files makes no difference here, import records can do both.

              2) you cannot change table structure with a script, you deploy a new copy of the file and use a script to import the records from the original file and to also update serial Id fields with the correct values to be consistent with the imported records.

              You can arrange data like you describe into columns using filtered portals.

              Many totals such as grand totals can easily be set up with summary fields.

              • 4. Re: copying a found set to another table in the same file
                med

                Thank you. I have all the answers now.