Find values associated with most recent entry

[Eliza_Bryant]

Thanks in advance for any insight from the AT community!

I have a base that includes a bunch of school enrollment information. Some is static/historical, and some is updated monthly.

There is one table that with the list of schools as the primary key, and those schools are linked in another table for monthly enrollment tracking. From the latter is a form that schools complete to enter their monthly enrollment information. The form submission has a “Date Created” field.

I want to be able to provide the updated enrollment total based on the MOST RECENT entry date. Because different schools provide there updates on any given date of the month (and some haven’t submitted since the previous month), I can’t just filter by date because I will lose some data. For instance, if I put a filter on the date for the last month, it won’t catch a school that last submitted five weeks ago.

For example:

Monthly Registration Tracking (School names are linked to the Schools Table)
School A - 6/8/2021 - 400 students
School B - 7/10/2021 - 115 students
School C - 8/1/2021 - 225 students
School B - 8/15/2021 - 123 students
School C - 9/1/2021 - 220 students
School B - 9/8/2021 - 125 students

I would want a roll-up or some field in teh Schools Table to show the following:
School A - 400 students
School B - 125 students
School C - 220 students

Thanks!
Eliza

[Jack_P · Accepted solution]

I had a very similar question that @Kamille_Parks answered for me this past week @Eliza_Bryant

How to pull a decimal number out of an Array OR how to use conditional lookup in combination with MA... Formulas

If: You have records in Table 1 linked to one ore more records in Table 2, You have {Field A} in Table 2 that can be compared with MAX(values) or MIN(values) (i.e. “what is the most recent date?”, “what is the smallest number?”) You want Table 1 to pull in not just what is the max (or min) of every linked record’s {Field A}, but the value of {Field B} associated with whatever record has the max (or min) value for {Field A} Then follow along in this episode of BuiltOnAir (Season 8, Episode 2,…

The podcast episode shows how you can use MAX(values), along with rollups and lookups to pull data from a record with the most recent date. I was able to successfully implement the solution after watching the episode briefly, it’s pretty much plug and play in the example @Kamille_Parks showcases.

Hope that helps!

[Eliza_Bryant]

This was great! Thanks!! It’s a little round-about, but it’s not overly complicated. Thanks for sharing!