13 Replies Latest reply on Dec 23, 2011 8:56 AM by philmodjunk

    Complicated Table Links and a Portal

    MikeHaddon

      Title

      Complicated Table Links and a Portal

      Post

      These groups have been incredibly helpful in my work to use lookups and portals.  I have reached a new barrier that I cannot figure out, and it includes a more extended table linking structure.  I was hoping by creating the appropriate links that this would magically work, but it isn't working as intended.  Let me start by explaining what I am trying to do on this layout:

      1.  I basically have a lookup portal on one side of the layout.  It contains the names of different legislative committee.  It is working (thanks to previous guidance).  What I am trying to accomplish is allowing the user to select a committee from this portal, then have another portal on the right side of the layout display the members of the selected committee.

      In this particular case, there are actually some extended table links I am trying to use to accomplish.  Here is what I have:

      1.  A table called "Committees".  This table contains an ID that links to a specific committee, as well as an ID that links to the name in a table of legislators.  I have done this so that I don't have to include the names of legislators on each committee (just using their IDs) and I don't have to include the full name of each committee in this table (again, includes an ID that links to a table listing committees).  This table includes a legislator and a committee on each row.  Basically, it would look like the following:

      Committee      Legislator

      2C                  1H
      2C                 10H
      2C                 74H

      Which might translate into:

      Committee                    Legislator
      House Ethics                  Doe, John
      House Ethics                  Doe, Jane
      House Ethics                  Smith, Mark

      So, my lookup connects to a table that contains the names of the committees and would also include a CommitteeID.  The lookup contains one row for each committee.  Somehow, when the user selects a committee from this portal, it needs to populate the other portal with the names of the committee members.  The LegislatorID is contained in this Committees table, but I need it to reconcile to the actual name of the legislator (e.g. 1H won't mean much to the user, but Doe, John will).  The actual names are in a different table - Legislators, which contains the LegislatorID that is in the Committees table.

      Does this make sense?  Any ideas would be appreciated.  Thanks!

        • 1. Re: Complicated Table Links and a Portal
          philmodjunk

          Legislators-----<CommitteeMembership>------Committees

          Correct?

          What you describe is a classic pair of "Master-Detail" portals. The Master Portal should be based on Committees and the detail portal needs either a portal filter or a relationship that is updated when you click a row in the committees portal.

          From a layout based on what table do you need these two portals? Would  List of Committees on a List View layout based on Committees work as the list of committees for you? (Would simplify things by a small amount.)

          PS, why the letters for your IDs? (That's not something you really need to identify legislators or committees and it complicates things unecessarily.)

          • 2. Re: Complicated Table Links and a Portal
            MikeHaddon

            The ID thing is probably a hold over from me working in more of a relational database system where space was limited.

            I just noticed in my setup that I may be close.  So, the layout has two portals.  The portal on the left side is serving as a lookup.  It lists the different legislative committees.  The portal on the right is supposed to be populating with the legislators who are members of the committee selected in the lookup.

            What I just noticed is the portal that is supposed to be showing all members of the committee selected, is, in fact just showing one member from the committee.  However, I noticed at the top of my Filemaker window (where it shows the number of records found), it is showing 21 records found - which is the number of legislators on the committee I selected.  If I use the "Go to the next record" button at the top of the window, it cycles through the individual members of the committee - one at a time showing in the portal.

            It almost looks like selecting the committee on the left is actually filtering or coming up with the right result set.  The portal on the right just isn't displaying all members of the result set - it only shows one at a time....

            • 3. Re: Complicated Table Links and a Portal
              mgores

              Make sure the fields in the portal are set to use the proper relationship also.  Have seen this happen to me when I used the fields from the wrong table occurrence.

              • 4. Re: Complicated Table Links and a Portal
                philmodjunk

                To repeat my question: On what table do you want to base your layout?

                It sounds like your script in the look up portal is finding records instead of updating a portal or relationship filter to list the appropriate committee members.

                I need to know the layout's table of reference before I can describe the relationships needed for each portal.

                Let's just pick a table occurrence for now as it really doesn't matter much which one we use as long as the correct relationships for the portals are in place:

                SomeTable::anyfield X Committees::Anyfield

                Is the relationship you should have in place for the left "look up" portal so that it lists all committee records.

                SomeTable::gSelectedCommitteeID = SelectedCommitteeMembership::CommitteeID

                will then let you set up a portal on the same layout to SelectedCommittemembership--a new table occurrence of your join table between legislators and committees.

                Clicking a button (or fields in the portal set up as a button) should then perform this script:

                Set Field [SomeTable::gSelectedCommitteeID ; Committees::CommitteeID ]

                • 5. Re: Complicated Table Links and a Portal
                  MikeHaddon

                  I basically have three tables in play:  Main, tblStanding, and CommitteeLookup.

                  CommitteeLookup is a new table occurrance of a table I have called Committee.  Committee contains the full committee name and the CommitteeID

                  I created the following join:  tblStanding::CommitteeID X CommitteeLookup::CommitteeID

                  I have used this relationship to populate the left (lookup portal) with the full committee names.

                  I also have the following relationship:  Main::LegislatorID = tblStanding::LegislatorID

                  Main contains one row for each legislator, as well as other information about each legislator.  tblStanding really contains the overall committee membership information.  A single legislator will belong to several committees; and each committee has several legislators.  I have tried setting the layout's underlying table to both Main and tblStanding - neither seem to have any impact.

                  Functionally, the lookup portal should pull a CommitteeID based on the full committee name the user selects.  From there, the portal on the right should show the names (from Main) for each legislator who is a member of the CommitteeID selected.  The CommitteeID would be in tblStanding, which has the link to table Main, where legislator names are stored.

                  • 6. Re: Complicated Table Links and a Portal
                    mgores

                    Also make sure your field is completely in the portal.  If its even touching the edge it can cause problems.

                    • 7. Re: Complicated Table Links and a Portal
                      philmodjunk

                      The actual table occurrence on which you base your layout has very little to do with what you see on your screen as long as you have the correct relationships linked to it for your two portals and the global ID field must be defined as a field in it.

                      I'm guessing that you have this relationship in place to handle committee membership:

                      Main ----< tblStanding >-- Committee

                      Just to keep things separate. Define a new table, Globals. You can use this table for all global fields not needed in relationships between other tables.

                      Define gSelectedCommitteeID in Globals and specify global storage for it.

                      Define these relationships:

                      Globals----<AllCommittees
                         |
                         ^
                      SelectedCommitteeMembership

                      Globals::anyField X AllCommittees::anyfield  (you can use any pair of fields for X relationships and you get the same result.)
                      Globasl::gSelectedCommitteeID = SelectedCommittteeMembership::CommitteeID

                      AllCommittees is a new occurrence of Committee and SelectedCommitteeMembership is a new occurrence of tblStanding.

                      Use this script as the script performed when a button in the portal to AllCommittees is clicked:

                      Set Field [Globals::gSelectedCommitteeID ; AllCommittees::CommitteeID ]

                      • 8. Re: Complicated Table Links and a Portal
                        MikeHaddon

                        Ok.  I created a Globals table with a global variable.  Then I set up the following relationships:

                         

                        gGlobals::gSelectedCommittee X AllCommittees::CommitteeID (AllCommittees has a single row for each committee)

                        gGlobals::gSelectedCommittee = tblStanding2::CommitteeID (tblStanding2 is a new occurrrance of tblStanding which contains all of the ties between legislators and committees)

                        When I did this, my lookup portal no longer contains any records.  I am tying the portal to AllCommittees, and trying to show the full committee name.  It does have the X relationship defined with gGlobals - but that is the only relationship created.

                        Also, I didn't see any ties to Main (or another occurrance of Main - so I'm confused as to how this will eventually bring in legislator names).  As always, I really do appreciate all of your assistance.

                        • 9. Re: Complicated Table Links and a Portal
                          philmodjunk

                          In layout setup, what table occurrence is specified in "Show records from"? It should specify gGlobals.

                          Also, I didn't see any ties to Main (or another occurrance of Main...

                          Forgot that detail. you'll need to hang another occurrence of Main off this set of occurrences and link it to tblStanding2 by LegislatorID so that you can include fields from this new occurrence to list the names.

                          • 10. Re: Complicated Table Links and a Portal
                            MikeHaddon

                            Here is what I have, and it still isn't working.  I'll get to that in a second.  Let me start just by outlining the relationships I now have:

                            Tables:  gGlobals, CommitteeLookup (occurrance of Committees that includes one row for each committee), StandingForiPad (occurrance of tblStanding - contains committee membership), and MainForiPadCommittee (occurrance of Main - that includes the names and other information about individual legislators)

                            Relationships:
                            gGlobals::gStandingLookup X CommitteeLookup::CommitteeID
                            gGlobals::gStandingLookup = StandingForiPad::Committee (CommitteeID)
                            StandingForiPad::Photo = MainForiPadCommittee::Photo  (Photo is the ID for the legislators)

                            Layout Setup:  underlying table is gGlobals  

                            On the Layout
                            Portal on the left side for lookup is tied to CommitteeID::Committee  (full name of the committee)
                            Portal on the right side that should show members of the selected committee contains:  MainForiPadCommittee::Name   (legislator's name)

                            Script tied to the Button Setup for the field in the CommitteeLookup portal (should be used to select a committee then show committee members in the right portal):
                            Set Field[gGlobals::gStandingLookup; CommitteeLookup::CommitteeID]

                            That pretty much covers what I have at this moment.  The lookup portal isn't showing any of the Committee names.  Clearly, the portal that is supposed to show members of a selected committee is empty as well.  Where have I messed this up? 

                            • 11. Re: Complicated Table Links and a Portal
                              philmodjunk

                              To repeat: Open layout setup and check "show Records from" what table occurrence is listed there? It should list gGlobals in order for this to work.

                              Now, while still in layout mode, check "show related records from" in portal set up for the two portals. The left hand portal should specify CommitteeLookUp. The right hand portal should show StandingForiPad.

                              • 12. Re: Complicated Table Links and a Portal
                                MikeHaddon

                                Ok....  It worked!!  I'm trying a variant of this on a different layout.  One hiccup I noticed is I am unable to edit records in the portal showing the names (after the lookup).  It actually says there are no records.  I am guessing this is because the layout was based on gGlobals, which really doesn't have records in it.

                                I'm looking for similar functionality to the last layout, but with some editing ability.  The first layout was really just to review information.  I'm trying to set up a layout where users can edit which legislators are on the committee.  These change from time to time.  I still need the lookup portal to allow the user to narrow their review and potential edits to a specific committee selected.  However, now, in the portal that shows the names, I need to be able to allow the user to edit the names.

                                • 13. Re: Complicated Table Links and a Portal
                                  philmodjunk

                                  Create on record in your globals table and see if it makes any difference.

                                  You can use any table you want in place of Globals, as long as you also base your layout on the correct table occurrence for it and define your global field in that table. I suggested the globals table simply as a way to set this up without interferring with any existing relationships and table occurrences.

                                  Editing which members are on a selected committee means editing a record in the join table. There are a number of methods you can use to work with a join table.

                                  Here's a demo file that takes you through the basics of a many to many relationship and then suggests several enhanced methods you can use for working with them:  http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html