Help

Re: Finding the most recent and next dates

Solved
Jump to Solution
885 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Kelly_Brask
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Kelly_Brask
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Last dr visit

and

Next dr visit

gave this (exactly what I wanted)

dr visits

See Solution in Thread

2 Replies 2

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.

image

image

Kelly_Brask
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Last dr visit

and

Next dr visit

gave this (exactly what I wanted)

dr visits