4 Replies Latest reply on Sep 30, 2012 12:31 PM by samjs

    Filtering Student Database

    samjs

      I am creating a Student Database for the music teachers in a public school.

       

      I want to create a group navigational filtered portal to the left of each student's record, which would allow the teacher to filter by group, such as band or choir. Please see the attached picture to get the idea.

      I want to then be able to select the student in the portal and have it navigate to their record.

       

      Now if their is no criteria in the filter field, I want it to list every student in the database, even the ones who are not assigned to a group. I have created the following tables:

       

      Student

      Group

      Groupline

       

      The problem I am facing when creating the relationship between the Student and Groupline tables is that ...

      1. It does not show students who are not in a group

      2. It shows duplicates of the same student because they are in 2 groups.

       

      I'm stuck at how to create this solution. Any ideas is greatly appreciated!

       

      Sam

        • 1. Re: Filtering Student Database
          BTimm

          I think the core of the issue is that your portal is based on the groupline record items so you can filter by band, etc, and be able to pull a student up in more than one group, if they have multiple entries in groupline.

           

          However, you want unique and complete set of students if there are no group/band filters selected, correct?  In that case, to show a list of all unique students, you probably need to display a different portal based on the Student table.  And then use two layouts and script triggers.

           

          Trigger on the filter selection. If there are no groups selected, show the layout with the student portal. If there are groups selected, show with the groupline detail portal.

           

          There might be more elegant solutions but I think that would work. The drawback is you have to manage design on multiple layouts, so if you change navigation or anything else on layout, you have to duplicate the changes on the layout with student portal.

          • 2. Re: Filtering Student Database
            LyndsayHowarth

            Just use a filter relationship as you have been doing... but make the match to a calculation field which adds "¶All" at the end of the group(s) in the selected group list.

             

            You can click "All" or enter criteria to match.

             

            - Lyndsay

            • 3. Re: Filtering Student Database
              samjs

              Lyndsay,

               

              Thanks for your help.  I have it set up to show all now, but because of my relationship, it only shows students who are in a group (students in the grouplineitem table)  I would love for it to show me all the students who are in the student table even if they are not in a group. 

               

              I thought of another possibility...

               

              Can I create a self joining student table relationship so the navigational portal will always show me the found set students?  If so, I could create a trigger that would find students in a group.  I tried a self joining relationship, but it does not show the found set results.

               

              Any suggestions?

              • 4. Re: Filtering Student Database
                samjs

                I have found a solution to have a portal show the found set.  You can find the solution at:

                 

                http://www.nightwing.com.au/FileMaker/demos8/demo804.html

                 

                Thanks everyone for your help!