Apr 13, 2020 03:39 PM
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
Apr 13, 2020 04:01 PM
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.
Apr 13, 2020 04:27 PM
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?
Apr 13, 2020 09:47 PM
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.
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.
Apr 15, 2020 04:59 PM
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.
Apr 15, 2020 05:18 PM