Sorting by the linked record with the most recent date

[Ryan_Sager]

Hi-

We’re trying to get Airtable to sort a list of records by the most recent linked record. Basically, we want Airtable to ‘understand’ dates in a linked record.

Here’s the basic flow:

• One table we have is the ‘Sponsor’ (advertiser) who has purchased a ‘Sponsorship’ (advertisement)
• The ‘Sponsor’ is linked to every individual ‘Sponsorship’ that it has bought
• Each individual Sponsorship is its own record, with a Date field as the primary field in the linked Table
• What we want is for Airtable to know what date a Sponsor’s most recent Sponsorship ran (or, what is the latest date of a Sponsorship linked to a particular Sponsor)
• With this, we’d be able to allow our users to sort by Sponsors who’ve run an ad recently (say, in the last 30 days)

The problem is that even though each Sponsor has all its Sponsorships linked to it, I don’t see any way to get Airtable to use the linked Sponsorship records to sort Sponsors.

We’ve tried a Lookup field of the date of the Sponsorship, but Airtable treats the earliest date as the “first” item in the Lookup field, and can only sort by that first date.

Any idea how to get around this? Perhaps create a field on the Sponsor table that contains only the most recent date of a Sponsorship? Would that require a script?

TIA

[AlliAlosa · Accepted solution]

Hi there!

Here’s how to get the date of a sponsor’s most recent sponsorship:

1. Add a rollup field to your [Sponsors] table
2. Point it at the [Sponsorships] table, then pick the field containing the date of the sponsorship
3. Use the aggregate function:

MAX(values)

This should give you the most recent sponsorship date :slightly_smiling_face:

I hope this helps!

[Ryan_Sager]

Amazing, that does the trick! Thank you!