Trying to think about the proper/organized way to do this. I currently have a personnel log of all employees on our project (we are a construction company so combo of salaried staff and hourly craft labor). This log has name/phone/email/job title/date hired/do they have an iPad issued (link from another table)/etc/etc.
What we are having trouble keeping organized is safety training. In construction we have many different trainings, many safety related, that lots of different people may or may not need. The 3 sets of data we would want to link up are:
The personnel log (these are the people that need training)
The training schedule (when is a particular training next scheduled for)
The training log (what training occurred, who took it)
Basically we would have a table of past trainings that occurred, which would link to the personnel log and everyone who was in that class would be linked to the record of that class (and the certificates/sign in sheet would be attachments to that record).
Related to that, in the personnel log you could look at an individual employees record and see all the trainings they have taken.
We would also have a log of upcoming scheduled trainings and link it to what employees need to take that class.
So the kicker here that I can’t figure out (and maybe can’t be done) is that these trainings expire after a certain period of time (for example CPR/first aid is good for 2 years). If myself and 4 other coworkers were to take CPR tomorrow, 4/10, I would have the safety director link our names to the record for the class given on 4/10/19. I would then want the system to DELETE that link 2 years later on 4/10/21 because my certification has expired, and probably shoot me a reminder email 3 months before the expiration (so 1/10/21) so that I know I need to sign up for a new class.
The reminder and the removal of the record is what I don’t have quite figured yet, not sure if this is feasible without lots of outside integrations or not which may be a bit much for the safety department to handle. Any thoughts on a direction for this would be appreciated though.