13 Replies Latest reply on Jun 22, 2017 2:18 PM by texas0478

    Copy records from one table into another table

    DranLang

      Title

      Copy records from one table into another table

      Post

           Is there a way to copy records from one table to another?  I would like to copy records that are validated to another table, but not sure how to do it?  What’s the best way to do this, scripting or table relationships?

        • 1. Re: Copy records from one table into another table
          philmodjunk

               The best way is often not to copy the records from one table to another in the first place. What problem are you solving by copying the records into a different table?

               That said, there are two typical ways that developers copy a record from one table to another:

               1) Import records can take a found set of records in Table 1 and copy them to a new or matching set of records in Table 2. This works the same way when both tables are in the same file as when the tables are in different files. This can be done manually or in a script.

               2) A script can loop through the found set of records on a layout based on table 1. The script can set a variable to the current record's primary key. The script then changes layouts to a layout based on table 2, creates a new record and sets a field to the value of the variable. Additional variables can copy over additional data or you can set up auto-enter field options and a relationship such that all you need copy over is the primary key and the other values are then copied over from the newly linked related record in table 1.

               After creating the new record and using set field to copy over data from variables, the script switches back to the first layout, goes to the next record and loops around again.

          1 of 1 people found this helpful
          • 2. Re: Copy records from one table into another table
            DranLang

                 I want some of the records in my table 1 to auto populate my table 2 based on set of fields/rule.  Example of which is when the user selects a record number that appears in table 1, it will appear in my table 2.  Perhaps import is the best word to describe it, so that I can also identify what are the fields that should be carried over to my table 2.  If I’ll be scripting it, what’s the first step I need to take?

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

                   I'm not convinced yet that you need to copy over any data save data used to match records in a relationship.

                   

                        Example of which is when the user selects a record number that appears in table 1, it will appear in my table 2.

                   With a relationship linking table 2 and table 1, all that need be done is to copy over the primary key from table 1 to a matching foreign key field in Table 2 so that the two records are linked in a relationship. And this doesn't necessarily require a script to do so as you might be able to achieve the same results with a value list.

                   Whether or not this is the best way to do this depends on details about your design and intended use of this data not yet present in the comments posted to this thread.

              • 4. Re: Copy records from one table into another table
                DranLang

                     The intention of importing the set of records with multiple fields from table 1 to table 2 is to use those records in computing meters in this project.  This is the same project I have in my previous posts, where I need to have a computation for the will look at a value of a field (H_Depth) and subtract it with another value of H_Depth when a IF condition is met (IF D_Start = "True" and D_End = "False"; <COMPUTE FOR METERS>).

                • 5. Re: Copy records from one table into another table
                  philmodjunk

                       But what keeps you from "computing meters in this project." while keeping the data in a single table?

                       It may very well be that you need to do this, but so far I don't see the reason for that.

                  • 6. Re: Copy records from one table into another table
                    DranLang

                         How can I get into another record to get a value of H_Depth to use in my Meters computation?  I can’t use GetNthRecord and Get(RecordNumber).   I made a recurrence table (TABLE2) of my TABLE1 and defined a relationship between the 2 as follows:

                         TABLE1::Job# = TABLE2::Job#

                         TABLE1::construe = TABLE2::D_End

                         TABLE1::SN < TABLE2::SN

                         And all of the fields above are indexed so I can compute for Meters which has a formula Meters = <VALUE OF H_DEPTH WHERE D_END IS “FALSE”>  - <CURRENT VALUE OF H_DEPTH>

                         Which in FileMaker translate to Meters = TABLE2::H_Depth  –  H_Depth

                         The problem is when our engineer changed the formula that D_End should be computed depending on a value of another field (which is unstored), my D_End field can’t be Indexed anymore because of the unstored value of another field which is part of computing/determining the value of D_End.

                          

                         Is there another way to do approach/ solve this problem?  Can a script be possible to solve this, if yes what approach should I use since I’m still not that experience using FileMaker?

                    • 7. Re: Copy records from one table into another table
                      philmodjunk

                           I made a suggestion for dealing with that issue in your original thread. There are ways to keep the value up to date to date and stored with a script and script trigger.

                           Copying data to another table looks to me like it would hit the same issue, the need to match to a record using an unindexed field value so I'm not seeing how copying records from one table to another solves the issue.

                           But please note that I have used this thread to describe two ways to move the data should you choose to do so.

                      • 8. Re: Copy records from one table into another table
                        DranLang

                             I missed your suggestion, sorry for that.  I just finished reading it, if I’ll use a script trigger to run the script will that work if the set of records or data coming in are imported and not manually entered, which script trigger should I use?  The process is that the data will be import into FileMaker and then process it in FileMaker.  I already have the script running to import data into FileMaker.

                        • 9. Re: Copy records from one table into another table
                          philmodjunk

                               Importing cannot trip a script trigger, but if the data is imported, you can use an auto-enter calculation to compute the value during import. The script trigger such as OnObjectSave would only be needed in order to update the value should the value be changed after importing.

                               And if you are doing an update type of import, you may need to use Replace field contents or a looping script to update the data immediately after import.

                          • 10. Re: Copy records from one table into another table
                            wfgclapp

                            philmodjunk wrote:

                             

                                 The best way is often not to copy the records from one table to another in the first place. What problem are you solving by copying the records into a different table?

                                 That said, there are two typical ways that developers copy a record from one table to another:

                                 1) Import records can take a found set of records in Table 1 and copy them to a new or matching set of records in Table 2. This works the same way when both tables are in the same file as when the tables are in different files. This can be done manually or in a script.

                                 2) A script can loop through the found set of records on a layout based on table 1. The script can set a variable to the current record's primary key. The script then changes layouts to a layout based on table 2, creates a new record and sets a field to the value of the variable. Additional variables can copy over additional data or you can set up auto-enter field options and a relationship such that all you need copy over is the primary key and the other values are then copied over from the newly linked related record in table 1.

                                 After creating the new record and using set field to copy over data from variables, the script switches back to the first layout, goes to the next record and loops around again.

                             

                            I just ran across this response while searching for a way to exactly what you seem to me to be describing. I had already created a script that does essentially this (or so I think). I'll give a quick summary of what I'm doing below but the end result is that my loop ends after the first record. I have given you scant information but I simply want to ask a question that I think might be point to the culprit. Does Table 1 have to be related to Table 2?

                             

                            Go to Layout (Table1)

                            Perform Find (I get 89 records)

                            Go to Record (First)

                                Loop

                                     Set Variable ($var=something)

                                     Go to Layout (Table 2)

                                     New Record

                                     Set Field (Table2::field; $var)

                                     Commit Records

                                     Go to Layout (original)

                                     Go to Record(Next; Exit after last)

                            End Loop

                            • 11. Re: Copy records from one table into another table
                              davidhead

                              wfgclapp wrote:


                              Go to Layout (Table1)

                              Perform Find (I get 89 records)

                              Go to Record (First)

                                  Loop

                                       Set Variable ($var=something)

                                       Go to Layout (Table 2)

                                       New Record

                                       Set Field (Table2::field; $var)

                                       Commit Records

                                       Go to Layout (original)

                                       Go to Record(Next; Exit after last)

                              End Loop

                               

                              This script will run into trouble with Go To Layout (original). This will take you back to the layout where the script started rather than where the loop started. Should work if it was Go to Layout (Table1).

                              • 12. Re: Copy records from one table into another table
                                wfgclapp

                                davidhead wrote:

                                 

                                wfgclapp wrote:


                                Go to Layout (Table1)

                                Perform Find (I get 89 records)

                                Go to Record (First)

                                    Loop

                                         Set Variable ($var=something)

                                         Go to Layout (Table 2)

                                         New Record

                                         Set Field (Table2::field; $var)

                                         Commit Records

                                         Go to Layout (original)

                                         Go to Record(Next; Exit after last)

                                End Loop

                                 

                                This script will run into trouble with Go To Layout (original). This will take you back to the layout where the script started rather than where the loop started. Should work if it was Go to Layout (Table1).

                                 

                                Ah!!  Sheesh, yes that did it. Thanks much David!

                                • 13. Re: Copy records from one table into another table
                                  texas0478

                                  1. Install this free plugin

                                  https://www.dracoventions.com/products/2empowerFM/family/sql.php

                                  to gain the ability of using powerful SQL commands like DELETE, INSERT INTO, UPDATE

                                   

                                  2. Copy the structure of the source table into the destination table

                                   

                                  3. write this script:

                                  SET VARIABLE [$SQLtxt; "INSERT INTO DestinationTable (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM SourceTable")

                                  SET VARIABLE ($Result; Value: epSQLExecute( $SQLtxt; "rowSeparator='RETURN' columnSeparator='TAB'"))

                                   

                                  And you are done.

                                  1 of 1 people found this helpful