Set a field to an "indirectly" related field

[Bilbo28]

Hi,

I have one more problem I would like to ask you concerning setting a field to one from another table.

I have a many to many reltionship between Patients and Doctors tables using a linking table (Doctor_Patient Relation), as a patient can have several doctors and a doctor can follow several patients at the same time.

I am also a doctor and I have to make a report on a given patient and this is why I use Report Table related to Patients via "Patient ID".

Now, when I create a report, I have to select a doctor in the report table to send him the report afterwards, and I do this via Doctor ID drop down list. As a patient can later change his GP and because I need my report not to be chaged subsequently, I have decided that I also have to store Doctor's Name is Report's TO.

So my question is how to set Report::Doctor Name to Doctors:Last Name automatically as soon as I have selected a Doctor ID from the drop down list in the Report layout?

I have been able quite easily to do this using another occurence of the Doctors table, but as I will have about 10 different Reports tables, this would mean that I will also have to create 10 different occurences of Doctors Table, which would complicate quite a lot the relationships chart.

I was therefore wandering if you had something smarter to propose. If so, could you please provide me with the script or calculation?

Many thanks for your help.

Patients_2.zip

[comment]

I would probably do it this way. Note that looking up the doctor's name into the report table is not strictly necessary, since it is available through the relationship.

If a patient changes their doctor, you need to create a NEW join record, and terminate the previous one (e.g. by entering a date into a Terminated field).

I will have about 10 different Reports table

Is that really necessary?

Patients_2a.zip

[Bilbo28]

Absolutely, différent types of medical exams = différent reports. In addition i dont want to have more than 1000 fields if i have only a single table.

[comment]

Well, I don't have enough details here to form an opinion, but I would still suggest you re-examine your approach.

For example, a structure of:

• Reports

• ReportTypes

• TypeFields

• ReportValues

can provide an unlimited number of report types with only 4 tables - and with no need to change the file's schema when a new report is needed or an existing report's structure needs to be modified.

[Bilbo28]

Thx a lot, very interesing change in the scheme, I am really impressed. I will definitely do this.

Concerning my database structure, I don't know if I should redo everything, because I am quite advanced now, eventhough I still have a long road to go.

Nonetheless, if you agree to have a look at my database, I can send it to you by e-mail. I woudn't like to post it, because I am not sure that I have anonymized everything and in addition a friend of mine started to do it, so I do not have all the rights on it. So if you provide me with your e-mail, I can send it to you.

Thx again

[comment]

if you agree to have a look at my database, I can send it to you by e-mail.

Sounds too much like work, I'm afraid...

[Bilbo28]

I can easyly understand this ;-))

Maybe you could be a little more explicit about your proposal of 4 Tables for the reports and about the Termination for a patient-doctor relationship?

[comment]

You only need a termination date if you want to track who are the patient's CURRENT doctors. Otherwise you could just leave it as it is - but the important point is NOT to delete or edit the join record when a patient changes doctors, but create a new join record instead. This way you keep a history of patient's doctors, and your previous reports will not be orphaned.

As for the reports, I am attaching an ERD showing the concept. Note that this is not a relationships graph, and to implement this in Filemaker you will need additional occurrence of the same tables. Not exactly an "entry-level" task, I am afraid, but I believe it will be time well-spent.

[Bilbo28]

Thx a lot.