Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 19, 2022 10:27 AM
I’ve used Airtable before, but never set up my own base before, so pretty new to this.
I have two tables
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!
Solved! Go to Solution.
Jan 19, 2022 12:16 PM
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.
Jan 19, 2022 11:27 AM
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.
Jan 19, 2022 11:43 AM
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!
Jan 19, 2022 12:16 PM
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.
Jan 19, 2022 12:58 PM
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!