Keeping only most recent date in field
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
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.
- 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”.
- 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.
- 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
