3 Replies Latest reply on Aug 2, 2011 1:11 PM by philmodjunk

    extracting information from a "join table"



      extracting information from a "join table"


      PhilModJunk helped me out last week by explaining to me how to set up a "join table" so that I could establish a many-to-many relationship in my database.  I have hundreds of members in one table and a few dozen committees in another, and I now am able to assign a member to various committees using a members-based layout, or I can populate a committee with members using a committees-based layout.  The Committees_Assigments table has just two fields.  MemberID and CommitteeID.  Records in this table are created or deleted based on the activity in the other two tables (Members and Committees).   I now need to understand how best to retrieve data from this "join table."  For example, I want to be able to display a "Number of Committee Assignments" on a particular member's layout.  I could then run a report showing numerically how many committee assignments each member has.  Likewise, I'd like to be able to have a field on the committee layout that shows the number of members assigned to that committee along with the portal I have right now that shows all of the actual names.  Being a novice, I just need to understand conceptually what the best approach is.  A calculation field?  A script?  Some combination of the two?  Wondering how to count the records in my join table based on found sets without being too convoluted.  I have some ideas, but I doubt my initial instincts would be the most efficient or "proper" solution.   Any suggestions would be greatly appreciated.  Thank you!

        • 1. Re: extracting information from a "join table"

          This is just another case where you need aggregate data (sum, count, average...) from the related records in a portal. There are two methods you can use. Both give the same result but the design of your database and use of a given layout may argue in favor of one over the other:

          Define a calculation field in the Committees table as Count ( Committees_Assignments::CommitteeID ) and it will present you with a count of the committee members listed in the portal. A similar field in Members would count a member's committee assignments.

          Define a summary field in the join table as the "count of" any never empty field such as either of the two ID fields. If you place this field on your committee layout, it will give you a count of committee members. On a member layout, it will count the number of committees.

          The summary field approach allows you to add one field to use for both counts, but can have screen refresh issues when you edit records in the portal in a way that should change the count displayed. Thus, with the summary field, you may need a script trigger controlled script to refresh the window each time you add/remove a record in the portal.

          • 2. Re: extracting information from a "join table"

            Not sure where I should post "Thank you!" since this box says "Post a Answer" (shouldn't that be "Post AN Answer?"), but I don't see another place for posting.  So thank you, Phil!  I will give this a try as soon as I have a chance.

            • 3. Re: extracting information from a "join table"

              The grammar is howlingly bad aint it? Wink

              Worse, I've been told that it's "hard wired" and modman can't change it, we have to wait for an update from Right Now to fix it. (And hopefully rename it to something more appropriate like post an a reply.)