1 Reply Latest reply on Sep 30, 2014 9:27 AM by philmodjunk

    Current Training v. Required Training



      Current Training v. Required Training



      I'm creating a database to manage employee training. I have a table called "Employees" that displays all of an employee's current certifications and the date that they received them. Another table, Qualification Table, lists all of the potential certifications that an employee can have. And finally a table called "Job Training Required" that lists the qualifications required for a specific job title. 

      The "Job Training Required" table lists qualification codes that are associated with a particular noun name of that training (example: T001). That code is associated with a particular noun name on the Qualification Table. Example, T001 = Company Policies. 

      I want to be able to choose a Job Title on the employee's record table from the popup menu I created and have it automatically compare their currently held certifications with what certifications are required for that job. I would like it to tell me in a field whether they have all the training they need, "All training requirements met", or indicate the noun name of the training that they need to get in order to have all training requirements met. I am not quite sure how to go about that (what function or script I would use, syntax, etc.).

      If anyone has any suggestions or info, thank you. 

        • 1. Re: Current Training v. Required Training

          You'll need to start with the data model needed (The tables and relationships).

          It would appear that you need these tables/relationships:


          Employees::__pkEmployeeID = Certifications::_fkEmployeeID
          Employees::JobTitleID = RequiredTraining::JobTitleID
          Qualification::__pkQualificationID = RequiredTraining::_fkQualificationID

          None of the fields shown above are name fields, they would be either auto-entered serial numbers or Get (UUID). Your "Noun Name" (odd term) would simply be a field in the Qualification table which can be placed in the portal row of  portal to RequiredTraining put on your Employees layout to show that value for each required training record. To show what required training has been completed or is incomplete, you can add Tutorial: What are Table Occurrences? and link it in like this:


          RequiredTraining::_fkQualificationID = Certifications|Employee::_fkQualificationID AND
          RequiredTraining::_fkgEmployeeID = Certifications|Employee::_fkEmployeeID

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

          _fkgEmployeeID would be a global field (hence the 'g') loaded with the value of __pkEmployeeID via the OnRecordLoad Trigger on your Employees layout. Enable "allow creation of records via this relationship" for Certifications|Employee and you can then add the DateRecevied field from Certifications|Employee to the row of a portal to RequiredTraining and you can then record an employee's completion of that training by entering a date into this field--an action that will then create a related record in the Certifications table.

          Every RequiredTraining Record that does not show a DateCompleted value from a linked Certifications|Employee record would represent training that the employee has not yet completed. If you wish a filtered portal could list all such records.

          Caulkins Consulting, Home of Adventures In FileMaking