6 Replies Latest reply on Aug 21, 2013 3:40 AM by teclo

    Hide inactive records in joint table

    teclo

      Title

      Hide inactive records in joint table

      Post


           Hi,

           I'm using FileMaker Pro 12 and have one table with students and one table with supervisors with a joint table in between as each student can have many supervisors. I have a field for active/inactive in my student's table and a script that always omit the inactive records (or rather set the field to only show active ones).

           How do I go about to have the inactive records hidden in the joint table by default as well?

        • 1. Re: Hide inactive records in joint table
          ninja

               Howdy Sara,

               What table are you looking at the joint table from?

               If it is a portal from Student or Supervisor, you can filter the portal to show only "Active"

               Or do the same filter in the relationship itself or by using a global or autenter field in the Student or Supervisor table and a multi-key relationship.

               {Related by ID = ID AND Student::GlobalActive = Joint::Status}

               If you are looking at the joint records from a layout based on the Joint table, simply omit the "Inactive" from the found set.

          • 2. Re: Hide inactive records in joint table
            teclo

                 thanks, I sorted it out by omitting the records, but I'm not sure that is the most efficient way in the long run since there seem to be a lot of filtering/field setting that has to be done before each new step in the future process.

                 could you elaborate on how to create a filter in a relationship? let's say the field is called IsActive and active=1 and inactive=0. do I create a field in the joint table where every record is "1" and relate those two fields?

                 I also have a value list based on the student's names that I need to omit the inactive records in. that value list is based on a new occurrence of the student's table with a global name field in the "original" table linked to the name field in the "new" table. can I create a multi-key relationship between these two occurrences in a simple way? sorry if that's maybe an entirely different question that needs its own thread.

            • 3. Re: Hide inactive records in joint table
              ninja

                   Which table are you on when you are looking at the records in the Joint table?

                   Can you show the current table structure?  I assume:

                   Student ---<  Joint  >----Supervisor

                   Is this correct?

                    

              • 4. Re: Hide inactive records in joint table
                teclo

                     Yes, that's the structure. I'm in the supervisor's table.

                • 5. Re: Hide inactive records in joint table
                  ninja

                       I also assume you are looking from the Supervior table to the Joint table using a portal:

                       If so, you can filter the portal in the portal setup dialog.  SImply set the expression to be {  Joint::IsActive = 1  }......this filters the portal, not the relationship.

                       To filter the relationship, you would create a global field in the Supervisor table, set to "1" and have the relationship between Supervisor and Joint be

                       Supervisor::IdNumber = Joint::IDNumber   AND

                       Supervisor::gActive = Joint::IsActive

                       Now only the active records are related to the Supervisor table.

                       Note that this relationship filter affects all records through the relationship...if you ever want to see "All joint records" you'll need to be working through a separate, unfiltered relationship from a different table occurrence.

                  • 6. Re: Hide inactive records in joint table
                    teclo

                         thanks a lot, I hadn't grasped the concept with global fields.