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?