I’m building a medical surveillance application for employees working in a hospital environment, and are required to take a set of annual medical tests.
The tables are: Test Types, Employees, and Employee Tests.
Employee Tests is an intermediary table between the Test Types and Employees table.
In the Employee Tests table, there is a date field named Compliance Date, which drives an Expiration Date formula date field to flag renewal dates.
What I am looking to do is automate the process of annual renewals.
Example: An Employee is related to an Employee Test, which is related to the Test Type ‘Flu’. The Compliance Date value is 10/1/2016. The Expiration Date is 10/1/2017.
I’d like to be able to add an Employee Test record in 2017 and have that action trigger a checkbox field – Test Renewed – on 2016’s ‘Flu’ record.
I’ve been messing around with summary and lookup fields trying to use the Maximum Date type, but its not working. Creating a Maximum Compliance Date field in the Employees > Test relationship returns the most recent Compliance Date of all of that employee’s test records.
Creating a Maximum Compliance Date summary field in the Test Types > Tests relationship return the most recent Compliance Date of all of that test type’s test records.I can’t figure out how to read when the Related Test Type and Related Employee are the same in multiple Employee Tests records, and then flag the most recent of that group of records.