Finding the most recent and next dates

[Kelly_Brask]

Greetings wise ones!

I have a base to track family medical history which includes a Doctors table and a Dr Appointment table. All appointments in the Dr Appointment table are linked to a doctor in the Doctors table. This includes past appointments and upcoming scheduled ones. In the Doctors table I would like one field where the date of the next appointment is calculated and shown and one where the most recent appointment is calculated and shown. How do I write a formula/rollup that looks at only the past or only the future dates and chooses the last of the past or next of the future?

I’ve created fields calculating the appointment dates from TODAY if that helps.

Thank you!

[Vivid-Squid]

Hi @Kelly_Brask,
Since you have the Doctors table linked to the Doctors Appointments table, you should be able to add a roll up on the Doctors table of the appointment dates, and use the MAX(values) to find the highest date. MIN(values) to find the lowest date.

If you have a status on your Doctor Appointments, you can filter to only show most recent completed appointment.

[Kelly_Brask · Accepted solution]

Thank you @Vivid-Squid ! That wasn’t quite it, but you put me on to the idea of conditional Rollups (which I’d read about but hadn’t used yet).

and

gave this (exactly what I wanted)