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!
