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.