Help

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

Topic Labels: Formulas
Solved
Jump to Solution
521 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Khuned_Sachdev
6 - Interface Innovator
6 - Interface Innovator

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:

Screenshot 2024-04-08 at 03.49.42.png

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

 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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!

Screenshot 2024-04-08 at 9.53.20 AM.png

Screenshot 2024-04-08 at 9.53.18 AM.png

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

See Solution in Thread

4 Replies 4

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
18 - Pluto
18 - Pluto

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!

Screenshot 2024-04-08 at 9.53.20 AM.png

Screenshot 2024-04-08 at 9.53.18 AM.png

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.  

@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.