5 Replies Latest reply on Mar 4, 2013 1:24 PM by jaytyo

    Creating records in one table based on records in another table

    jaytyo

      A little help please for a FileMaker newbie who is feeling the urge to write some SQL. I have a global variable, lets call it $$PID. I want to query table 1 with something like this:

       

      select field1, field2 from table1 where table1.PID = $$PID. I have put that into a custom function with the ExecuteSQL function step.

       

      I want to take those returned rows plus another global variable (lets call it $$InstID) and insert them into table2 which has those three fields and then show a list style layout of the records.

       

      I will be executing this from a button on another layout where the $$InstID is created.

       

      Any ideas how to call the function or parse and insert the records into table2? I know I can also do a Perform Find to get the records from table1, but don't know how to get the data into table2 especially considering the two fields from the SQL above are the only two matched fields.

       

      Thanks,

       

      Jay

        • 1. Re: Creating records in one table based on records in another table
          nickchapin

          I'm not entirely clear on your chosen method; are these tables related? If so that's probably the best route to create new records. If not then after your SQL call you can loop through the result getting each value in turn and creating a record. This, obviously, would be performed via a script. BTW: you don't have to call a CF to execute your SQL; use a Set Variable within your script.

           

          Hope that helps!

          • 2. Re: Creating records in one table based on records in another table
            taylorsharpe

            First, you can do this faster just without ExecuteSQL because you can import directly from one FileMaker table into another one.  But lets assume there is a reason that ExecuteSQL you are using does something not natively available in FileMaker without SQL such as find related records without a FileMaker relationship.  There are a lot of cool advantages of using ExecuteSQL, but one disadvantage is that it does NOT write to a table, delete records, etc.  All it does is return the result as a text variable.  So for you to do what you are saying... you have to return the result to a text variable (think of it as an array) and then write a looping script that goes through the text variable pulling out records to put into the new table.  Once you have the new text variable, you loop through it pulling out records. 

             

            Here is an example.  I created two tables with 3 fields each (one text, one number, one date). 

             

            Tablel_1

            Field_1 (text)

            Field_2 (number)

            Field_3 (Date)

            Table_2

            Field_1 (text)

            Field_2 (number)

            Field_3 (Date)

             

            Then I created the following script to copy things via an ExercuteSQL from Table_1 to Table_2

             

            jay.jpg

             

            I'll attach the file for you to look at it. 

            • 3. Re: Creating records in one table based on records in another table
              jaytyo

              Taylor,

               

              That looks like what I was thinking about.  But I am interested in your thought that it is faster (more efficient?) to import from table 1 to table two.

               

              I have a find that gets the records I want from table 1.  There are fourteen fields in table 1, I only want 3 or 4.  How do I copy the records from table 1 to table 2 which also has about 14 fields.  Only the 3 or 4 fields match, plus I want each record to have the global variable ($$PInst, above).

               

              Thanks,

               

              Jay

              • 4. Re: Creating records in one table based on records in another table
                taylorsharpe

                Jay,

                 

                Just do a find of the records you want to import, switch layouts, and do an import mapping the appropriate fields.  Then insert $$Plnst into the field you want and do a Replace Field Contents to save it across all of the imported records.  I think that would take care of it for you if I understand what you want.  Let me know if that works. 

                • 5. Re: Creating records in one table based on records in another table
                  jaytyo

                  It appears this worked, though I have not a huge amount of testing.

                   

                  Thank you very much for your helpful comments.

                   

                  Jay