Relationship problem ... perplexes me

[ron G]

I am using FM 12 03 advanced.

 

Here is the TOC graph:

 

The relationships are pretty simple.  There is a main Patient table that holds a tab structure.  Each tab holds a portal that is the 'many' side of the relationship.  On most portals I need to know the doctor who issued a prescription, performed a procedure etc... Hence, the duplication of the Doctor table (in red).  This works.

 

On the RX input portal I have this:

 

I produce a report based on the RX table that shows the active prescriptions for a selected member.

  This works.

 

The problem:  (It seems like this should be a no brainer but I just can't 'get it' grrrr). 

I want to designate one doctor as the 'primary' doctor for a patient and to be able to put this doctor's name and phone number on the above layout.   (As shown above)

 

I tried adding a 'PrimaryDoctor' field to Patients.... but that won't work.  (It seems like it 'should' work since a patient has only one Primary physician; therefore that information would belong in Patients? ?

 

I thought of adding a separate table called "PrimaryDoctor" which would hold the PK for the PrimaryDoctor but that seems 'kludgy'. 

 

What are your thoughts?

 

Thanks for reading...

 

Ron

[LaRetta]

Hi Ron,

I would suggest a PrimaryPhysicianID field in Patients where you select the DoctorID. This is same method used to specify a single primary address for a company out of many related addresses.

BTW, you do not need the red table occurrences if they only provide the doctor name. A value list based upon field values, all values from DoctorID also showing doctor name, and showing only value from second field ... attached as a pop-up with entry to browse unchecked provides the doctor's name without a relationship. :-)

[ron G]

Thanks LaRetta,

I will have to see what happens when I remove all the Doctor TOCs. 

 

RIght now I got the Primary Doctor to show up on both layouts.  But, 'Don't I need a relationship between Patient and Doctor to pull the Primary  Doctor 'PhoneNumber'?

 

To that end, I tried creating a TOC called Doctor_Patient and inclued FK_PatientID to link the Patient with their Primary Doctor but that didn't work.  Surely there is a simple way to do this 'rather simple task' that I am apparently being just too bone headed to see how ... Thanks for the 'light'.

 

R

 

Nov 18, 00:34 PS here is the new TOC structure;

 

But, I am going bonkers trying to figure out the best way to get the Doctors::OfficePhone to show up when the primary doctor is selected....  You mentioned an address selection similarity.  Can I read about this technique somewhere?

 

Thanks

 

Ron

[LaRetta]

If you need additional doctor information then yes you will need table occurrence of doctors attached. You said doctor name and I see a lot of folks attaching table occurrences needlessly. If name only then yes it works. But if only additional phone then I always concatenate name and phone to display the 'unique person' in case there are two Jim Browns anyway. Otherwise you would not know which Jim Brown to select because both would show in the pop-up if based upon their IDs but displaying only name.

Why do you say TOC? TOG is table occurrence group. TOC is table of contents as far as I know. But the suggestion of the pop-up is a general recommendation and has nothing to do with assigning a primary physician. Put DoctorID in Patients, fill with doctor ID, attach table occurrence of doctor to it to know the primary physician. :-)

ADDED. Also, it is sad that you deleted those table occurrences ... but I clearly said, "you do not need the red table occurrences if they only provide the doctor name."

Edited December 18, 2012 by LaRetta

[ron G]

If you need additional doctor information then yes you will need table occurrence of doctors attached. You said doctor name and I see a lot of folks attaching table occurrences needlessly. If name only then yes it works. But if only additional phone then I always concatenate name and phone to display the 'unique person' in case there are two Jim Browns anyway. Otherwise you would not know which Jim Brown to select because both would show in the pop-up if based upon their IDs but displaying only name.

Why do you say TOC? TOG is table occurrence group. TOC is table of contents as far as I know. But the suggestion of the pop-up is a general recommendation and has nothing to do with assigning a primary physician. Put DoctorID in Patients, fill with doctor ID, attach table occurrence of doctor to it to know the primary physician. :-)

ADDED. Also, it is sad that you deleted those table occurrences ... but I clearly said, "you do not need the red table occurrences if they only provide the doctor name."

 

 

Yes, I need additional doctor information put on the RX Layout.  This is what my TO's look like:

 

 

What relationships should I establish between a 'NEWDOCTORS' TO and presumably the Patient::PrimaryDoctorID???  I have tried several, and nothing seems to work.

 

Thanks LaRetta

 

R

[LaRetta]

Put a DoctorID in your Patients table (maybe name it PrimaryDoctorID) and attach a pop-up with value list based upon DoctorID and also display secondary field doctor name - show values only from second field.  Create another table occurrence of your Doctors table, calling it Primary Physician (or whatever you wish).  Join as:

 

Patients::PrimaryDoctorID = Doctors::DoctorID

 

In Patients, select the primary doctor from the pop-up. :^)

[ron G]

Put a DoctorID in your Patients table (maybe name it PrimaryDoctorID) and attach a pop-up with value list based upon DoctorID and also display secondary field doctor name - show values only from second field.  Create another table occurrence of your Doctors table, calling it Primary Physician (or whatever you wish).  Join as:

 

Patients::PrimaryDoctorID = Doctors::DoctorID

 

In Patients, select the primary doctor from the pop-up. :^)

Laretta,

Worked like a charm!

Now that you 'turned on the light' for me, it makes a lot of sense.  I have to connect Patient { -- }} Doctor with a popup based on Doctor so that the PrimaryDoctor can be selected.  When this is done, PrimaryDoctor holds the PK of the Doctor selected.  To get the OfficePhone, for example, of the PrimaryDoctor, I needed another 'instance' linked between the PrimaryDoctorID and Doctor2::PKDoctorID. 

 

Thank you for your help.

 

R