6 Replies Latest reply on Jul 10, 2014 10:44 AM by VirginiaPollard

    Script: Transferring data between tables

    VirginiaPollard

      Title

      Script: Transferring data between tables

      Post

           I have three related tables: Managers, Events, and a Join table that joins the two. Each new Event needs to be linked to a subset of the Managers, with a new joint record created in the Join table. I want to automate this process.

           I wrote a script that finds the relevant subset of Managers, and then creates that number of new records in Managers-Events (Join).

           Now I want to loop through the found set of Managers, sequentially copying each Manager ID # and pasting it into each successive new record in the Join Table.

           I have managed to automate this in a cumbersome way by going back and forth between tables and copying and pasting records. The process is clunky and inexplicably leaves some blank records in the Join table. There must be a better way to accomplish this. Thanks for any suggestions.

        • 1. Re: Script: Transferring data between tables
          philmodjunk

               It would appear that you have this relationship:

               Mgrs----<Mgr_Event>-----Events

               Mgrs::__pkMgrID = Mgr_Event::_fkMgrID
               Events::__pkEventID = Mgr_Event::_fkEventID

               Though your table and field names are likely different.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               The copy/paste scripts steps are better not used for this type of action. Copy, will replace any data the user may have previously copied to the clipboard with data copied by your script. When user copies data to the clipboard, opens your database and runs this script, they will be confused and irritated to find that the data they copied has been replaced by other data. There are other scripts steps that can do this same job that do not use copy or paste. (The copy and paste script steps also silently fail to work when the field they are set up to act on is not present and accessible in browse mode when the step is executed by the script.)

               You can use Set variable to set a variable to a field's value in place of copy. You can use set field to set a field to the value of the variable instead of paste.

               It's also possible to copy over your set of manager records with a single import records step. You can then use Replace Field contents to update the imported records in order to link them all to the correct Event record.

          • 2. Re: Script: Transferring data between tables
            VirginiaPollard

                 Thanks for your response. I understand that it’s not a good idea to use copy/paste. I have been able to partially solve my problem, but I am not a sophisticated scripter (obviously) and need more specific guidance to make the whole script work.

                 I have these related tables: Mgrs----<Mgr_Event_Join>-----Events

                 My script creates empty records in Mgr_Event_Join (the table that joins Mgrs to Events.) The number of records is based on the number of Managers in current found set.  I Set Variable [$No_of_Recs; Value: Get (FoundCount)]. The script then gets the name for the current Event (pk Event Name) and enters it (using Set Field) into the Event Txt field of Mgr_Event_Join (fk Event Name txt). These steps work fine.

                 Next, I want to successively get each (pk)Mgr ID# from the found set and enter it into the (fk)Mgr ID Txt field in Mgr_Event_Join. I tried this loop but it doesn’t work:

                 Loop

                             Set Variable [$Mgr ID number; Value: Main Table::Mgr ID]

                             Exit Loop If [$No_of_Recs = Get (FoundCount)]

                             Set Field [$Mgr ID number]

                             Go to Record/Request/Page [Next; Exit after last]

                 End Loop

                 How do I make the loop cycle between the Main table (to get the next Mgr ID number), and the Mgr_Event_Join table to enter the number? Does the script have to keep going back and forth between layouts? …or between tables? Do I need nested loops? Not sure how to do this.

                 Thanks

            • 3. Re: Script: Transferring data between tables
              philmodjunk

                   Why use a loop when you can use import records to import this data into MGr_Event_Join in a single step?

                   I have these related tables: Mgrs----<Mgr_Event_Join>-----Events

                   Does not list any table named "Main Table". Is Mgrs in the above info the same as "Main Table" in your script?

                   

                        How do I make the loop cycle between the Main table (to get the next Mgr ID number), and the Mgr_Event_Join table to enter the number? Does the script have to keep going back and forth between layouts?

                   That is a commonly used method for achieving the result you describe. It's not the only option, however. Not only is it not needed with the Import Records method I've mentioned, but it's often possible to gather all the values in a return separated list in a variable and then loop through the values in the variable instead of switching back and forth between layouts. FileMaker 13's new List type summary field is especially helpful for that approach.

                   Note: the biggest issue that can develop when a script changes layouts is that the layout change can trip a great number of script triggers set on either the layout being exited and also the layout being entered. If such a trigger is tripped and its script is performed, this will take place while the original script is paused and can interfere with the results that you need as well as slowing the execution of your script. In addition to using layouts that don't have any such script triggers set up, you can also set a global variable to a value and use that as a "switch" to keep trigger performed scripts from interfering. But as I mentioned at the start, there are other methods that eliminate the need for this.

              • 4. Re: Script: Transferring data between tables
                VirginiaPollard

                     Apologies about the naming confusion; I was trying to simplify and didn’t proofread carefully enough. I should have said Mgrs instead of Main.

                     I was able to successfully modify my script to use Import Records to enter the manager names in the Mgr_Event_Join table. Thanks for the help.

                     I have one more (hopefully final) question: I need help writing a script step to create a number of new records equal to the variable number I have set. The variable is the number of active managers. I Find the active Mgrs, then Set Variable [$No_of_Recs; Value: Get(FoundCount)]  I want to create a set of blank records in the Mgr_Event_Join table that is equal to $No_of_Recs. I have set up a loop, but I don’t know how to construct the limiting step. I want to say Exit If $No_of_Recs = ???  Everything I have tried loops indefinitely and doesn’t stop when it gets to the current variable number.

                     Thanks.

                • 5. Re: Script: Transferring data between tables
                  philmodjunk

                       Loop
                          Set Variable [$K ; Value: $K + 1 ]
                          Exit Loop If [ $K > $No_of_Recs ]

                        

                        

                       End Loop

                  • 6. Re: Script: Transferring data between tables
                    VirginiaPollard

                         Great! Thanks.