AnsweredAssumed Answered

Script to Automatically Create Related Records in Related Table

Question asked by OlivierV on Jun 27, 2014
Latest reply on Jun 28, 2014 by 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 !

      

Outcomes