Rollup - displaying a field, based on another field's value

Question

My Table A is a list of Staff members, and is linked to Table B. Table B are records of Agreements, with one of the fields being Company (the company name of that Agreement. We have several companies).

A record in Table A (Staff) has multiple Agreements in Table B. We would like to have a field in Table A (Rollup presumably) to list the company of the agreement with the latest End Date. End Date is a date field in Table B.

Here is what I tried:

But I could not find a condition for the Date field to be the latest date. Is there a way to conditionally list only the Date field with the latest date?

Thanks, Khuned

TheTimeSavingCo · Accepted Answer

Yeap, but you'll need a couple of helper fields and I've set up a base here for you to check out and you can duplicate it to see the formulas and stuff!

The idea is to add a rollup field to your "Staff Members" table with a "MAX()" to display the latest date from the linked records in "Agreements". Then, add a lookup field in "Agreements" to display that latest date, and then a formula field to check that record's date against the latest date. Finally, add a lookup field to "Staff Members" to display the company name from "Agreements" based on the output of the formula field

Hi Adam @TheTimeSavingCo,

Thanks a lot for this. This answer what we need, but as you also mention, rather surprised that it requires such a detour. Happy that at least it can get done. Thanks.

Khuned_Sachdev · Answer

Yeap, but you'll need a couple of helper fields and I've set up a base here for you to check out and you can duplicate it to see the formulas and stuff!

The idea is to add a rollup field to your "Staff Members" table with a "MAX()" to display the latest date from the linked records in "Agreements". Then, add a lookup field in "Agreements" to display that latest date, and then a formula field to check that record's date against the latest date. Finally, add a lookup field to "Staff Members" to display the company name from "Agreements" based on the output of the formula field

Hi Adam @TheTimeSavingCo,

Thanks a lot for this. This answer what we need, but as you also mention, rather surprised that it requires such a detour. Happy that at least it can get done. Thanks.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded