Finding the most recent record in a subset of records

#1

I have a table that is a list of people. Call it “People”. Say there are 5 people.

Then I have a table that’s a list of deals. Call it “Deals”. Say there are 3 deals.

Each Deal record has a “closing date”.

Then I have a table that’s a list of investments made into Deals. Call it “Investments”.

Each person can invest in each deal. So there are as many as 15 records in the Investments table – one for each intersection of Person and deal.

I’m trying to come up with a way where, in the People table, I can see the date of the most recent investment each person made. And then I want to sort by that date. This way I can see who hasn’t invested in at least X months and I can reach out to them to try and reactivate them as an investor.

I’ve noodled on this for some time but no solution has come to me. Any ideas?

People can make Investments into Companies.

People can make as many investments as they

#2

Hi @Peter_Steinberg - if this is your Investments table:

Back in your People table you will have this:

The {Most Recent Investment} field is a rollup formula:

So, a rollup of the {Investment Date} field from the Investments table using MAX(values)

You can then sort by the rollup field.

JB

#3

JB-

This did the trick. You’re the best. Thanks!

(I knew of the Rollup field type – I just didn’t recall that you could apply functions to the results.)

-Peter

1 Like