1 Reply Latest reply on Jul 29, 2011 5:55 PM by philmodjunk

    assigning committees to a member vs. assigning members to a committee



      assigning committees to a member vs. assigning members to a committee



      I have a table for member records and a table for committee records.  Right now my method of establishing committee assignments is a layout that displays member records with a check-box list for the various committees.  I move from member record to member record and check the appropriate boxes.  However, there may be instances when it would be far easier to pull up a layout listing committee records, and then populate a particular committee with names from the member records.  We're talking about hundreds of members and only about 20 or so committees.

      How best to create this second option of populating a committee with members?  When entering member names, it would be nice to actually pull from the existing member records so that we're not creating new names due to misspellings that don't match existing records.  Drop-down menus and pop-up lists seem impractical because of the number of members.

      My table of member records has a "Committee Assignments" field that is made up of committee names based on the check-box data entry, and I've created a "Committee Members" field in the table for committee records to prepare for entering member names over there, but beyond that I'm not sure what I should be doing.  I would definitely describe myself as a novice, although I do manage to figure some things out on my own, so I'm not completely helpless.

      Any suggestions would be most appreciated.  Thank you!

        • 1. Re: assigning committees to a member vs. assigning members to a committee

          What you have is a many to many relationship. Any given member can be a member of more than one committee and any given committee lists more than one members. Adding a "join" table to match up members to committees is a better option than your check box group and will also allow you to pull up a committee record and assign members to it like you have requested:


          Members::MemberID = Committee_Membership::MemberID
          Committee::CommitteeID = Commitee_Membership::CommiteeID

          With these tables and relationships, you can add a portal to committee_membership on a members layout to list that member's committees. A portal to committee_membership on a committees layout will list the members for that committee.

          Here's how to set up your portal on the Committees layout. If you carefully follow the same pattern, you can set this up on the members layout as well:

          Enable "Allow creation of records via this relationship" for Committee_Membership in the committee to Committee_membership relationship.

          Place a portal to Committee_Membership on the Committees layout. Add the Commitee_Membership::CommitteeID field to the portal and format it as a drop down list or pop up menu of MemberID's from Members with the Member Names as field 2. In this value list, you can see the member name to select a member, but the memberID is what is entered in the field to link this record to a record in the member table. You can then add additional fields from the Member Table to this portal (such as the member name field) to provide additional info about each member on the committee.