Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Finding the most recent and next dates

Solved
Jump to Solution
157 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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