Forum Discussion

HeatherBryant2's avatar
HeatherBryant2
Qrew Assistant Captain
8 years ago

Flag most recent record in many to many details table

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.
Ask about Quickbase functionality
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    I would suggest adding a 4th table.  Called something like "Renewals"

    They would be a child record to the "Employee Tests"

    So then any time you have a re-test/renewal. You would click to add a renewal to the test.

    This gives you the history, and an easy way to keep track of that particular test, for that particular employee.

    If you play the cards right you can have the new record be added in a pop-up window and then when saved it refreshed the record so you have the most recent updates visible.
  • HeatherBryant2's avatar
    HeatherBryant2
    Qrew Assistant Captain
    8 years ago
    Matt, thank you. That makes sense. Do you suggest I have the first entered Employee Test record as a Renewal record, or should only the recurrent test records live there?
    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      For speed of use on entering the first test, you can use the normal test field for the 'original' date, and the renewals for everything after that.

      Your 'expiration' date is going to be a formula field already so you can use the original if there are no renewals.

      I think the users will enjoy that simpler approach.
    • ShawnRenn2's avatar
      ShawnRenn2
      Qrew Trainee
      8 years ago
      Agreed. I was worried the post was getting lengthy so I figured that would come next. Same setup as what you'd use for starting inventory.
  • ShawnRenn2's avatar
    ShawnRenn2
    Qrew Trainee
    8 years ago
    Hey Heather, what matthew is suggesting is that the Employee test record will now be used only as a placeholder for tests an employee needs to stay current on, and a place to summarize the last time a renewal was completed.

    So lets say you're adding a new employee and their tests. The workflow would be as follows.

    Create the employee record>click "Add Employee Test">populate the Employee Test information and select the related Test type, (you would create one for each of the test types the employee needs to stay current on.)

    You would want to create a new table called something like "Renewals" and relate it to Employee tests where one Employee Test can have multiple "Renewal Records" 

    The only function this renewal record serves is being able to capture a history of the employees completed renewals for each specific test, and (the most important part) a summary of the last (or Maximum) completed renewal for each specific test type for that employee, rather than the lastest of any completed tests.

    There are some other great things this structure allows you to do, such as the flexibility of how often each test type needs to be renewed (annual, bi-annual, etc) or a field to indicate how much notice you want for each renewal (maybe one test requires more preparation), if the tests require a facilitator, you might benefit from a report showing all employees summarizing the number of upcoming renewals grouped by test type so you could schedule a group of employees that need that renewal for a single facilitator... 

    Hope this helps.