Most Recent Record-Related table

[bb84604]

I have 3 tables: Dashboard, patientInfo, and appointmentInfo.

I am trying to use portal filtering on the dashboard to display patients who have appointments on a certain date.

In patientInfo I have a calculation field called lastAppt that has the calculation: Last (appointmentInfo::appointmentDate). I have the relationship sorting records in ascending order by the appointmentDate field. I am thinking this returns the value of the last appointment date in the related records in the appointmentInfo table (and it looks like it is).

I have a field in the Dashboard table called lookupDate which is related to the patientInfo table by a relationship where lookupDate = lastAppt. I have a portal in a Dashboard view that pulls in records from patientInfo but when I put a value in lookupDate it doesn't bring in any records. I think it should be pulling in all the patients that have appointments on that date (it's not).

Any ideas on what I'm doing wrong?

Thanks very much.

[comment]

It cannot work, because the calculation field lastAppt is (and must be) unstored - therefore it cannot be used as the matchfield on the child side of a relationship.

Why do you need to limit the display to patients whose last appointment is on the lookupDate? Shouldn't you be showing all patients that have (any) appointment on that day? This could be done by pointing the portal to the Appointments table instead.

[bb84604]

I need to be able to list patients in a portal on the dashboard that have had an appointment in the last six months for example. I wanted to put in a start and end date for their last appt.

Patients could have had multiple appointments during that time but I only want them listed once and to show their last appt.

Thanks for the help so far. I'm halfway there.

[comment]

Attach another occurrence of Patients to the filtered Appointments, and switch the portal to that. i.e.

Dashboard -< Appointments >- Patients