Hello all,

I've got a table for patients and another table for visits. I need a list of patients with their next appointment date. Each patient has multiple appointments from the past and for the future.

I tried creating a calc in visits that said:

case(appointment date > get(currentdate); patient ID)

Then I could build a relationship from the patients table to the visits table based on Patient ID.

That of course works once: since you can't build a relationship on an unstored calc the current date doesn't update.

Is there a custom function I can use?

You don't need a custom function for this. In your Patients table, define an unstored calculation field cToday (result is Date) =

Get (CurrentDate)

Then define your relationship as:

Patients::PatientID = FutureVisits::PatientID

AND

Patients::cToday < FutureVisits::AppointmentDate

where FutureVisits is an occurence of the Visits table. This relationship will show all future visits of the current patient.

Edited December 19, 200619 yr by Guest
corrected a typo: reversed < sign

Excellent.

Thank you!