3 Replies Latest reply on Mar 13, 2012 1:53 PM by philmodjunk

    Which team did I assign Mary to?



      Which team did I assign Mary to?


      I have two tables, People and Teams. One team has many people, one person has only one team, so this is a one-to-many relationship linked by primary key _kp_people_id in the People table and the foreign key _kf_people_id in the Teams table.  The assignment of people to teams is accomplished via a pulldown menu in the Teams table.

      After these assignments of people to teams, I want to see which team each person has been assigned to. So if each team has a captain, I'd like to have a field in the people table which indicates the captain of the team that each person has been assigned to. Thus if Mary has been assigned to Dave's team, I want a field in Mary's record in People which says she's been assigned to Dave. How do I think about accomplishing this?  I have a feeling this is a very simple question, but I don't see the answer.  Running FMP 11.

        • 1. Re: Which team did I assign Mary to?

          You have this relationship:

          People::TeamID = Teams::TeamID

          You can add another table occurrence to get:

          Teams::CaptianID = People 2::PeopleID

          And CaptianID can be formatted as a value list of team members (conditional value list) so that you select the team caption from the group via drop down list of these individuals.

          Then you can put the name field from People 2 (or what ever you call this added occurrence of People) to your people layout to show the team captian for each team member.

          To make a new table occurrence:

          In Manage | Database | relationships, make a new table occurrence of People by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          • 2. Re: Which team did I assign Mary to?

            So as I've worked to understand and implement this, I think I've refined the approach somewhat, so I can understand better the question to be asked.  Here's where I am now:

            I have People::TeamID>-Teams::TeamID (one team has many people; is >- the correct nomenclature here?).  Each team has several roles to be filled, such as Captain, Painter, etc.  I was wrong earlier to focus on Captain as a Team identifier, since Captain is just one of the several roles to be filled on each team. My goal is to have people in each of the team roles, and I think the way to do it is first, to assign people to a team, then of those assigned people to put each one in the desired role.  I don't know if this can be done in one step or not.

            For the first step, assigning people to teams, I generated a Value List called TeamIDs from fields: "Teams::TeamID and Teams::Captain", and then used these as a dropdown list on the People layout to say, for example, Sally Smith is assigned to TeamID=3.  Likewise Joe Jones is assigned to TeamID=3.  Now when I go to the Team3 layout, I'd like to click on Painter and choose from a dropdown which includes Sally Smith and Joe Jones.  But I can't figure out how to construct the value list from which to display the dropdown.  If I'm on the Team4 layout, then the value list would contain different names from which to choose.

            I've produced a People2 table occurence (Teams::PeopleID>-People2::PeopleID )as you suggest, but I can't figure what to do next. For one thing, the relationship is backward (one person has many teams, instead of the correct relation one team has many people.)

            Have I followed your suggestions correctly, or have I veered off in the wrong direction?

            Am I on the right track for solution, or am I in the wrong ballpark altogether?

            Thank you again for your help.

            • 3. Re: Which team did I assign Mary to?

              You started out right with

              People::_kf_Team_id = Teams::_kp_team_id

              but then I suggested

              Teams::CaptainID = People2::_kp_people_id

              That produces: People>---Teams-----People2      (>---- stands for many to one)

              That's reasonable when we just wanted to identify the captain and is still useful if you want to access a People record and see the name or other info about their team captain.

              But now we are assigning each person a different role on the team and you've already indicated that a person is only a member of one team. Assuming that a person can only have one role on the team, I'd add a field to the People table to use for assigning roles.

              If there is even the slightest chance that a person could be a member of more than one team, I'd use a join table between Team and People for managing team membership. This might be a useful approach even if this is not possible, but the same person might be assigned more than one role as you can designate roles with a field in the join table.