Many-to-Many Challenge

[4Justme2]

I have been out of the Filemaker Pro arena for the past 8 years. I’m currently working to bring myself up to speed on FMP 11 Advance and have run into an issue and I can't seem to get my arms around a solution.

First let me start by identifying my primary tables, primary, foreign and match key fields, and the current database schema:

tblCONTACTS (fields: [pkCONID] primary, [fkHHID] foreign, [kContact_Type] match key and other fields)

tblHOUSEHOLD (fields: [pkHHID] primary and a bunch of address fields)

tblHOUSEHOLD_JOIN (fields: [fkCONID] and [fkHHID])

I have a join between tblCONTACTS::pkCONID on the one side and tblHOUSEHOLD::fkCONID on the many; likewise, I have a join between tblHOUSEHOLD::pkHHID on the one side to tblHOUSEHOLD_JOIN on the many.

The reason I used the join table is because the Contacts who are labled as "Child" from the [kContact_Type] field can belong in several different households (mom and step-dad, dad and step-mother, etc). In order to get a one-to-many relationship for this specific cohort I added a join table between tblHOUSEHOLD and tblCONTACTS.

The CONTACTS table is just as you would expect, a table that contains information relevant to all of the people in the database. The HOUSEHOLD table contains all of the addresses of the people in the CONTACTS table. The field [kContact_Type] in the CONTACTS table is used to distinguish between Children (<18 with no children of their own), and Participants (anyone else). [kContact_Type] field contents are drawn from a value list containing two words, "Child" or "Participant".

My ultimate goal: To have the ability to report on Contact activity as a group within the same household [HHID], individually by [kContact_Type]= "Child" or "Participant", and the really tricky part for me… I need to be able to look at these two groups by their roles as they relate to one another: One mom can have many Children, one [Child] will have at least one, morely likely two or more[Participants] a/k/a parents. And not all Participants are parents (some won't have any children).

I assume I need a seperate table for these relationship roles but I can't seem to work out how that would work.

I tried creating a table occurance based on CONTACTS in a self-join relationship hoping I could link by both the CONID and the [kContact_Type] of "Child" or "Participant"... but naturally that didn't work and I was looking at a mirror image of the main table. I tried seperating the contents of "Child" and "Participant" into two different fields and then creating a self-join... but I still got the mirror image since the join didn't distingish between fields that had null values.

Any help you can give me on how I might set this up to work would be greatly appreciated.

[comment]

There are several ways to look at this, and I am not quite sure where do you want to go next (you speak of tracking activities, but we don't know what exactly it means).

Let me suggest a possible approach, which is common in genealogy: instead of having a Type field in the Contacts table, add a Role field to the HouseholdContacts join table. This will allow each contact to have a different role in each household, e.g. Adam is "biological child" in the Smith household, but "adopted child" in the Jones household, etc.

[4Justme2]

By tracking activities I am referring to being able to identify when relationships change. Your suggestion is excellent. Although, within each household a person can have many roles. Mary could be the mother of John and the stepmother of Bill. That slight difference matters in the history I am looking to record.

[comment]

Although, within each household a person can have many roles.

All the more reason to put the role in the join table. You could have a person joined to the same household more than once - for example:

PersonID: 123

HouseholdID:45

Role: Spouse

From: 2005

Until:

PersonID: 123

HouseholdID:45

Role: Other woman

From: 2001

Until: 2005

Strictly speaking, however, neither "mother of John" nor "the stepmother of Bill" are roles in the household.

[LaRetta]

http://fmforums.com/...__fromsearch__1

http://fmforums.com/forum/topic/82925-portal-records-dont-match-the-table-it-was-based-on/page__fromsearch__1

I was not aware that you were already being assisted with the relationship, 4justme2.

I would prefer you take this over, Michael, since you are much better at relationships than I am. Should we merge threads or what would you like to do? I now hesitate to respond on the other thread.

Edited March 18, 2012 by LaRetta

[comment]

Your link leads back to here.

In any case, I don't want to take over anything (at least not in the forums...).

[LaRetta]

Fine. Then I will continue as if I do not know about this thread. I corrected the link in case others are interested.

Edited March 18, 2012 by LaRetta

[4Justme2]

That was my fault. My apologies. I just came across this post I started back in February. I probably should not have responded since I already have the topic reopened in the latter post. It just goes to show how I have been going around in circles on how to handle the relationship between the parents and children. I do apologize for the confusion. I'm still really new to this and appreciate everyone's patience with me. Will someone stil be able tol get back to me about the other post? (Portal records don't match the table it was based on). I still have questions before I re-combine the two tables into one again. I need to make sure I understand how I can isolate the child records from the parent records for reporting purposes. Also, there were specific funtions in the Household file that I was unfamilar with and wondering if someone could provide me with a brief explanation of how they work.

[LaRetta]

I plan on responding on the other thread in a bit. :)

[comment]

I corrected the link in case others are interested.

I see now what you mean - indeed this seems to be a "prequel" to the other one. However, even after reading both I am still not sure what the exact purpose of the solution might be.

[LaRetta]

Coming from 'child care' industry, I immediately pictured the need to track family dynamics and relationships but I can be wrong.

[4Justme2]

Coming from 'child care' industry, I immediately pictured the need to track family dynamics and relationships but I can be wrong.

You are correct. I'm writing more on this on the related thread.