Finding the most recent record in a subset of records

[Peter_Steinberg]

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

[JonathanBowen]

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

[Peter_Steinberg]

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

[Richard_Cawthor]

Hi @JonathanBowen,
Your “Most Recent Investment” solution worked perfectly for me. My only remaining problem is how to use the date in the Rollup to display associate data in the Deals table. Lets say the Deals table contains a text field called “Purchase” which describes what was purchased on the Investment date. How can I display the purchase text for Deal B that is associated with 22/4/2019 in Most Recent Investment? Would be grateful for any assistance you can give me.

[JonathanBowen]

Hi @Richard_Cawthorn - unfortunately, there’s no way to access another field of a rolled-up field.

[Richard_Cawthor]

Thank you for saving me alot of wasted time banging my head over that.