How to capture most recent child records

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

Hello,

I have a parent and child relationship table set-up which we use to track Employee Trainings.  The first table, Employee Trainings (Parent), lists all of the trainings people have to take.  From each training, personnel in our office will create a record (an attestation) in our Attestations table (Child) to attest to the specific training records.  

Some of the trainings need to be completed annually.  Is there a way to capture the most recent attestation for each training by personnel?  

I have used the guidance below, but when I do this the Maximum Record ID brings in the latest attestation, regardless of who completed it. I do not see a way to filter my summary field to have it unique by user.

Please note - I could do this by "Current User" option, but that doesn't work when I need to see someone's information. 

Guidance I have already found, but doesn't appear to get me all the way to my answer.  

(From Mark Shnier in another post) 

Make a summary field of the maximum of the Record ID# of the child status table records called [Maximum Record ID# of status records]

Make a new relationship where 1 Status is related to Many parents - ie a reverse relationship and the reference field at the right side will be that field [Maximum Record ID# of status records]

Then just do a lookup of the Status from the Child to the Parent.  but QuickBase will see it as a lookup from a parent to a child.

Photo of Ken

Ken

  • 50 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
Can you describe your tables and their relationships explaining the One to Many side of each relationship?
Photo of Ken

Ken

  • 50 Points
I'll apologize in advance if my response doesn't address the specific information you may be looking for in the question.

The Employee Training table is the One and the Attestations table is the Many in this relationship.  The Employee Training table contains individual trainings that personnel must take.  In which each record is a training that is required for specific projects.  The personnel view the training record, and then are able to "Add Attestation" tied to the Attestations table.  So a single training will have multiple attestations, and personnel are able to attest to the same training multiple times (re: Annual Trainings).  

I will add this caveat.  There is a separate table, Personnel, which has a record for every user (personnel) who needs to attests to trainings.  We have multiple projects, so not every user needs to take every training.  The record has Report Links to bring in associated reports to show which trainings people need to take; and Report Links to bring in associated attestations to those trainings.  This way I, or Human Resources, can view a specific Personnel's record and see all trainings they need to have taken along with the attestations to the training.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,478 Points 50k badge 2x thumb
You will not be able to get what you want without some enhancements to your app.  In fact I recently did a setup for a client which was very similar.  In their case there was required Training to be taken, but only certain Training applied to certain employees.  We set some criteria fields for the "Areas" which needed to be trained.  Say there were 5 drop down fields on the Training record  and the first Training record was needed to be done by an Employee in the Areas of Warehouse or Driver.  Correspondingly, each employee record was flagged with say 5 Areas they operated in, such as Driver, Warehouse,  Manager. So an employee responsibility could cover several areas.

I then created a button to push to copy the Training needed records to each qualifying employee.  So they would set up a New Training Master Record required record and essentially a version of it would be copied in as a Child table to the correct employee records.  Then each of those child record, would need to be completed by a certain date.  In your case each of those child records would need regular Attestations.

But back to your app.  I think that each employee will need to have a Training Required record for each Training they are required to do.  You just have 1 "Master Training Record" for each training type. so that is why you are stuck.

So, you need to have a setup where 1 Master Training record has many Employee Training records, and of Course 1 Employee has Many Training Records.  Then those Attestation record would be in a relationship where 1 Employee Training record has many Attestations. Then you could roll up with Summary fields if the Employee Training for each Employee Training record is up to date.

You can create these Employee Training records manually perhaps with Grid Edit or contact me off line via the information in my profile to help you automate the process - ie click a single button and add the employee Training record to each Employee or, if you need that concept, to each "qualifying" employee if not all training applies to all employees.  I'm off now until Labor day on a trip (with email but not doing new QuickBase development work) but back in action after Labor Day.  Feel free to contact me if you need an automated solution.