Rollup based on max date in source table

[DanielP]

I have 2 tables…

Companies
CompanyID, CompanyName, ContractIDs

Contracts
ContractID, ContractTitle, EffectiveDate, ExpirationDate, CompanyID

They’re linked together via the final field in each table where 1 Company can have multiple Contracts.

I want to create a field in the Companies table to display the ContractTitle of the most recent contract but am struggling to find a solution using Rollups. Of course, I can add a field to Companies using a Rollup that takes the MAX(ExpirationDate) to at least give the latest contract expiry, but I can’t find a way to use that expiry to bring back the single ContractTitle field with that expiry per Company.

Appreciate any thoughts at all on whether this is possible!

[ScottWorld · Accepted solution]

I cover this process step-by-step in this video podcast:

[DanielP]

Thank you that worked perfectly - also learnt about View sections by accident which sound very useful!