Script to Automatically Create Related Records in Related Table
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 :)
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.
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 !