Current record based on a date in a group

Andrea_Maney · ‎Jan 02, 2021

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

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!

ScottWorld · ‎Jan 02, 2021

You can watch my demonstration on how to solve this problem in this Airtable podcast episode. In a nutshell, here is what you will need to do:

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.
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.
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! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

See Solution in Thread

ScottWorld · ‎Jan 02, 2021

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.

Andrea_Maney · ‎Jan 02, 2021

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!

ScottWorld · ‎Jan 02, 2021

You can watch my demonstration on how to solve this problem in this Airtable podcast episode. In a nutshell, here is what you will need to do:

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.
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.
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! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Andrea_Maney · ‎Jan 02, 2021

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!!

ScottWorld · ‎Jan 02, 2021

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