6 Replies Latest reply on Aug 28, 2014 2:23 AM by Annette

    Related multiple portals.

    Annette

      Title

      Related multiple portals.

      Post

            Apologies if this is already explained somewhere, I searched various threads and couldn't find what I was looking for probably due to not know the correct term for it.  

           I have the following tables:

           Staff which has StaffID, name ManagerID, etc

           I created a self join table of that called LMID ..... Related by Staff::StaffID to LMID::LMID because I wanted to create a value list of the names as different people manage different things....wanted to select anyone as appropriate on people table....the field shows the StaffID once they select the name.  This portal is on a layout based on the staff, and when you go to a person where someone else has them as a manager the list of names appears.  So if on my page I put John Doe as my manager, his ID appears in that field for me...the portal on the right is blank...but when you go to his page....my name will be in that portal to right.  

           I have another table, STaffWTE which has PK , STaffID, WTE, Directorate, link, LMID etc.   each person can have more than one entry on this table which is why I didn't just add these fields to the staff table,  I have one occurrence do this table linking Staff::StaffID to STaffWTE::StaffID   And another related by LMID::link (which is a calculation = 1) to StaffWTE2::Link.  I've tried also making the relationship related by StaffID or LMID's in each but None of these work the way I want it to as explained below.  

           what I would like to do is create a layout where managers can go that has a portal for LMID showing all their staff reporting to them, and when they select a name from that portal, it shows the related info in a portal to the right from STaffWTE.  Is that the best way to go about them seeing a list of their staff and their related WTE or an i going about this all wrong.  If it is I'm clearly doing the relationships wrong and/or basing the layout on wrong table.  I currently have it based on the Staff table.  

           Please help!  Thank you in advance.

        • 1. Re: Related multiple portals.
          philmodjunk
               

                     This portal is on a layout based on the staff, and when you go to a person where someone else has them as a manager the list of names appears.

               Unless a member of Staff can have more than one manager, there is no need for a join table. You can set up a self join using ManagerID

               Staff::StaffID = Staff|Manages::ManagerID

               Staff|Manages is the name that I am giving to an added table occurrence of Staff. A portal to Staff|Manages would list all staff managed by the person whose record is the current record on your staff layout.

               

                    what I would like to do is create a layout where managers can go that has a portal for LMID showing all their staff reporting to them,

               My best guess is that you want to see a portal to StaffWTE that lists all records in that table for staff that is managed by the current Staff record.

               That would seem to suggest this data model:

               StaffWTE>-----Staff----<Staff|Manages-----<StaffWTE|Manages

               Staff::StaffID = StaffWTE::StaffID
               Staff::StaffID = Staff|Manages::ManagerID
               Staff|Manages::StaffID = StaffWTE|Manages::StaffID

               You could then put a portal to StaffWTE|Manages on your Staff layout to show all StaffWTE|Manages records for all staff managed by the manager whose record is current on the Staff layout.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Related multiple portals.
            Annette

                 Hi Phil....thanks.  I didn't explain myself well, apologies, my LMID is a self Join table as you stated above.  That method works fine, and I had gotten it to work that way but as a manager can have a lot of staff they manage I was hoping to be able to use one portal where they click on name of their staff member they want to look up and then the other portal shows only their WTE because the list could be very long to scroll through to find the one they want.   Is that possible? 

            • 3. Re: Related multiple portals.
              philmodjunk
                   

                        my LMID is a self Join table as you stated above.

                   And this is a table that you do not need from what I can see here. There isn't a many to many relationship between a manager and the staff that they manage it's one manager to many staff--so the join table seems a needless complication.

                   But what you describe is sometimes called a "master detail" pair of portals and I have a thread on the topic that you can examine:

              Need layout solution for nested portals...

              • 4. Re: Related multiple portals.
                Annette

                     Phil, I am apologizing more...lol....I meant to say a table occurrence all along so I have been what your suggesting.  I have the Staff Table....and another occurrence of that staff table called LMID, related by Staff::StaffID=LMID::LMID. 

                      

                     Thank you you for the link, will have to look at it more as I've messed something up somewhere because it isn't working ....Staff is my main table (LMID a second occurrence of it) and STaffWTE another table.  But if I was going to reference the way your link explains....is LMID the parent, staff the child and STaffWTE the grandchild?  Or would Staff still remain the Parent and LMID the child?  I've tried both with no success.  Will work at it more tomorrow when I'm back in the office.

                      

                      Thank you again for all your help. 

                • 5. Re: Related multiple portals.
                  philmodjunk

                       If your layout is based on Staff, you have:

                       Staff = Parent

                       LMID = Child

                       StaffWTE = GrandChild

                       It's the realtionship between an added field in Staff that matches to the correct occurrence of StaffWTE--the same used for the second "detal" portal that's key. The set field script then sets this field in staff to the needed StaffID in order for the portal to only show StaffWTE records for this selected staff member.

                  • 6. Re: Related multiple portals.
                    Annette

                         Came to work with a fresh head today and found the problem.  I had the script on the bottom wrong.  Thank you so much Phil for your patience and help!!!