4 Replies Latest reply on Feb 25, 2013 7:47 AM by timmcmanus

    Copy a set of records from one related table to another related table

    timmcmanus

      It's late, and I'm stumped approaching this issue.

       

      I have one table "Student". It has a relationship with a table named "Term". "Term" is related to a table named "Term Dates". "Student" is also related to a table named "Scheduled Term"

       

      Here's the logic behind the table setup. Terms are a set of 13 dates. Terms are predefined (think template). "Students" are assigned to the "Term". However, in order to track attendance or to manage a rescheduling, I want to copy the records in "Term Dates" to "Scheduled Term" when the "Student" is assigned their schedule. The user selects the term from a pop-up list, there is a button to "Assign" that term to the student in a layout displaying records from "Student".

       

      The dates in "Term Dates" will not change and serve as a template to expedite assigning the dates to the students. "Scheduled Term" is where I want to manage attendance and rescheduling (change of dates or any date deviation on a per student basis).

       

      All I need to do is copy the related records from the "Term Dates" table into the "Scheduled Term" table. The primary key from the "Student" table will be copied as a foreign key into the "Scheduled Term" table on each row that also contains the "Date" field from the "Term Date" table.

       

      Tables/relationships attached in graphic.

       

      It should be really easy, but I'm out of coffee and need to get some sleep before this drives me insane. Any help/guidance is greatly appreciated.

        • 1. Re: Copy a set of records from one related table to another related table
          Mike_Mitchell

          Hi, Tim.

           

          There are at least two methods for accomplishing the "template to join table" sort of arrangement you have here. One is easier to program, but has some vulnerabilities. The other is more bulletproof, but harder to program.

           

          The easier method:

           

          1) Find all template records you want.

          2) Use Import Records to move them into the join table ("Scheduled Term" in this case).

          3) Use Replace Field Contents to update the key field for the particular parent record (in this case, Student).

           

          Vulnerabilities include error trapping (you can't tell which record errored out on a Replace Field Contents) and being careful with path names (so you don't error).

           

          The more bulletproof method:

           

          1) Assemble a list (return delimited) of either key fields (normally) or dates (in your case) in a variable.

          2) From a layout based on the join table, start a loop.

          3) For each value in the list, create a new record. Set the values as needed (parent key, date / template key).

          4) Go back to original context (or close subordinate window).

           

          The second method allows error trapping and record reversion if there's a problem.

           

          Let me know if this doesn't make sense. HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Copy a set of records from one related table to another related table
            timmcmanus

            Mike:

             

            Thanks for the help.  Pointed me in the right direction.  Here's what I came up with.  Not pretty, but it works.  To complicate things I needed to copy data from the current record and then from Portal A to Portal B.

             

            I wasn't going to retype the script and for the life of me forgot how to get it out of FMP in a reasonable way (I need to stop coding late at night).  I attached the screen shot.

             

            I used a loop and I also used the variable (which is a field, I was lazy) to select the row in the portal.  The logic is to select the field, copy it, select the field in the destination portal, move to the corresponding row, and paste it.  Selecting a field in a portal always defaults to the first row, so using the variable I could "count" down to the proper row in each portal.

             

            And the business reason for all this madness is because the customer wanted a way to create schedules that would be applied to individuals, and then those individuals would have times assigned to them.  Once the assignments were made I needed a method to preserve the original schedule yet allow for rescheduling.  So I created another table to hold the individual records for the user.  There is other data in that table that's specific to each record, so there are other reasons for it.  I was also able to pull record keys to make lookup queries shorter.

             

            The way I have my script created it could in theory work to pull data from mutiple portals on a page, however, organizing portal rows become challenging unless all of the data is predictable (portal rows being the same and 1:1 copying each row).

             

            Thanks again for your help!

             

            Screen Shot 2013-02-25 at 1.41.44 AM.jpg

            • 3. Re: Copy a set of records from one related table to another related table
              Mike_Mitchell

              Tim -

               

              That'll work.

               

              For future reference, you probably want to avoid the use of Copy and Paste. Using the Copy buffer wipes out whatever the user had saved in the buffer prior to executing the script (bad user experience) and also requires the field to be editable on the layout (not always true). You can more generally be served with Set Field or Set Variable.

               

              HTH

               

              Mike

              • 4. Re: Copy a set of records from one related table to another related table
                timmcmanus

                Mike:

                 

                Definitely.  It's a little ugly but the method works.  I should note that I have a field in each portal table called "Total_Records" which is basically a found count, and that keeps the loop honest if the number of records in the portal varies.

                 

                I was somewhat challenged by this because I'm used to copying/moving with C++ or shell script code.  I couldn't find an easy "take this and put it in this" operand.

                 

                Thanks again for the help!