Current record based on a date in a group

I have a database of clients who need to pass an annual certification exam.
I have a Client table, that is linked to a Certification table, with multiple Certification rows for each attempt at the exam, with a unique date & result for each row. For each Client on the Client Table, I’d like to select the row On the Certification Table with the most current Test Date, so I can easily see their current Certification status.

The Client table key is Client Name and the Certification table Key is Client Name + Testing Date. I can change these if needed.

An example of the Certification Table below. On my Client Table, I’m creating a view with lookups to several other Tables in my base to get a complete view of Clients Status. When finding info on the Certification Table, I’d like to select the row that matches each Client on the Client table, with the most current date on the Certification Table, so I can easily see their current Certification status.

CERTIFICATION TABLE

 ID                        CLIENT     TEST DT     RESULT

|Client1 02-17-21 | Client1 | 2/17/2021 | Certified |
|Client1 02-20-21 | Client1 | 2/20/2021 |Not Certified|

|Client2 01-01-20 | Client2 | 1/1/2020 | Not Certified|
|Client2 03-01-21 | Client2 | 3/1/2021 | Certified|

I am currently using the Free Plan, but am considering upgrading to Pro for Non-Profits. Thanks in advance for any help you can provide!

Welcome to the community, @Andrea_Maney!

In your Client table, you will want to create a Rollup field using the MAX(VALUES) function to show you the latest date.

Thanks! @ScottWorld ! I had tried this, but didn’t know where to go from there.
So now, on my Client table, I have my Client info, and I am linked to the Certification table, and I have the max date calculated. I currently see that each of my test Clients are linked to 2 Certification rows, and I have a Max Date field that is correctly displaying the most current date for each client.

Now, how do I get just the Certification Result (Certified/Not Certified) from the Certification table based on that Max Date, so I only see my latest Certification Result for each Client? Hopefully that makes sense!

Even though this would be a piece of cake in any other database platform, Airtable doesn’t make this process very easy nor intuitive. It is QUITE the process.

Solving this will require creating 3 additional fields: 2 new fields in the Certification table (a lookup field and a formula field), and 1 new field in the Client table (a lookup field).

Here’s what you’ll need to do:

  1. First, you’ll need to go back into your Certification table, and create a lookup field that looks up the new rollup field that you just created in the Client table. You could call this field “Lookup Max Date”, for example. This will give you the exact same date for all of your certifications for the same client.

  2. Then, you’ll need to create a new formula field in your Certification table that compares the certification date of that particular certification test with the date of your brand new lookup field that you just created. If the 2 dates match, then the formula results in the status of the certification (“certified” or “not certified”). If the dates don’t match, then the formula results in nothing.
    The formula could look something like this: IF({Testing Date} = {Lookup Max Date},{Certification Status},"")
    This formula field should only show you the Certification Status for ONE of your testing records per Client.

  3. Then, back in your Client table, you’ll need to create a new lookup field that looks up the value of that new formula field in the Certification table. Airtable will ignore any of the records where the formula field results in an empty string, and it will only show you the value from the one record where it shows you the certification status.

Hope this helps! :slight_smile:

There is also another way of doing this which requires much more manual work on your end:

  • You could create a checkbox field in your Certification table where you can check a box to represent which certification test is the latest certification test for each client. You would need to manually uncheck the old test for each client, and then manually check the new test for each client — so there is a lot of checking & unchecking involved.
  • Then, back in the Client table, you could just create one simple lookup field that only looks up the latest certification status — and it would know which one is the latest certification status because you already manually checked off the latest certification test. There is an option in the lookup fields that lets you only include certain records, so you would tell it to ONLY include records where the checkbox field is checked.

Personally, I prefer the more automated way that I outlined above, but the manual way only requires one lookup field.

1 Like

Thank you again! I was trying to think this through and was on a similar path as your first suggestion, but thought there must be an easier way. Lol

I’ve built many apps in other database tools. Airtable is awesome for its simplicity, mobile app and ease of sharing with others in my org who aren’t database savvy, but does have its own quirks as a trade off.

What you described makes perfect sense and should get me exactly what I need. Thanks so much!!

1 Like

You’re welcome! Glad I could help! :slight_smile:

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.