9 Replies Latest reply on Oct 11, 2013 6:38 AM by philmodjunk

    Help adding records from one table to another through a Master table

    DanChronister

      Title

      Help adding records from one table to another through a Master table

      Post

           Here is my need. I have created a database for project management with 3 tables. Projects, Active Tasks and Personnel. You would create a new project, and from that record you could add tasks to the project which saves those tasks as records in the "tasks" table. However I have a need to auto import a list of tasks that will be done on every project. In other words, when you create a new project record, it auto imports certain tasks.

           My thought was to create a new table called "static tasks." Add the tasks that will display on every "new project." My problem lies here. I need those records, from the static tasks, to be auto imported into the "active tasks" table upon project creation so that they all display through the portal I have created on the "Projects" table layout.

           tldr: I need to have records auto imported from a "static tasks" table to the "active tasks" table, associated with the project, upon creation of a new project...

           Oo

        • 1. Re: Help adding records from one table to another through a Master table
          philmodjunk

               Import records can import those standard tasks into your tasks table. Replace Field Contents can update the foreign key field of these newly created records to link them to the new Projects record. This can all be done with a script so that clicking a "new project record" creates the new record in projects, saves the ID of that new record in a variable, imports records from your static tasks table and then uses replace field contents to update the fk field of each new tasks record with the value stored in the variable.

          • 2. Re: Help adding records from one table to another through a Master table
            DanChronister

                 I want to start by saying that I am learning as I go with database creation. So your patience is much appreciated. And thank you for the great response. I understand the vision you laid out. My problem is in the execution since I am in "discovery mode" with the software. When I add import records to the script, I can't seem to find how to specify.... "These Records".. from "This Table." It seems to want an external file (search my hard drive)

                 The second part contributing to my challenge is the Replace Field Contents part and updating the fk to link them to the project. Does there need to be a specific relationships be between this new static tasks table and the active tasks table and or the projects table?

                 Any additional help you could give would be greatly appreciated, along with maybe a sample of what the script would look like for reference. 

                 Thank you all again for sharing your important time with a Noob!

            • 3. Re: Help adding records from one table to another through a Master table
              philmodjunk
                   

                        I can't seem to find how to specify.... "These Records"..

                   Import records will import the current found set of records, so to specify a subset of all the records in a given table, first perform a find to find just those records

                   

                        ...from "This Table." It seems to want an external file (search my hard drive)

                   Well it wants you to select a file because import records can import data from a table in a different FileMaker File, but you can select the very file that you have open such that you are importing records from a different table in the same file. Once you have selected the same file that you have open, you'll get a dialog where you can select the table and then map fields in the source table into fields in the target table.

                   Note that when you select a "table", you are actually selecting a Tutorial: What are Table Occurrences?. Each such table occurrence can have a different found set so if there is more than one table occurrence for the same table, be sure to select the same one as that of the layout where you performed your find to set up your found set of records for the import.

              • 4. Re: Help adding records from one table to another through a Master table
                DanChronister

                     You have been such a help. I now have all of that taken care of. My biggest disconnect now is associating the project id to the newly added tasks through the variable. I have added a screenshot of the current script, which isn't associating the newly created project id to the imported tasks from the static tasks table. I can't get it to save and recall/replace the project id field contents with the variable saved from project.

                • 5. Re: Help adding records from one table to another through a Master table
                  philmodjunk

                       The last step should look like this:

                       Replace Field Contents [Active Tasks::Project ID Match Field ; $$Project ID ]

                       You'll need to select the calculation option to be able to set up the variable as the "calculation" used to assign a value for every newly imported record.

                       And there's no real reason that I can see for making $$Project ID a global variable. You could name it $Project ID and get exactly the same results here.

                  • 6. Re: Help adding records from one table to another through a Master table
                    DanChronister

                         I took your advice and changed it back to the local variable. Perfect, it seems to be working with one caveat. Instead of just assigning the $Project ID to the records that were imported from the static tasks to the new project, it is changing ALL active tasks, no matter their related project, to the new $Project ID. Does their need to be some kind of find in there then replace using the variable?

                          

                         Thanks again, you are amazing!

                    • 7. Re: Help adding records from one table to another through a Master table
                      philmodjunk

                           Replace Field Contents will update every record in your current found set.

                           But immediately after the import records step, the found set should consist of just the records that you imported and thus no other records should be affected by the Replace Field Contents operation.

                      • 8. Re: Help adding records from one table to another through a Master table
                        DanChronister

                             So with this script, the replace field contents continues to replace the contents of ALL records in the active tasks table. Even in it's current order. Any way I could get it to only change the project id of the records imported per occurrence?

                             Thank you again for your time.

                             p.s. anyone feel free to chime in.

                        • 9. Re: Help adding records from one table to another through a Master table
                          philmodjunk

                               Freeze window
                               Go to Layout ["layout based on table into which you have imported records']
                               Replace field contents
                               Go to Layout [original layout]