extended search/find

[dervaish]

hello...

me again... : ... i have a script which finds all the non-blank records and show them on a layout [listview]. records are about patients and their appointments [1 patient can have multiple appointments] and each appointment is assigned a status [urgent booking required, to be booked, book appointment, booked]. for now the find-script shows the records as required. but i want to further narrow it down to a single appointment-record / patient. my issue is that i want to show only the highest priority appointment for each patient [urgent booking with highest priority, booked with the least]. so if a patient has 2 urgent appointments and 3 to-be-booked appointments then i want to show 1 URGENT APPOINTMENT ONLY [doesn't matter which one it is of the two] and omit/hide the other related appointments. and i want to do this for all patients since the layout is a listview setup.

so how can i accomplish this? i have spent all night trying to figure out a way to do this but so far no luck. 9 hrs of hard work but to no avail :

all the info is stored in a single appointments-table, which is linked to a mater table [patient-info table] through Patient-ID [PK in patient-info table, fk in appointments-table].

Patient-Info-Table::Patient-ID -|< Appointments-table::Patient-ID [relationship]

thanx in advance for your help.

[comment]

if a patient has 2 urgent appointments and 3 to-be-booked appointments then i want to show 1 URGENT APPOINTMENT ONLY

It sounds to me like you want to find PATIENTS, not appointments. So why don't you do just that: go to a layout of Patients, enter Find mode and put the search criteria into the related Status field from Appointments. That will find all patients with urgent appointments.

Alternatively, you could find the urgent appointments in a layout of Appointments, then do Go to Related Record [ from Patients ; Match Found Set ]. Same thing, perhaps a bit faster.

[dervaish]

thanx for the reply. actually i want to find appointments, not patients. second a patient may not have any urgent appointments but only booked appointments. in that case i have to show one booked appointment only and so on. this layout is only for appointments. i have a separate layout for patients.

so the question remains, how can i find the highest priority appointment for a patient and show only one if there are more than one matching high priority appointments. currently i have two dummy records with 4 appointments each. my find shows all 8 records. in these two dummy records, there are 3 urgent appointments for patient A and 1 urgent appointment for patient B. i only want to know 1 urgent appointment out of 3 for A and 1 urgent appointment out of 1 for B. lets say patient B doesn't have any urgent appointment and the high priority appointment is TO BE BOOKED appointment. then in that case i'll show TO BE BOOK APPOINTMETN only once [if there are more than one matching appointments for B.]

as a side note, a patient can have 11 appointments at any given time. so there will be 11 records for a patient and can have multiple URGENT or TO BE BOOKED or BOOK APPOINTMENT or BOOKED APPOINTMENTS and their priority order is as such

HIGHEST - URGENT BOOKINB REQUIRED

2nd - TO BE NOOKED

3rd - BOOK APPOINTMENT

LOWEST - BOOKED

if there is no URGENT APPOINTMENT then 2nd will get the highest priority and so on. but in any case, i only want to show one high priority appointment / patient no matter which one is it.

[comment]

actually i want to find appointments, not patients. second a patient may not have any urgent appointments but only booked appointments. in that case i have to show one booked appointment only and so on. this layout is only for appointments. i have a separate layout for patients.

Thanks for clarifying - but I still think you are looking for patients. You want a list of patients that have appointments, and next to each patient you want to see the most urgent appointment that the patient has.

This can accomplished quite easily by sorting the relationship between Patients and Appointments by urgency (or defining another such relationship), and placing related fields from Appointments directly on a layout of Patients (showing data from the most urgent appointment).

It COULD be also done in a layout of Appointments, by producing a report summarized by patient, and sorted by patient, then urgency. The sub-summary by patient would show details of the patient and the first (or last) appointment for the patient, and the body part would be deleted from the layout. But you would need to go into Preview mode to see this report.

To do exactly what you describe, you would need write a looping script that would sort the records in the same way as for the report, then loop and omit all but one record from each group. A lot of unnecessary work, IMHO.

---

I'm afraid you've lost me with your explanation regarding dummy records. I don't see why you need them, or why "a patient can have 11 appointments at any given time". I believe there should be exactly one record for each REAL appointment - no more and no less. A patient should be able to have ANY number of appointments (unless there is some business rule to restrict them).

[dervaish]

thanx for the help. i have tried your suggestion and its working but only upto 50%. i'll try to modify it to make it work fully and give me the results as expected.

once again thanx.

[comment]

Here's a file showing the two methods I described - hopefully working at 100%...

PatientAppointments.fp7.zip