Thoughts on a Personnel Training Log That Notifies When Training Is Expiring?

Spencer_Mullane · ‎Apr 09, 2019

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.

drassi · ‎Apr 09, 2019

We run a very similar process to track all employee trainings & certifications, and the model that I use is similar but slightly different than what you outline. We only use 2 tables (as we don’t track upcoming trainings, only completed):

Employees
Training Certificates

As far as modeling, Training Certificates obviously have a link to the Employee completing the training, and there is an Expiration Date column is set on all “Training Certificate” records.

Then, on the Employees table, we do a Rollup that tells us the MAX expiration date of the training in question. Then, we create an Employee view such as “Needs Safety Training”, that filters such that only Employees with a max training expiration date are already past or within the next month, for example. As far as monitoring, now you can either check this specific view periodically to see who needs an upcoming training, or use something like Zapier/Integromat to automatically email you when new records pop up in this view.

From a data quality perspective, I would NOT recommend that you delete records when a training expires—there is no need to remove this old data, and it could cause confusion or missing training history! In our model we simply keep adding new trainings in an append-only fashion, and employee’s max expiration date simply increases as new trainings are taken.

It gets a little complex if you have different training types. If you want all the trainings to all fit in one table, I had to make some intermediate formula columns (one per training type) so that the employee rollup can select records for only one specific type at a time.

JonathanBowen · ‎Apr 09, 2019

Hi @Spencer_Mullaney - I had a go at this problem - maybe some ideas in the base below you could use?

A few comments:

Personally I wouldn’t have a training schedule and a training log, i.e. the past and the future. I think it makes more sense to have both of these in the same table and use different views (based on date) to see what is coming up in the future and what has already happened.
I agree with @drassi - generally better not to delete records as you might need them in the future and they’re nothing to store (again could use views to hide past events that you don’t want to look at all the time)

What I’ve done in my base is to have a personnel table and a training courses table and link these in the personnel/training table. The training courses table allows you to specify how long a training lasts and you can use this info to drive future training alerts/requirements.

In the personnel training record table it calculates the next due date based on the last date taken and number of days till the training expires. I added an alert in there based on a formula to highlight when a person/training needs to be rescheduled based on the last time they took it. There’s a manual checkbox in there to note that you have rebooked them for future training and this sort of closes the old record out by removing the alert.

Hope there’s something you can use here!

JB

Spencer_Mullane · ‎Apr 12, 2019

Thanks for the responses everyone, had actually made a few tweaks exactly like that immediately after I posted that I think probably send it more in the right direction. One employee/personnel tab, one training tab, and a column with a single select for whether or not the training is completed, scheduled, or canceled. Then in the training table we’ve got a column for when the training is (start and stop because I want to feed a calendar to show a training from 10am-12pm next Tuesday for example) and then also a “good for” field (in years) and an expiration date which is the training date + the “good for” duration which yields when the employee needs training again.

The part I am still having trouble with and this is probably because I haven’t played with views and inter-table relations too much, but we have LOTS of training types that I would ideally like in one table and in one field in the employee record so that the employee table doesn’t have to add 30 columns. We have, as a short example;

Training Types:
Confined Space
Permit Required Confined Space
Excavation Safety
Trench SAfety
Fall Protection
Aerial Lift
Forklift
Crane
Concrete Pouring
Scaffolding
Roadway Worker
OSHA 10
OSHA 30
OSHA 40
HAZWOPR

etc etc. Any employee may need all or some of these training courses (and more) and they are all good for varying lengths of time (usually 1, 2, 3 or 5 years). We also, depending on the needs of the project, may add ANOTHER training that was previously unknown, so the list can always grow.

Right now I am pulling a linked field from the “Training” Table over to the “Employee” table which allows multiple records and thus would have every training that the employee ever took (agree, not sure why I stated it as delete the record, rather we would probably like to hide expired trainings but still have the data there). The issue is how do I separate each of the many training types that could land in that linked field for each employee, and then filter out by expired or not.

The goal, and maybe this is too complex to be workable, would be to keep this info updated and basically get alerts sent when an employee needs a new training and then also include the next available training sessions of that type over a 3 month period. So if the safety department has added 3 future first AID/CPR trainings to the “Training” table within the next 3 months and flagged them as “scheduled”, then if my First Aid cert is coming up due on 7/12/2019 (2 months out), i would get an email saying I am about to expire and here are the (in this case 3) upcoming dates that I can get trained before my cert is over. Obviously if we don’t have any training added to the schedule then I would get no options, but I would know it was coming due. Safety could then appropriately schedule future trainings based on the upcoming needs of the project personnel (ie me and 26 other people need first aid by 8/1, so lets put some of those on the calendar).

Right now I’m seeing how I can link in the record of what training was taken by each employee, but the jump to seeing what training they are coming due for (separated out by each type) is what I’m stuck on. Apologize if I’m overlooking the answer above, reviewing Jonathan’s sample base some more for reference it looks very similar to what we have setup so far.

JonathanBowen · ‎Apr 12, 2019

Hi @Spencer_Mullaney - I think this is where you need a 3rd table to link people and training courses (see my example base - table “Personnel Training Record”). If you do this, you have a record for each person/training/date combination and so can more easily see those that are coming up, those that have expired and need to be rescheduled etc. If I understand correctly, you’ve got a single person record with all of their trainings in a single field, which makes the expiry element (and other views, e.g. filtering by type) difficult.

JB

Spencer_Mullane · ‎Apr 12, 2019

JB,

Nailed it, I will look into that 3rd table it seems to me that you are basically just concatenating the “employee” + “training and date” to make a unique record for each combo of employee + training type + date, is that correct?

Thanks again

Spencer_Mullane · ‎Apr 16, 2019

Jonathan,

Any way you can share that base with the fields still editable so I can investigate the setup for each table? (EDIT here, copied the base, duh…) For some reason I am drawing a blank (and it will probably be really obvious) on how to create the 3rd unique employee-training-date record from the other two fields.

This is the format I have at the moment:

Personnel is a list of all employees on the project
Training types is the list of all the different types of training we would do and specific information on each that never changes (IE how long is it good for, what employees require that training, training name, etc)
Training table lists the specific training events we had, which pulls names from the “Training Types” table and inputs some of the particulars, and then also pulls in employee names for who needs the training class.

Big picture I want to set it up so that we can know what trainings to schedule based on who needs that training and when groups of people have that training expiring (and hopefully automate everything but the scheduling of the actual time/date of the class). Still stuck on how to reference that third intermediate step table and then relate it back to what training each employee is and is not current on (IE if I am listed and requiring first aid and my last cert is expired, it shows me as needing that class. However if I am NOT listed as required to have trench safety but my last cert is expired, it would not show that I am due.)

Thanks again

EDIT: Think I may have figured out the difference, and it may be a sticking point or at least more work for us. Correct me if I am wrong, but it appears that in “Personnel Training Record” you pull in a name and a class for each record line, so if there was a class of 20 you would manually create the 20 record lines (using linked fields from “Personnel” and “Training Courses”).

What I am “hoping” to do is to create a single “class” in “Training” (my base) and then using a linked field, populate everyone who went to that class. That would be the basis for generating 20 unique records of “Name-Date-Class” in the “Personnel Training Record” table, but I am not sure if this is doable? I would be somewhat of a reverse pivot table I suppose, if that makes sense.

JonathanBowen · ‎Apr 17, 2019

Hi @Spencer_Mullaney - I think you’ve basically got what I was thinking of, although I appreciate this is different from the way you were approaching the problem.

Re: this:

Yes, correct.

For your last point:

I don’t think this is doable in Airtable on its own, i.e. have a training class record with 20 attendees listed in a single field, then have Airtable explode these out into 20 records in another table. That said, I see some pretty crafty workarounds on the community boards, so possible someone might be able to advise on a method. You could definitely do this sort of thing with the API, and might be possible with Zapier, but not with native Airtable AFAIK.

Just FYI, I’ve made a couple of changes to my base (same link as above) that might help:

I added a job roles table and then assigned a job role to each person and to each training course. You can then pull through onto the Personnel table the trainings a person/job role is required to do (just the course type, not the course/date):

In the Personnel Training Record table, I created a new view that groups by training course/date:

You could obviously create variants of this to see all future/schedule courses or all first aid course over time.

As a general principle, if I were building this from scratch as a web app with a relational database, I would definitely model this as a personnel table, a courses table (i.e. types of training), a course/date table (an instance of a course on a specific date) and, finally, a person/course/date table (with a record/row for each person/course/date). You wouldn’t normally try to add the person records to the course/date record as the information is then much harder to manipulate. So, hence, I’ve followed this principle in my base. That said, Airtable is a bit more “horses for courses” so different approaches are equally valid.

Hope this helps.

JB

Spencer_Mullane · ‎Oct 09, 2020

Revisiting this topic as we finally got the safety department to start updating records in Airtable.

The new challenge I am looking at is how to only show the most “recent and expired training” in a view.

I have a personnel table with a list of employees, a table for a list of training types (first aid, Crisis management, crane safety, etc).

Then there is a training schedule where I create a unique training occurrence which links to training types and then I add a date (ie there is a record for a first aid class on 2/2/2015 and another record for a first aid class on 3/15/2018, and for crisis management on 2/24/2020, etc etc). The unique key in column 1 is a concatenation of training + date.

Finally I have a training registration table where I link an employee to a training, and this is where I am trying to filter records. Here I have a linked field to the previous table, training schedule, and then another linked field that goes to the personnel table. The unique key for each record in this table is a concatenation of the training type + date from the other table & the personnel name from the first table.

What I can’t seem to figure out is filtering out “old” expired records so I can get a current report of expired training without a lot of clutter, somehow I need to put a “MAX” filter on any record where other columns meet certain criteria.

For example, lets say first aid is good for 2 years and I take it every year on january 1. I will have a record from Jan 1, 2020 that is valid, then one from Jan 1, 2018 which is invalid, and another from Jan 1, 2016 which is also invalid, and Jan 1, 2014 which is invalid. Let’s say I had not taken that Jan 1, 2020 class and thus my first aid cert is expired.

What I want to do is group by employee and then only get the most recent occurrence of each training type, and then filter out the ones that are currently valid so it only shows me a list of expired training grouped by person.

In the above example if I filter as explained and I DID take that first aid training on Jan 1, 2020, it’s going to remove my currently valid Jan 1, 2020 training but show me 3 expired trainings from 2014, 2016, and 2018. This is wrong because my first aid is current for 2020, so I only want to work off the most recent dated entry. Also if I had NOT taken the 2020 training, it would correctly show me being expired (most recent being 2018) but would also clutter the list with 2 additional expired trainings from 2016 and 2014.

Any thoughts on an elegant way to filter this out? I know it can kind of be done by making a TON of fields in the personnel tab, one for every type of training there is, but we have like 75 training types and more get added which would not only require adding them to the table but then also adding a column for them in the personnel table and the associated MAX() formula every time something new was included.