AnsweredAssumed Answered

Many-to-Many Challenge

Question asked by 4justme2 on Feb 20, 2012
Latest reply on Mar 9, 2012 by 4justme2


Many-to-Many Challenge


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 I could use some help with.

First let me start by identifying my primary tables, primary key and foreign 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 Child from the [kContact_Type] field can belong in several different households (mom and step-dad, dad and step-mother, etc).

The CONTACTS table contains info related to all these people. The field [kContact_Type] is used to distinguish between Children (<18 with no children of their own), and Participants (anyone else 18 or older).

I need the ability to report on 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] can have many [Participants] a/k/a parents. And not all Participants are parents.

Originally I tried creating a table occurance of the tblCONTACTs in a self-join relationship hoping I could link by both the CONID and the Contact_Type field with a filter on Child only records. As I understand, you can’t filter on a table.  Can anyone advise me on the best way to set these relationships up? Can I do this without having to separate the Contact Table in tables based on the categories of contact_type, Child and Participants?