Displaying most recent date in another table

[Blake_Eskin]

I’ve used Airtable before, but never set up my own base before, so pretty new to this.

I have two tables

• Students
• Daily Submissions

Students is a fixed list of enrolled students that is linked to Daily Submissions

Daily Submissions is fed by a form that collects a student (selected from a menu from Students), a link, and a description. When someone submits a form, it also collects Created Time in a field called timestamp.

In Students, I want to create a field called Most Recent Share that will display the date of the most recent Daily Submission for each student.

After reading Rollup field overview and topics including Formula to calculate the latest Date in an array and Roll up max date but show another field I tried creating a Rollup that collects ARRAYUNIQUE(values), which showed nothing, and MAX(ARRAYUNIQUE(values)), which showed a column of zeroes

But I don’t know which of those is correct, or what the Formula (or is it Lookup?) in the Most Recent Share field should be, and how it calls the Rollup field.

I would be most grateful for your guidance. Thanks!

[Justin_Barrett]

Welcome to the community, @Blake_Eskin! :grinning_face_with_big_eyes: The MAX(values) aggregation formula is what you want. It will look at all of the dates and find the most recent one.

[Blake_Eskin]

Thanks, @Justin_Barrett! I just tried MAX(values) and I’m seeing nothing in that field, the same as what I see when I do ARRAYUNIQUE(values).

And maybe I’m mistaken I thought I read somewhere that MAX doesn’t work with dates in Airtable, only with numbers?

I appreciate your quick answer!

[Justin_Barrett · Accepted solution]

Blake_Eskin:

And maybe I’m mistaken I thought I read somewhere that MAX doesn’t work with dates in Airtable, only with numbers?

The MAX() function does work with dates, and I’ve used it frequently for that purpose. However, because of something that I overlooked—or, more accurately, didn’t look at closely—in your screenshot, I didn’t realize that it wouldn’t work in the specific conditions of your setup.

The lightning symbol overlapping the calendar icon next to “timestamp” indicates that you’re pulling from a synced table. That means that the value in {timestamp} isn’t actually a date. It’s just a string, and in that context, the MAX() function doesn’t realize that it’s a date.

I’m guessing that your [Daily Submissions] table is the synced table. What you’ll need to do is add a formula field to that table—call it “Timestamp Converted” or something similar—that translates the text version of that timestamp back into an actual datetime value. Based on what looks like a standard “M/DD/YYYY hh:mm” format (if I’m correctly reading your first screenshot), Airtable should be able to auto-parse that if the formula is just DATETIME_PARSE(timestamp). Then you can use the rollup field in the [Students] table with a MAX(values) aggregation formula to roll up the output of that formula field.

[Blake_Eskin]

Wow! This is now working. I’ve still got some fiddling with timezones to do but I think I’ve got it now. Thank you so much @Justin_Barrett!