1 2 Previous Next 27 Replies Latest reply on Mar 9, 2012 1:48 PM by 4justme2

    Many-to-Many Challenge



      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?


        • 1. Re: Many-to-Many Challenge

          Is this the relationship you have to start?


          tblHouseHold::pkHHID = tblHouseHold_Join::fkHHID

          tblContacts::pkCONID = tblHouseHold_Join::fkCONID

          That's not exactly what you posted but I think/hope it was a typo. Wink

          and now you need to see the child to adult relationships for a given child or given parent? What's not quite clear is whether you care whether a given adult is actually a parent or simply an adult in the same household (participant).

          In FileMaker 11, you can set up a portal to tblHouseHold_Join with a portal filter expression that limits the records in the join table to just those of a specified type. This can be "hardwired":

          tblContacts::contact_Type = "Participant"

          or it can be in a field where the filter refers to an added field to make the filter selectable:

          tblContacts::contact_Type = GlobalsTable::gSelectedContactType. (I'm specifying a field with global storage here, though it's not strictly necessary to do so.)

          With this second option, you'd best use added occurrences of the join and contacts tables so that you can include a X relationship based on this gSelectedContactType field so that the portal updates smoothly when you select different  values in the gSelectedContactType field.

          • 2. Re: Many-to-Many Challenge

             Thanks so much for taking the time to look at this Phil.

            * You are correct about the typo and in understanding what I meant to say vs. what I actually wrote about the relationship of the tables.

            * "...you need to see the child to adult relationships for a given child or given parent?" [I would be nice to have the option to look at this from either perspective where I have one layout with the child to parent view and another with parent to child]. Is there any chance that might be possible?

            *"What's not quite clear is whether you care whether a given adult is actually a parent or simply an adult in the same household (participant)." [Doesn't matter-seeing all adults in the same household is probably best].

            *Just fyi so you know what I've done--I used the hardwired filter solution you mentioned above--it worked well.

            The filter on the portal is working in that I am no longer seeing the child records (except the name which is from a calculation--I don't know if that matters--I dont' know if that matters--the calcuation is still coming from the context of the CONTACT table).

            Also, now I'm seeing the same records on the portal as on the layout.  The portal is based on Household_join the context of the layout is based on Contacts. 

            What looking for is the Child records only on the layout and related adult (partipcant) records in the portal... those adults living in the same household as the child.

            Also, I'm only seeing one adult per portal on each record. Is there a way I can see all of the adults living in the same household on the same portl?



            • 3. Re: Many-to-Many Challenge

              Hmmm, seems like I left out a detail. And that's on what layout to place the portal. I was specfiying that you place this filtered portal on the household's layout to see all contacts from a given household of a specified type. From what I see here, you can only identify the adults living in the child's household or households and conversely list all the children living in an adult's household.With scripting, such a layout could display a selected child and the portal display all adults from a given household or a selected parent and all children from a given household.

              That's where I wondered if this was really what you needed or If you needed to somehow to distinguish between "Jane Smith" the child's mother and "Fred Jones" who happens to live in the same household but has no direct relationship to the child...

              • 4. Re: Many-to-Many Challenge

                That is really wonderful.  I can see the whole family and I see the child both places when they live in more than one household.  Now I have another issue in which same regard.  I'll use an example:

                Two different households (HH)

                Household 1: Mary, Dan and the kids John and Barry (Barry is Dan's stepson)

                Household 2: Joe (mom's ex) and Barry (child shared between Joe and Mary from an earlier marriage)

                All of the people are stored in the Contacts table

                Where can I capture the relationships to the children?

                Mary, mom to John

                Mary, mom to Barry

                Dan, dad to John

                Dan, stepfather to Barry

                Joe, father to Barry

                I was thinking I could do it at the address level but naturally I can't--it needs to be at the individual contact level.  How would you suggest I do this?  With another table?  I'm trying to think of how that would work.  Someone enter's Mary's contact information in the system. When the field that identifies Mary as a parent at script is triggered that links to another table to add a relationship?  I get that far and then I'm stumped.





                • 5. Re: Many-to-Many Challenge

                  Yes, I'd use a separate Join table to document familial relationships as these are independent from your household data--though it may be of assistance when you work through the process of documenting relationships. A field in such a join table can identify the type of relationship.

                  Due to questions by others about many to many relationships, I ginned up a demo file and then worked out some alternative methods of working with the data so I could share it with others. You may have seen it already, but if not, it may have a trick or two in it that you'll find useful:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

                  • 6. Re: Many-to-Many Challenge

                    Wow, I hadn't seen that example you included.  I'm so impressed.  I'm going to sit down right now to try and digest it. I love how you annotated everything.  It's going to be so helpful.  It would have taken me ages to figure out something like that on my own (and I do mean ages).  Something like this would solve my issues around this database configuration.  I can't thank you enough!!!

                    • 7. Re: Many-to-Many Challenge

                      I am so thrilled that you shared this example with me.  Just the way you color everything and annotated the whys, hows and wheres... I can actually follow this.  What a wonderful tool.  I notice you have two globals fields in the Contact table. I don't understand how global fields work, in general--they intimate me. Would you mind giving me a brief explanation of how this work in this example?

                      • 8. Re: Many-to-Many Challenge

                        Fields with global storage specified on the storage tab in field options store only one value for all records in the table. They are also accessible from all scripts, tables, layouts in your database as you don't need a relationship in order to access them from another layout or table in your database.

                        If I have 10,000 records in my table and enter "Apple" into a text field that has global storage specified, all 10,000 records will show "Apple" in this field. If I switch to a layout based on a different table and add this field to it, it will also show "Apple" no matter what relationship (if any) is defined between the two tables.

                        If your database is hosted so that multiple users can access the database at the same time, global fields show an additional set of behaviors that make them very useful in multi-user databases:

                        1) Any change made to a global field from a client computer is not visible to other users. Each gets their own "virtual copy" of the field. This keeps changes from one user to this field from interferring with the actions of another.

                        2) When you close the database from a client machine, any changes made to the global field are not saved, it reverts back to the value it had when the file was first opened on the client machine. Thus client sessions cannot make permanent changes directly to a global field.

                        • 9. Re: Many-to-Many Challenge

                          After working on this for an embarassingly long time I finally got the value list in my portal to work... expect when I click out of the portal everything in the portal disappears. Can you tell what I might have done wrong from from the above discription? 

                          • 10. Re: Many-to-Many Challenge

                            This happens for one of two reasons:

                            1. The change made to the record changed a field used in the relationship on which the portal is based and thus it is no longer related to the layout's current record.
                            2. The change made results in a portal filter expression evaluating as False for this record and the filter thus excludes it from view.

                            If you go to a layout based directly on the portal's table and do a find, you should be able to locate the record. Examining the values in its fields should show that one of these two explanations fits for the data stored in it.

                            There's very good chance that your value list equipped field is the wrong field to set up for such editing--being a match field used in the relationship.

                            • 11. Re: Many-to-Many Challenge

                              Do I have any choice on which field to use for my value list?  I thought I had to use a foreign to primary match in order for the selected value to populate the rest of my portal records. 

                              • 12. Re: Many-to-Many Challenge

                                RightNow continues to screw things up. The message count indicates 11 posts, but I can't see the 11th one. I'm posting this as an experiment to see if it makes #11 visible....

                                • 13. Re: Many-to-Many Challenge

                                  That worked.

                                  You cannot modify the foreign key field to a different value and expect it to stay related to the current record in your layout as it must continue to match identical values. I'll post this message and then click "Prev" to review the preceding posts before giving a more detailed answer here.

                                  • 14. Re: Many-to-Many Challenge

                                    You have something like this:

                                    tblContacts----<Relatives>----TbleContacts 2

                                    tblContacts::ContactID = Relatives::ContactID
                                    tblContacts 2::ContactID = Relatives::RelatedContactID

                                    Your portal relationship must keep tblContacts::ContactID equal to  Relatives::ContactID or you'll lose the record in the portal. Your value list equipped field shoudl be RelatedContactID. Changes to it link the current record in tblContacts to different contact records, but changes to it will not "break" the connection to the current record in tblContacts.

                                    1 2 Previous Next