5 Replies Latest reply on Mar 31, 2011 2:43 PM by philmodjunk

    Relationships - Confused

    MitchellJohnson

      Title

      Relationships - Confused

      Post

      Hi Again,

      Here is what i have and what i want to do (im sure its simple enough and im just missing something)

      Two tables one named - "Members" one named "Duties" neither are related at the moment.

      I want to be able to assign many members to one duty:

      Say for example a duty record is a Football match i want to be able to assign members to that duty and then subsicwently create portals, reports etc from that info.

      What am i missing, i cant seem to fiquire it out.

        • 1. Re: Relationships - Confused
          philmodjunk

          But can a member also have many duties? That is also possible, but requires a join table for easiest implementation.

          For now, I'll assume one duty per member. Relate your tables like this:

          Members::DutyID = Duties::DutyID

          Duties::DutyID is an auto-etnered serial number field. Members::DutyID is a number field.

          Now you can format Members::DutyID as a value list of DutyID's from the Duties table, with a name field from the same table displayed in the second column of this value list. To assign a duty to a given member, simply pull down the value list or pop up the menu and select a duty.

          • 2. Re: Relationships - Confused
            MitchellJohnson

            Thanks mate, Sorry to be a pain but yes members can also attend many duties , do you mind explaining (as simple as you can) how this would be possible please

            • 3. Re: Relationships - Confused
              philmodjunk

              No problem. This is called a "many to many" relationship.

              Members---<Member_Duties>-----Duties

              Members::MemberID = Member_Duties::MemberID
              Duties::DutyID = Member_Duties::DutyID

              Put a portal to Member_Duties on the members layout to see a list of all duties assigned to that member. You can add fields from Duties to the portal row to disply more detail about that duty such as the duty's name or description. In like manner, you can place a portal to member_duties on the Duties layout to list all members assigned to that Duty. Any information specific to that member's duty assignment can be entered in fields defined in the Member_Duties table.

              • 4. Re: Relationships - Confused
                MitchellJohnson

                Brill thanks, next (sorry) simple button on a duty record that says add member - Basicly how do i now assign a member to a duty in a simple user freindly way - Ideally by a button. thanks again

                • 5. Re: Relationships - Confused
                  philmodjunk

                  Instead of a button use a drop down list or pop up menu on the MemberID field in the portal to Member_Duties.

                  1. In Manage | Database | Relationships, double click the relationship line linking Duties to Member_Duties and select the "Allow creation of records via this relationship" option for the Member_Duties table if you have not already done so.
                  2. In Manage | Value list, you can set up a value list that lists all member ID's from the members table in column 1. List the member names from column 2. When you select a member from this list, you'll see the member names so that you can see who you are selecting, but when you select them, their member ID is what is entered into the field.
                  3. Now go to the Member_Duties::MemberID field in the portal and use the Inspector's data tab to format it as either a drop down list or pop up menu that uses this value list. (You can put the members::MemberName field next to this field to show the member's name when you exit the drop down.