Help with a date formula based on a single select column

[Lance_Priebe]

I am attempting to create a formula that finds the latest lastModified Date from a group of records. The group is a single select column.

= greatest lastModified date of “group1”

Thanks

[Justin_Barrett · Accepted solution]

Welcome to the community, @Lance_Priebe! :grinning_face_with_big_eyes: While it’s possible to do this with a little extra setup using a single-select field, that extra setup requires making links from all records to something in another table. Because of that, you could get something that behaves more naturally by converting your single-select into a link field, although you would lose any color-coding and automatic group ordering for those single-select items.

Here’s an example. I’ve got a basic project base with a single-select {Status} field to set the status for each project.

I can convert that in-place into a link to a new table. After choosing the “Link to another record” option, select the bottom choice, “+ Create a new table,” and set it up like this, making sure to turn off the “Allow linking to multiple records” option.

This will make the new table, create records with names matching your former single-select choices (those that were actually chosen in your records, anyway), and link to those relevant records. If there are single-select choices that weren’t represented in any of your records, you’ll need to make records for those manually. In the end, your main table now looks like this:

And my new table looks like this (after adding missing entries in my case):

Now on to date tracking. In my case, I’ll add a {Last Updated} field to my first table, making it a last modified time type, and only targeting my {Status} field. Because all records were modified when the that {Status} field was converted, all the time stamps will be the same at first. As you start changing options, the dates will change.

In the new [Status] table, I’ll add a {Latest Update} rollup field to pull in all of these dates, and calculate which is the latest with the MAX(values) aggregation formula:

Back in my main table, I’ll bring this time stamp back in with another rollup (I could use a lookup, but that would give me an array, whereas I have greater control over the type with a rollup).

Because all the dates are currently the same, I’ll just remove one and re-link it to force it to be the latest.

Finally, I can add a formula field that displays an emoji where {Last Updated} matches {Most Recent Time}:

IF({Last Updated} = {Most Recent Time}, "✅")

Because we’re using links, each status type will have its own latest update time. If I add more records for each type and group them, this will become more clear:

[ScottWorld]

Really nice step-by-step walkthrough, @Justin_Barrett! :slightly_smiling_face:

This is a nice little tidbit that I learned here:

Justin_Barrett:

I could use a lookup, but that would give me an array, whereas I have greater control over the type with a rollup).

This is very clever, since arrays have such difficulty in Airtable.

Thank you for posting all of this! :slightly_smiling_face: