How to find the record with the oldest date within a set/group?

[Peter_Galletta]

I have a table with a list of contracts that has fields for CompanyName (link record to a company table that lists all the companies) and ContractStartDate.

How do I filter or find the oldest record for each Company based on ContractStartDate? A company can have 1 or multiple contracts.

Thanks!

[Sistema_Aotearo]

In your Company table, use a rollup field on your on the contracts link field. Display the ContractStartDate and use this formula:

MIN(values)

 This will then display the Oldest date within those linked records.

You can then sort or filter by that rollup field. Let me know if this is what you're looking for.

[Peter_Galletta]

Thanks.

I had tried that, but what I end up with is the date of the oldest contract, but no way to correlate that to the linked contract. Maybe I'm missing something with the filters?

 

[Sistema_Aotearo]

Sorry for misunderstanding your question. Are you looking to display the most current contract and then sort by date?

Rather than finding the oldest contract for each company, you want to find the company with the oldest current contract.

You use a lookup field. Again with the ContractStartDate. Limit the selection to just one.

If this doesn't work, you can also make a drop-down on your Contracts table that can toggle if the contract is "complete/current" or "active/inactive". Sort of like its status. From there use the filters on the lookup field to filter out any non-current contracts. If a company has multiple contracts active, use the Rollup field I described above, with these filtering set up.

Apologies if this is again not what you're looking for! Let me know 🙂