Help

Rollup based on max date in source table

Solved
Jump to Solution
818 2
cancel
Showing results for 
Search instead for 
Did you mean: 
DanielP
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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

See Solution in Thread

2 Replies 2
ScottWorld
18 - Pluto
18 - Pluto

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

DanielP
6 - Interface Innovator
6 - Interface Innovator

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