4 Replies Latest reply on Mar 20, 2013 12:13 PM by SteveBlair

    Portal question



      Portal question


           Hi, I'm developing a database for submissions to our film festival. I have a Films table, which gives each record an ID number and contains details about the films.

           We have a group of 9 people who will be watching films and meeting regularly to discuss their thoughts on each of the films. I've created a table called Committee that contains an ID number and Name. I also have a table called Ratings, which contains Film ID, Committee ID, and Rating.

           What I'd like to have is ALL committee names listed in the Films layout along with radio options for each of their ratings (numbers 1-5). I've tried several different ways of relating the tables and creating portals, but none are giving me what I want. I have managed to create a portal to display the corresponding Ratings records, grabbing the person's name from Committee, however I want a list of all Committee names, whether they've rated that film or not.

           Any help would be much appreciated

        • 1. Re: Portal question

               It appears to me that you have a many to many relationship.

               A given film is reviewed by more than one person, but a given person reviews more than one film. Thus, you would use a third, join table, to link the two in a many to many relationship:


               Committee::__pkCommitteeID = Committee_Film::_fkCommitteeID
               Films::__pkFilmID = Committee_Film::_fkFilmID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               With these relationships, you can place a portal to Committee_Film on your Films layout to list all committee records linked to that one Film Record. Fields from Committee can be included to show additional info such as the reviewer's name. Fields in Committtee__Film can be defined to hold info specific to that one reviewer's review of a given film if that is of use for this process.

          • 2. Re: Portal question

                 That's definitely getting me closer, and I'll continue to play around with it. The issue really is I want Committee Names listed whether they've rated that film or not. So, all 9 committee members will be listed for every film - then during meetings I can quickly click on the radio button for their rating of the film we're discussing.

                 I originally had fields for ratings within the Film table which worked, but if the committee members change at any point it will be a headache updating everything.

            • 3. Re: Portal question

                   The individual ratings would be recorded in fields in the join table. If a member has not yet rated a film, you will know because that field is blank.

                   You can use a script to populate the join table for each film record at the time you add a new record in Films to save the need of creating individual records by hand for each film.

              • 4. Re: Portal question

                     I hadn't thought of using a script to create the empty records, that's perfect! Thanks again for the great solution Phil