9 Replies Latest reply on Dec 18, 2016 3:26 AM by FileMakerMentor

    Filtering two portals in the same layout

    kickaha

      Imagine the following situation:

       

      one table of tasks

      one table of Persons

      one table of roles

       

      a many to many relation  with a join table between tasks and persons

      one task can be made by multiple persons

      a person can be assign to multiple tasks

       

      a many to many relation  with a join table between persons and roles

      one person can have many roles

      and a role can be assigned to many persons

       

      a layout based on tasks  with two portals:

      a portal to enter persons with role X

      a portal to enter persons with role Y

       

      Everything works fine. With help of some scripting i manage to assign a role X to multiple persons and add it to the portal.

      The problem began when i add a portal to role Y and when i add a person that already have a role X, i can´t display that person in both portals.

      i add a filtering criteria to the portals. one to show only role X (Role::RoleID=1) and other to show only role Y (Role::RoleID=2). This filter only works for persons that only have one role (X or Y), but if they have both roles assigned only the first one assigned will display in the respective portal.

      if i remove the filter from both portals every persons i ad in portal X also appear in portal Y and vice versa.

      Everything is working fine internally, I checked the join tables and every values have the correct relationship, but i can't manage to display the same person in both portals at the same time.

      I hope i had been clear enough. I really appreciate some help on this. Thanks.

        • 1. Re: Filtering two portals in the same layout
          erolst

          If you have

           

          SomeContext x Person --< PersonRole >-- Role

           

          and use an equal operator, you will only query the first related Role record.

           

          Try a filter condition testing on membership, i.e.

           

          not IsEmpty ( FilterValues ( n ; List ( Role::id ) ) )

           

          where you can save one hop using

           

          not IsEmpty ( FilterValues ( n ; List ( PersonRole::id_role ) ) )

          • 2. Re: Filtering two portals in the same layout
            kickaha

            Hi erolst,

             

            Thanks for your reply.

             

            I tried to use the formula you sugested:

            not IsEmpty ( FilterValues ( n ; List ( Role::id ) ) )

             

             

            (i replace the "n" in the formula by the role ID number)

             

             

            i used it in the portal filtering but that didn't work, it behaves as it doesn't have any filter and all.

             

             

            i don't really understand what you mean by "Where you can save one hop using"

             

             

            Further help will be much appreciated! Thanks!

            • 3. Re: Filtering two portals in the same layout
              philmodjunk

              Seems to me that roles should serve as your join table.

               

              Persons----<role>---Tasks

               

              Persons::__pkPersonID = Role::_fkPersonID

              Tasks::__pkTaskID = Role::_fkYaskID

               

              To assign the same person to more than one role for the same task would require creating more than one role record with the same person and task IDs, but a third field that identifies the role would be different.

               

              You might use a fourth table for roles and make the join table a "star join" between the three tables if needed.

              • 4. Re: Filtering two portals in the same layout
                kickaha

                Hi philmodjunk,

                 

                Thanks for your reply.

                 

                My role table is not a join table as you mentioned

                I've a joint table between role table and Person table

                and another join table bettween Persons and Task tables

                 

                Tasks---<JoinTaskPerson>---Persons----<JoinPersonRole>---Role

                 

                I don't know if this is the ideal approach and i never read nothing about "star Join", but now i get curios, and i'll dig about it.

                 

                The logic is like a movie database, being the tasks the movies, that have Persons, that can be in different roles (like director, actor, writer, etc)

                 

                I'll be grateful for any more thoughts. Thanks!

                • 5. Re: Filtering two portals in the same layout
                  kickaha

                  Hi erolst,

                   

                  I was wrong when i tell the filter works as it has no filter at all.

                  It filter correctly but the problem remains, a person that is assigned with role X and Y only displays in the first assigned portal.

                  • 6. Re: Filtering two portals in the same layout
                    BruceRobertson

                    One thing you are clearly declaring so far is that roles have got nothing to do with projects; or with tasks.

                    • 7. Re: Filtering two portals in the same layout
                      philmodjunk

                      My role table is not a join table as you mentioned

                      I've a joint table between role table and Person table

                      and another join table bettween Persons and Task tables

                      My suggestion was and is that you change your design so that roles either is the join table or is linked to the same join table as the other two main tables. This makes more sense to me. If you only need to name the role or "roles" are something fairly unique to every task, roles can be the join table. If you have a "standard list of roles" that you want to use over and over again and need to document information about each role, then a separate table that links to the join table just like tasks and persons makes sense.

                      1 of 1 people found this helpful
                      • 8. Re: Filtering two portals in the same layout
                        kickaha

                        Ok. Problem solved.

                        philmodjunk guide me in the right path. Thanks!

                        • 9. Re: Filtering two portals in the same layout
                          FileMakerMentor

                          Hi, was just thinking that using relationships, modelling the data is important, i.e. looking at the real life structure of the data, usually holds the answer, a bit like a family tree.

                           

                          The best way to achieve these sorts of views of your data is to use JOINS. A JOIN is a connection table object usually with two or more foreign keys. A join is best explained as a non exclusive child record, thus allowing more than one parent record to have a relation to a child record. The JOIN sits between the PARENT and CHILD tables.

                           

                          left portal could be

                           

                          MUM

                          DAD

                           

                          if you select MUM you can make a JOIN record related to MUM, and another JOIN record related to DAD. then with an outer join you can connect the Join table to the CHILD table, using the ChildID.

                           

                          This allows you to have two join records but only one Child,

                           

                          Mum Relates to Son, and Dad relates to Son, but there is only one Son record in the Children Table.

                           

                          in this case i'd probably call the JOIN table CHILD.PARENT indicating the nature of the two ID's/foreign keys it contains.

                           

                          JOINS make a more complex architecture, but there are some very elegant and simple layout designs that can hugely simplify these kinds of "non exclusive" data structures for the user.

                           

                          PARENT CHILD JOIN.png

                           

                          I hope this helps/compliments the existing replies.

                           

                          If you have the answer you need, great, otherwise, happy to give some further guidance, or even via our mentoring service which is metered and with no minimum cost. If it takes five mins we bill for five mins.

                           

                          Kind regards,

                           

                          David