Trying to sort out how this can be done without creating a LOT of columns.
I have a list of trainings for employees, many of which may be duplicates other than the date as the same employee may take the same training many times over the years (IE I take first aid every 2 years).
The unique record ID is “Employee - Training Name - Date” which is a formula concatenating other fields.
What I want to do is group by the employee and then filter for any trainings that are expired (formula is created to determine if the date is past due IE first aid is good for 2 years, the training I took in 2014, 2016, and 2018 are all expired but the one from 2020 is not). The point of this is to generate a list to distribute to the team to show what employees are behind on training.
The kicker here is when I group by employee and then filter to exclude “current” training and just show expired, it is going to show that I have expired training from 2014, 2016, and 2018 (which clutters the list) and it will not show that I am actually current on my training as I took it in 2020.
What I am trying to figure out is how to filter to the MAX date value (aka most recent record, so in this case the filter just brings up my 2020 first aid training and removes all the others since they don’t have the max date), but that date is conditioned on being filtered by both the employee and the training. In this list there are lots of employees doing lots of different training, so I need the max value of each unique case (IE Tony took first aid with me every year except 2020 so his return there would be 2018, but he also took substance abuse training every 5 years for the past decade so his 2019 training shows up for that).
Any way to pull this off? I know it could be done with some array formulas or queries in excel, but I can’t quite land it in airtable. View/Formula to Filter for Max Date Conditioned by Other Field(s)?
