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

[Khuned_Sachdev]

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

[ScottWorld]

Surprisingly, this is a very tricky feat to pull off in Airtable.

I give step-by-step instructions on how to do this on this Airtable podcast episode.

p.s. If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo · Accepted solution]

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

[Khuned_Sachdev]

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]

@ScottWorld thanks for the explantion.  I will definetly keep you in mind for our expertise needs.  We are real no-coders and building apps by the actauly operations team, so it seems the time will coming soon, that we stick with the archotecture of our needs and ask experts to help build them.