Relationship help - Doctors/Patients

[fmphysio]

Hi,

I'm struggling conceptually to create a relationship between doctors and patients.

Two tables:

a) Patients; and

: Doctors

Each has a unique identifier for doctor [D(x)] and patient [P(x)].

I want to create a relationship where a doctor could be linked to multiple patients, and also where a patient could be linked to multiple doctors.

I'd like to then be able to display and be able to manipulate these links between doctors and their patients via a portal.

I've tried simple methods, but with no luck.

Can anyone suggest a way I might accomplish this?

Thanks,

fmphysio

[efen]

You need a third - join - table for this to connect doctors with patients. There are many examples of this on this forum - see

http://fmforums.com/forum/showtopic.php?tid/209790/post/334501/fromactivity/myposts/#334501

[fmphysio]

Thank-you!

I've been playing around with a link field, essentially is just a text field with ID's separated on each line. This seems to work, but the join table seems easier.

Thanks efen

Edit: Join table works fine for adding a new record. But how can I delete the link between Patient and Doctor, without manualy searching for the record in the join table?

When I try linking to a related record in the join table from the doctor portal (on the patient layout), I only get the first record from that patient, and it doesn't take into account the doctor which is listed in the portal row.

Edited September 9, 2009 by Guest

[efen]

Attached is a script for deleting the link between products and orders in the file attached to the other post.

Use the script on a button placed in the "options" portal row.

Regarding the other question I don't know how you have set up your file - which is the doctor layout, which is the patient layout/ i.e. how do these relate to the example file?

[efen]

I have amended my original file - does this solve your problem?

doc_patients.zip

[fmphysio]

That's perfect and exactly how I inteded it to work. Thank-you!

I looked into the difference in your script and the one I wrote, and I think I'm going about it the wrong way.

Where you have entered find mode and then set the fields using $patient and $doctor, I just created a find where those fields were set to those strings as well. I don't know how to call it, but my script had perform find [restore], which is likely my problem.

It created the search, and instead of placing the values (e.g. doctors name) in the field to search, it simply typed "$doctor". Hence I never got the right answer.

I cannot thank you enough for your help.

[comment]

I don't think you need the find at all.

doc_patients.fp7.zip

[fmphysio]

WOW that's even easier. I was worried that by deleting the portal row, I would in fact be deleting the patient, rather than the relationship.

I guess this isn't the case. However, where your approach falls down is that if someone is lazy and doesn't look to see if a doctor is there, the doctor will get listed twice, and you will see this doctor twice.

Perhaps if the Patients_all portal could get filtered to omit those patients who already have a relationship, and thus removing this as an option?

It's interesting there are these two approaches to displaying this data, even though the database relationships are the same.

[comment]

I was worried that by deleting the portal row, I would in fact be deleting the patient, rather than the relationship.

That's exactly what would happen the way you had it before, when the portal was showing records from the Patients table.

However, where your approach falls down is that if someone is lazy and doesn't look to see if a doctor is there, the doctor will get listed twice, and you will see this doctor twice.

I am afraid I didn't get this part.

[fmphysio]

Ahh yes this is true.

Sorry I meant to say, look to see if a patient is there. What I mean is if I click to add "Patient 1" 3 times, I see Patient 1 listed in the portal 3 times.

Whereas by showing the a portal of the patients, regardless of how many times this link between doctor and patient are listed in the join table, you'll still only see it listed once.

Is there any way to avoid this say at the time of adding the patient? i.e. remove this patient as an option from the list, or only adding unique records to the join table?

[comment]

Actually, you should do both - set a validation for unique joins AND build the user interface to prevent duplicate entries.

For the validation, define a text field in the join table that auto-enters (with replacing) the concatenation of the two foreign keys, and validate it for being unique.

For the "dwindling" of options, see:

http://fmforums.com/forum/showpost.php?post/233897/

[fmphysio]

Ahhhh thank-you. This is exactly what I need to know. Cheers :