7 Replies Latest reply on Jun 28, 2014 10:54 AM by OlivierV

    Script to Automatically Create Related Records in Related Table

    OlivierV

      Title

      Script to Automatically Create Related Records in Related Table

      Post

           Hello ! 

           I'm very new to databases in general and Filemaker in particular. I saw a lot of videos including some lynda.com tutorial and was able to achieve a lot. I'm just stuck on a particular problem as my knowledge of scripts is very (very) limited. I looked at this forum for the solution and couldn't find one so i decided to create a new topic, i hope you won't mind :)

           PROJECT

           The project is a little like a role playing game for Employees. The goal is to have Missions that every employee can do, whenever a mission is accomplished, the employee gets some experience points and they will get higher levels etc. So I have a table with EMPLOYEES, a table with MISSION and a related table called VALIDATIONMISSION (it's linked via MissionID and EmployeeID key fields) where I can assign each mission to each employees. The problem is I have to assign manually each mission to each employee which is a tedious process. Not to mention that if I ever add an employee or a mission I have to do it all over again and assign each to each. It's a nightmare as I have about 100 employees and over 300 missions.

           SOLUTION

           The ideal scripts would do the following.

           - Automatically create records in the VALIDATIONMISSION table so that every MISSION is assigned to every EMPLOYEE.

           - Be able to update. For example if a new mission is created it would be automatically create records to assign it to every employee without changing the existing records. Also if a new employee is added to the database he/she should have all the mission automatically assigned as well.

           Maybe I need 2 scripts. One to create everything from the database I already have and another that would just be an Update script. I just don't know :/

           WHAT I TRIED

           - From what i could gather I created a script based on copying and pasting some fields and i could create an embryo of what I'm looking for. It worked. Sort of. I was able to assign every mission to a single employee using this method and a loop but that's it. And if i run the script again obviously it created duplicates.

           It's just a very clumsy solution so that's why I'm turning to you today :)

           Is it something that is possible or am I looking at it all wrong? I hope this was clear enough, as I said i'm new to the semantics and all.

           Thanks very much for your help !

            

        • 1. Re: Script to Automatically Create Related Records in Related Table
          philmodjunk

               There's not enough key detail here in the design of your database and what script steps you used to provide a detailed response.

               In general, there are two ways to create records in the related table: Neither should use the copy and paste scripts steps. While those can work, the mess up any data copied by the user to the clipboard prior to running the script and this irritates and confuses users. It is also easily avoided by using different script steps that do not use copy and/or paste to do so.

               method 1:

               If you have this relationship:

               Parent-----<Child    (one parent links to many child records)

               Set Variable [$ID ; value: Parent::__pkParentID ]
               Go to Layout ["Child" (Child) ]
               New Record/Request
               Set Field [Child::_fkParentID ; $ID ]
               Go to Layout [original layout]

               Will create one child record linked to the current Parent record if performed from a layout based on "Parent".

               Method 2:

               If you have this relationship:

               Parent-----<Child    (one parent links to many child records)

               Set Field [Child::anyField ; any not null value can go here ]

               Will create one and only one related Child record if performed from a "Parent" layout and only if you enable "allow creation of records via this relationship".

               If you run this second script a second time for the same Parent record, no additional related record is created. It only creates a related record if one does not already exist.

          1 of 1 people found this helpful
          • 2. Re: Script to Automatically Create Related Records in Related Table
            OlivierV

                 Hi PhilModJunk,

                 Thanks very much for taking the time to help me, it's much appreciated :)

                 I attached a screenshot of my tables (it's partly in French forgive me), I hope it will be a little clearer.

                 As for the two examples you give me I fail to see how it would work exactly regarding my specific case (where many Missions are linked to many Employees). I guess there are some steps that i just don't see with my limited knowledge of scripts and variables.

                 Thanks for conforming that copy and paste is a bad idea :)

                 I apologize again for my beginners level in the matter.

            • 3. Re: Script to Automatically Create Related Records in Related Table
              OlivierV

                   Let me add an illustration of what I'm trying to achieve.

                   I have 2 Tables, Employees and Missions. On this example I choose 3 employees and 3 missions. I'd like for the records shown on the ValidationMission Table example to be automatically created.

                   Hope this helps :)

                    

              • 4. Re: Script to Automatically Create Related Records in Related Table
                philmodjunk
                     

                          where many Missions are linked to many Employees

                     But notice that the relationship from Missions to ValidationMissions and Employees to ValidationMissions are both one to many--just like my examples.

                     But before looking at scripted solutions, there's a non-scripted solution that can be pretty simple to set up and may do all that you need.

                     If you put a portal to ValidationMissions on Your Employees layout, you can include fields from Missions in the portal row of this portal. If you enable "allow creation of records via this relationship" for ValidationMissions in the Employees to ValidationMissions relationship, you can format ValidationMissions::MissionID with a drop down list or popup menu of missions.

                     Then, to link the current employee on your employee layout to an existing Mission record, you simply select that Mission from the value list set up on the ValidationMissions::MissionID field.

                     A script could be used in place of this, but since you will still need to design an interface that allows you to select both the employees and the missions to which to link them before running the script to create and link the needed records in ValidationMissions, there'll be a lot to work out before you even get to the script with that approach.

                     This process can also be turned around where you use a portal to ValidationMissions on the Missions layout to select employees for that mission.

                     You may also find this demo file a useful source of ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                     It is in the older file format so if you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open this file and produce a copy converted over to the newer file format.

                • 5. Re: Script to Automatically Create Related Records in Related Table
                  OlivierV

                       While it does sound like a great solution, it would not work quite as I've designed it in the interface. I'd prefer not to use drop-down menus to select a mission but rather to have all the missions appear so I can select the one I'm interested in.

                       Ideally there would be one Layout where we would see Employees Details (like a Character Sheet) and on this Layout I've put a portal with several panels. One will show the Mission currently in session, the second will show all the missions that are available (but they only appear if they are linked in the ValidationMission table of course which is the core of the issue) and a third shows all the Missions that are accomplished. (There is a "MissionStatus" field which is a value list so we can manually enter if it's accomplished, to do etc...).

                       It's very frustrating as the last screenshot I post seems fairly simple to an uninitiated like myself. I have 2 tables and I want to populate a third with all the values of these 2 tables. But i guess it's a lot more complex than I anticipated.

                       I'll study some more on Filemaker, including the file you dropboxed and hope i'll eventually see the light :)

                       Thanks again so much for taking some time to help ! 

                  • 6. Re: Script to Automatically Create Related Records in Related Table
                    philmodjunk
                         

                              I'd prefer not to use drop-down menus to select a mission but rather to have all the missions appear so I can select the one I'm interested in.

                         Then you might look at the "check boxes" layout example in the demo file.

                    • 7. Re: Script to Automatically Create Related Records in Related Table
                      OlivierV

                           I will try :)

                            

                           Thanks again !