Keeping only most recent date in field

[Jim_Webster]

Hi - I have a lookup field that returns the date from another table when a location was visited. The problem is when a place has been visited several times (e.g. in different years) I get all the dates in the cell, separated by a comma. Is there a way to only retrieve the most recent date or trim it to the most recent date in an adjacent formula field?
Thanks very much

[WilliamPorter]

Jim_Webster:

Hi - I have a lookup field that returns the date from another table when a location was visited. The problem is when a place has been visited several times (e.g. in different years) I get all the dates in the cell, separated by a comma. Is there a way to only retrieve the most recent date or trim it to the most recent date in an adjacent formula field?

@Jim_Webster,

Welcome to the Airtable Community!

Yes, there’s a way to do this. Let’s say you’ve got two tables Customers and Visits, where each customer might be linked to multiple visits. You’ll have a link-to-record field that creates that link (of customers to visits). To accomplish what you’re asking about, hide that link-to-customers field and add a Rollup field that looks like this:

That will pick the most recent date out of the linked dates and display it only, like this:

In this second screen shot, I showed both fields, so you can see that the value “4/17/1995” is being pulled out of a bucket that has other values in it, and that it is indeed the most recent value.

William

[Jim_Webster]

Thankyou!

The Max(value) rolls up the dates perfectly - is there a way to do the same with multiple text answers that are retrieved and separated by commas to only show the newest one?

In your example if a customer was given a code at each visit e.g. red, green or blue and the lookup pulls up red, red, blue for three visits - can you just reduce to the newest - in this case blue?

Much appreciated!

Jim

[WilliamPorter]

Jim_Webster:

The Max(value) rolls up the dates perfectly - is there a way to do the same with multiple text answers that are retrieved and separated by commas to only show the newest one?

In your example if a customer was given a code at each visit e.g. red, green or blue and the lookup pulls up red, red, blue for three visits - can you just reduce to the newest - in this case blue?

Your first question was fairly easy to answer – Airtable gives us the tools for that. This second question is easy to do in some other platforms (like FileMaker, which I know well, or with SQL) but not easy in Airtable. At least, I don’t see an easy answer.

Say you have Visit records that look like this:

You don’t want the MAX() code for customer WP (= 456), because that’s not the code associated with the latest date (389).

Here’s one way that MIGHT work for you, if your codes are all the same length.

1. In the Customers table, create another rollup field called Codes. In my little test app, this will, for the customer WP, generate this result: “123,456,389”.
2. Now, also in Customers, create a formula field with this formula: RIGHT( Codes, 3 ). I use “3” here because my codes – all of them – are three characters long. If your codes are nine characters long, use RIGHT( Codes, 9 ). This will grab “389” (in my data set), which is the value associated with the visit on 4/17/1995, which is the latest visit for that customer.
3. You can now hide the Codes field.

This approach only works if the codes are the same length.

I can think of a couple other ways to do this that are a little harder to code. I’m hoping somebody else will offer a better solution, if this one doesn’t work for you.

Good luck.

William

[Jim_Webster]

Thanks very much William - I really appreciate the time you put in to answer this. I could change my responses so they are the same character number and apply your solution and am also looking at a way to find the most recent record.
Thanks!
Jim

[kuovonne]

You can also use a script to lookup the first/last value in a linked record.