Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Help with a date formula based on a single select column

Solved
Jump to Solution
890 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Lance_Priebe
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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.

Screen Shot 2020-06-04 at 9.58.37 PM

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.

Screen Shot 2020-06-04 at 10.00.55 PM

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:

Screen Shot 2020-06-04 at 10.05.24 PM

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

Screen Shot 2020-06-04 at 10.06.24 PM

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.

Screen Shot 2020-06-04 at 10.11.00 PM

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:

Screen Shot 2020-06-04 at 10.14.25 PM

Screen Shot 2020-06-04 at 10.13.25 PM

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).

Screen Shot 2020-06-04 at 10.19.51 PM

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

Screen Shot 2020-06-04 at 10.21.15 PM

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

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

Screen Shot 2020-06-04 at 10.23.54 PM

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:

Screen Shot 2020-06-04 at 10.29.20 PM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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.

Screen Shot 2020-06-04 at 9.58.37 PM

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.

Screen Shot 2020-06-04 at 10.00.55 PM

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:

Screen Shot 2020-06-04 at 10.05.24 PM

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

Screen Shot 2020-06-04 at 10.06.24 PM

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.

Screen Shot 2020-06-04 at 10.11.00 PM

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:

Screen Shot 2020-06-04 at 10.14.25 PM

Screen Shot 2020-06-04 at 10.13.25 PM

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).

Screen Shot 2020-06-04 at 10.19.51 PM

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

Screen Shot 2020-06-04 at 10.21.15 PM

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

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

Screen Shot 2020-06-04 at 10.23.54 PM

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:

Screen Shot 2020-06-04 at 10.29.20 PM

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

This is a nice little tidbit that I learned here:

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

Thank you for posting all of this! :slightly_smiling_face: