Relationships help for "relationships"

[fmphysio]

Okay, slightly confusing in terminology but, I'll try to be as clear as possible.

I have a database that records various aspects about patients. Often, we find two two patients will be related, e.g mother and daughter, or siblings.

I would not only like to capture this information, but also be able to add other family members in, but not necessarily add them as Patients.

So the tables so far are:

PATIENTS - all info about patient

FAMILIES - info about family as a whole

Now what I'm aiming to do is have some sort of a join table that looks a bit like this:

JoinID | FamilyID | PatientID_1 | Relationship_1 | PatientID_2 | Relationship_2

So here's an example of how data would be entered:

1 | 34 | 186 | Mother | 263 | Daughter

- so patients with ID's 186 and 263 belong to the Family (34) and are mother and daughter.

If the mother wasn't a patient, the ID field would just be left blank, and she would exist only in the join table.

I want to display this information in a portal on each patients record, and list all famly members and their relationship to the patient.

Thus, any patient could occupy either PatientID_1 or PatientID_2. So I created two occurences of this table and made relationships as such.

My drama is getting the relationships right (perhaps an extra table for showing the family is needed) so that I can view them in the portal.

What I want to see is the following:

(PORTAL)

(Family ID) | Patient ID | Relationship

(36) | 123 | Father

(36) | 231 | Mother

(36) | 112 | Sister

(36) | 172 | Sister

(36) | 100 | -- as this would refer to the patient being included into their own family list.

Any help on this idea would be great. I feel like I'm going around in circles, and I wonder if my relationships graph is too.

Thanks :

[fmphysio]

Okay, still trying to figure this one out.

What I've included now is a calculation field in the PATIENTS which basically says, if the PatientID matches PatientID_1, then use Relationship_1, but if it matches PatientID_2, use Relationship_2.

And this works fine, but only displays two values which correspond to the first record in the join table.

How can I get it to recognise all the relationships in the join table?

[Søren Dyhr]

Well the rest of us are trying to figure out what you are saying :

However is the extensive use of calc'fields you suggest not a way I would pursuit - I would instead exploit a feature of "Allow creation of related..." as JMO goes on about here:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

...there would possibly be tradeoff with that approach as well, but it seems to be working according to your meager specs. roster.

--sd

Ties.zip

[fmphysio]

Thanks Søren for a quick reply.

Yes this does work to a degree. You are assigning a "role" to each patient, such as this person is the "mother or father". And this would work if you consider you'd only ever view the family as a whole unit. This may have to be the compromise for the time being.

I was hoping to take this a step further and rather than assigning a predetermined "role" to someone, have it figure out the 'role' based on which patient record is open and the related records seen in the table.

For example, Dave is John's Son.

If I viewed Dave's family, I would see John (father). However, if I viewed John's family, it would show Dave (son). And as these records are related to each other using a familyID, they would also see themselves (highlighted), but the relationship would be blank.

How I'm considering getting around this is rather than assigning a "role" to a patient, assigning a family position number.

So, for instance, parents are always +10, children are always -10. Using the example of Dave and John above, I would assign Dave (10), John (20). Then use a calculation field to subtract one from the other, and then based on the result spit out the relationship (as well as confirming the sex of the patient - mother/father, daughter/son).

As these are all related by FamilyID, it shouldn't be a problem to use the same numbering system for other families.

Does this seem like a good approach?

If I can get this working, I'll post up the file.

[comment]

I'd suggest you try first to work the linkage issue with all people in the same table - perhaps this might help:

http://fmforums.com/forum/showtopic.php?tid/171427/

I'd think twice about keeping patients in a separate table anyway, and especially so if they are to be linked to non-patients. At most, I'd consider keeping patient-specific data in a subtype table.

[fmphysio]

Thanks comment, yeah i totally agree with keeping them separate.

I'm not sure if this is the correct term, but I have a self linked table?

Basically two tables Patients and Familys, linked via the FamilyID field, and a duplicate of Patients called Patients_Patient_family linked to Patients via the FamilyID field.

Then the portal displays related records from patients_patient_family, and all patients with the same FamilyID are listed.

I'm starting to make some headway on this problem.

By using a script trigger on loading the record, I can set a global variable to the current patient's FamilyPositionNumber, and using a calculation field get the difference between the Current patient's position number and the related patients number.

Then with another calculation field display the relationship based on the result. So far I have this list:

+20 - grandparent

+10 - parent

0 - self(if ID's match) or sibling

-10 - child

-20 - grandchild

Then obviously I'll need to create another script to set the PatientPositionNumber, based on the relationship I'd like to make.

A work in progress...

Thanks for all the help so far.

[comment]

i totally agree with keeping them separate.

I'm afraid you may have misunderstood me: I suggested putting all people in the same table, NOT keeping them separate. Then you would have a "self-linked table", but with a twist: it needs to be linked by a join table, because the relationship is many-to-many - and you need a place to record the nature of the relationship.

[fmphysio]

Ahh yes, sorry.. **together**.. I did agree. Currently all patients are in the same table, it was only a passing thought that I might want to list family members that aren't part of the patient group, and I think for *that* I would want a separate table? I dunno, I easily confuse myself.

I did try the whole join table between the two patient tables, and I could never get it to work as well as I'd wanted, I think I was always missing someone in the family.

Although first trial of the calculation fields was successful.

I'm using the following fields to figure out the relationship:

FamilyID: links the records

FamPosNum: family position number (any numbers 10 apart)

$$FamPosNum: the current opened record's FamPosNum (linked to a script trigger that will refresh this value and the screen when the record changes)

Relationship1: =FamilyPositionNo - $$FamPosNum

Relationship2: =Case( Relationship = 0; "self or sibling"; Relationship = 10; "Parent"; Relationship = -10; "Child")

So to include more relationships and work it better I'll just adjust Relationship2 to really figure out what's going on incorporating on the basis of PatientIDs (matching = self, not = sibling), based on sex - rather than parent have mother/father, and I might even add another field for marriages.. so you just add the PatientID of the person married to, and then instead of two people with a difference of 0 coming up as bro and sis, it would say husband and wife.

Edited December 11, 2009 by Guest
typo

[Søren Dyhr]

I was hoping to take this a step further and rather than assigning a predetermined "role" to someone, have it figure out the 'role' based on which patient record is open and the related records seen in the table.

Isn't it - in my approach, just add and extra value to the role value list and call the person "Individual" if nothing else really fits?

--sd

[fmphysio]

Yes totally, your solution would work for a "self" relationship.

I could name someone "child or son" and that would be fine if I was looking at this relationship from the parent's perspective or even viewing the family as a whole.

But what if I wanted to look only from the grandparents perspective? I want it to change to say "grandchild or grandson".

With only a single word attached this wouldn't be possible.

However, what I'm doing now is assigning "100" to the first person added to a new family number, and then adding relationships relative to this person, using an expanded version of the scheme listed above, where the difference in these numbers determines what the relationship is.

If I get some time I'll create an example file.