3 Replies Latest reply on Nov 28, 2014 3:23 PM by philmodjunk

    Copy records from one to another table ?

    FredH

      Title

      Copy records from one to another table ?

      Post

      Hi Experts,


      I have 4 tables : 

             
      • Project
      •      
      • Scenario
      •      
      • Forecasted Revenue
      •      
      • Realized Revenue

      with : 
      Project ---< Investment
      Investment ---< Forecasted Revenue (1 Investment record = 6 Forecasted Revenue records (6 years))
      Project ---< Realized Revenue (1 Project record = 10 Realized Revenue records (10 years))
      Note : The 2 revenue tables are split because they have the same field names but for one they are calculated and for the other they are numbers.

      When I create a new investment for a project, the first thing I do is set the forecasted revenue (budget) for the 6 years (i.e. 2015 -> 2020).
      For the Realized Revenue for that same project, I will have to fill in the actual revenue (actuals) until 2014 but for 2015 -> 2020, I would need to copy the revenue that has been filled in in the Forecasted Revenue table.

      This "copy" from one table to another could be done :

             
      • either directly : when I enter the data in Forecasted Revenue, it copies automatically to Realized Revenue
      •      
      • via a button script : when I push the button, it takes all Forecasted Revenues for all Projects and copies it to the Realized Revenue table

      Note : the Realized Revenue cannot be a "Calculated field" because I should be able to overwrite the budget numbers once the actuals will be available

      Questions : 

             
      • What is the best option?
      •      
      • What should I do to have the setup of the best option working? 

      Thanks in advance for your input!
      Kind regards,

      _Fred_

        • 1. Re: Copy records from one to another table ?
          TKnTexas

          You cannot COPY.  You isolate the records into a FoundSet in the source table.  Go to a layout in the destination file.  Initiate a File Import.  You have to select the file and then the source table.  This is a legacy from pre-VER7.  It would be nice if there was a parameter to the import that did so intra-file.

          • 2. Re: Copy records from one to another table ?
            FredH

            Hi TKnTexas,

            Thanks a lot for your input!
            Finally, I opted for a loop where first I set in variables the fields I want to copy in the Forecasted Revenue table and then I set the fields of the Realized Revenue using these same variables.

            It took some trial and error to make it work and I am sure my script could be largely optimized but it works! :-)

            KR,

            _Fred_
             

             

             

            • 3. Re: Copy records from one to another table ?
              philmodjunk

              Import records can be set up to work to copy data from Table A to Table B when both are in the same file. You go through the same steps to specify the source file and table that you do for importing from a different file, you just select the same file that you already have open. You can even do this for an external table if you set up a table occurrence to the source table using an external data source reference.