2 Replies Latest reply on Feb 23, 2012 3:39 PM by philmodjunk

    getting confused with relationships (primary key, foreign key, fields from related tables, etc.)

    TerryCoolidge

      Title

      getting confused with relationships (primary key, foreign key, fields from related tables, etc.)

      Post

      I realize there is much redundancy in what is asked and what is answered on this forum, but so often it's hard to grasp these concepts to the extent where they can be applied to very specific scenarios.  I feel like I'm getting there, and I thought I had experienced that "a-ha" moment where the lightbulb went on over my head, but I've found that I must still be missing some portion of the fundamental understanding behind how related tables work.

      I'll try to quickly summarize where I'm at:

      • I have a church membership stored in a table called member_records
      • I have a list of committees in a table called committees
      • I used a join table called committee_assignments to connect the member_id to the committee_id
      • I can now populate a committee by adding names to a committe record via a portal on a committee record layout
      • I can also populate a member's list of committee assignments by going to their member record and adding committees via a portal
      • committee assignments using either method result in records being added/deleted properly on the join table
      • I can choose a chair for each committee by selecting from the members assigned to the respective committee

      Here's where I'm running into trouble.  I now want to create a table for our church Executive Board which is made up of six elected members.  I do not want to treat this as a committee like the other committees.  I want this to be separate.  So I created a separate table called executive_board, and I'm wondering what fields I need to have that are unique, what fields should simply be calculated from existing data elsewhere, and what fields should be related to fields in other tables.  Also, I had assumed that I would use the member_id as the field with which to relate the two tables, but now I'm not so sure.  So far I had created a field called executive_board::position, and I created a check box set of "Female 1," Female 2," "Female 3," "Male 1," "Male 2," and "Male 3."  When I had just the very basics, I could relate executive_board to member_records via this position field, and then I could go to a member's record and check one of these boxes to "assign" them to the board (they would show up on my executive_board table).  However, things went haywire when I then tried to go to a committee record and assign a "board contact" (each committee has an assigned board member to act as a liaison).  I'm not sure what to do.  Can the existing thread of relationships work "as is" where member_records is a sort of hub and I can see a chain that runs all the way from executive_board through member_records through committee_assignments to committees?  Or do I need to establish new relationships?  A direct relationship between executive_board and committees, perhaps?  Exactly where and why and using which fields?  To get the check box functionality, I created the relationship between executive_board and member_records using the "board_postion" field, but should I be utiilzing the member_id (I thought I remember reading somewhere that this serial number set-up is really important for relating tables).  Now that I have this long chain of relationships, what's the best approach when placing a field on a layout?  Using a portal and looking at the field from another table, or using a calculation, or using a relationship?  I'm so confused.  I don't think I'm beyond help, but I need someone to be patient with me and help me understand the principle behind how this works.  I feel like I'm close, but obviously I'm not quite there.

      Thank you so much for taking the time to read this and respond!!!  Embarassed  Smile

        • 1. Re: getting confused with relationships (primary key, foreign key, fields from related tables, etc.)
          TerryCoolidge

          I apologize if the original post is difficult to read.  For some reason my bulleted, unordered list didn't show up when I clicked "post."  Grrrr...

          • 2. Re: getting confused with relationships (primary key, foreign key, fields from related tables, etc.)
            philmodjunk

            member_records---<committee_assignments>----Committees

            so far so good.

            You want a separate table for the Executive Board. That could be added in as:

            Member_Records---Executive_Board

            Member_Records::MemberID = Executive_Board::MemberID

            With a 6 member executive board, you'd have 6 records each linked by member ID to a different member record.

            BUT, you also need to link each executive board member to one or more committees. Not only is this many to many, it creates a circular relationship that cannot be explicitly created in FileMaker so we have to use extra table occurrences to get the same result, but without a "circle".

            Executive_Board----<Liasons>-----Committees is one option. Executive_Board----<Committee_assignments is another.

            It depends on whether you want to list the Liasons as members of the committee or not. If you want to list them as fellow committe members use the second option.

            The relationships would be:

            ExecutiveMembers::MemberID = Executive_Board::MemberID

            Executive_Board::MemberID = Committee_Assignments::MemberID

            ExecutiveMembers is a new table occurrence of Member_Records to eliminate the circular relationship issue.

            Note that assigning a board member to a committee or a member to the board automatically links them in as the board Liason. If this is not always a disrable result, add a foreign key field to committee_Assignments, ExecLiasonID and then you can specifically choose them as liason for a given committee.