5 Replies Latest reply on Jul 27, 2016 9:24 PM by philmodjunk

    Combining Information from Three Distantly-Related Tables in One Layout

    jgetman

      Hello, all.  I have a situation where I have three tables in my database that hold People (PeopleProgram, PeopleEpisode, and PerformersPeople. These need to remain separate tables so that they only relate to specific other tables (in this case Program, Episode, and RecordingSession). 

       

      (If I made them one table, then everyone who is listed in PerformersPeople and PeopleEpisode would show up in one list along with PeopleProgram in, say, a layout for Program, and that is not what I'm looking for.)

       

      In any case, now I need to make a layout in which users can search through all People (from PeopleProgram, PeopleEpisode, and PerformersPeople) in a way that will bring up a list of all People and create connections to their related record in the correct layout (see attachments). The problem, as you might imagine, is that making a layout to do this is problematic because the layout can only be attached to one master table. It's a flaw in my database app that I haven't been able to figure out, either at tables/relationships level or at the user interface level.

       

      I've attached some images that might help--the relationships diagram and all six of the current user layouts. If, for instance, someone searches for Alexander Courage on this fictional "People" page, the page should come up as having records related to all three groups of people, and I want users to be able to follow links to the related records in Programs Browse and Detail, Episodes Browse and Detail, and Master Cue Browse and Detail. Can it be done?

       

      I'd appreciate any thoughts. If it takes reworking the relationships, I'm happy to hear it. If there's some way to make a more general "Google"-like search page, then cool. If it takes scripting...I'm willing to learn. But really, anything that can make this work so that users can search through people on one page and then choose which related records they want to see on which layouts--that will make me happy.

       

      Thanks! ~J

        • 1. Re: Combining Information from Three Distantly-Related Tables in One Layout
          philmodjunk

          I'm afraid that there are some fundamental misconceptions about tables and relationships in what you have posted.

           

          Item 1:

          These need to remain separate tables so that they only relate to specific other tables (in this case Program, Episode, and RecordingSession).

          This is not the case. You can relate the same people table in as many different relationships to as many different tables as you need. This is done by creating multiple occurrences of the same table in the relationship graph. (Multiple "boxes" in the relatinship graph can all represent the same underlying table, but in different relationships.)

           

          You can add a category field and use it to work with different groups of records in the same table. In this case, you might set up a category field and give all people records one of these values: Program, Episode, Recording session.

           

          If I made them one table, then everyone who is listed in PerformersPeople and PeopleEpisode would show up in one list along with PeopleProgram in, say, a layout for Program

          This is closer to being accurate, but in fact, there are many different ways that you can limit a list of records from a unified People table so that a user is only able to see and work with the records from a specific group or groups.

           

          Here are just two examples:

          1) You can list the records in a portal with a relationship or portal filter that limits the records to those of just one category.

          2) On a list or table view, you can use script triggers to constrain the found set after any given find is performed by script or a user. That constrain can limit the records to just those of a specific group.

           

          So I would suggest you improve your data model by using a single table of people. It's relatively easy to work with subgroups of records from the same table, much more complicated to work combine groups of records from separate tables.

          • 2. Re: Combining Information from Three Distantly-Related Tables in One Layout
            jgetman

            Hi, philmodjunk (et al). You're right--I think I am misunderstanding something. I'm closer to getting what you're talking about, but in trying to implement these changes, I ran into a problem that shows that maybe I *don't* understand.

             

            I went ahead and built a single People table. I then made two copies of it in the diagram (People 2 and People 3) that I connected to the correct related tables (Program, Episode, and RecordingSession). I then placed these in the data entry and end-user layouts and populated them a bit so that I could test it out.

             

            All was well until I started building the "People" search page for the end users. You might be able to tell in the images, but for some reason, the layout built on "People" (and not People 2 or People 3) accesses records from the basic People table, but does not access the correct related data (e.g., EpisodeName [from Episode] and MasterCueName [from MasterCueInstance, which is related to RecordingSession]). Although it does access related data I don't want to see for "People" who are supposed to be attached only to Program....

             

            (I just tried it with portals in for those related fields, too, and it still doesn't work.  Hmm.)

             

            Any ideas on what I'm doing wrong? I have a feeling it's something basic that I'm just not wrapping my brain around.

             

            Thanks! I appreciate the wisdom.  ~J

             

            New Diagram.jpg

             

            People Table.jpg

            People Browse Edit.jpgPeople Browse View.jpg

            People Browse View - Portals.jpg

            • 3. Re: Combining Information from Three Distantly-Related Tables in One Layout
              keywords

              Re: "…maybe I *don't* understand … I went ahead and built a single People table. I then made two copies of it in the diagram (People 2 and People 3)"

               

              To help yourself unravel your own confusion try to avoid naming TOs (Table Occurrences) default names like People 2 and 3, etc. Five minutes after you created them, what does the name tell you about what the TO is for, and how it is connected within your schema? The Table has a logical enough name—People—which tells you something instantly. Use TO naming to also tell you something logical. Commonly, developers will use the names of the tables that are being joined (fromTable_toTable) where the table that the TO belongs to is on the receiving (to) side. You can do the same thing with field naming, i.e. make the name itself communicate. It is worth spending some time studying some of the methodology documentation that is available (look up Anchor–Buoy for starters) to help get your head around this stuff.

              • 4. Re: Combining Information from Three Distantly-Related Tables in One Layout
                jgetman

                Hi, keywords. Thank you for the advice.  Looking up TO naming conventions, I see that I should have renamed each of the table occurrences something like "People_Program", etc. There seems to be a number of ways to do this, but I'm assuming that's what you mean? (If you look at my diagram, I'm already doing this in my field naming convention, particularly the foreign key conventions. I was just trying to make this work before figuring how if and how I should rename the TOs. Guess I should have done that first.)

                 

                However, updating the table names is not solving my problem--I don't quite understand what I'm doing wrong when trying to pull from the master People table into a single layout, and then providing in that layout, for each record, the necessary information from tables related to each TO (Program, Episodes, and MasterCueInstance [a child of RecordingSession]). If the issue is that a layout can only be attached to one TO, then I have my original problem: I cannot combine information from all three TOs correctly in one layout.

                 

                I really do feel like I'm misunderstanding something at the core of how FMP works in this instance. If there is a chapter in a manual or an explanation available somewhere that you'd like to point me to, I'm game. I've looked and haven't been able to find it, but I'm happy to self teach once I'm pointed in the right direction. If someone would be willing to boost me through the next step in this, though, I'd appreciate it.

                 

                Thanks! ~J

                • 5. Re: Combining Information from Three Distantly-Related Tables in One Layout
                  philmodjunk

                  Since all of your Table Occurrences (Relationship Graph Boxes) all interconnect. Then from any layout based on any one of the People TO's, you can access all of the other related tables. FileMaker will "tunnel" through one TO to reach other TO's that are are linked to the TO being "tunneled" thru.

                   

                  Example:

                  TOa----TOb---TOc

                   

                  From a Layout based on TOa, you can add fields from TOc because FileMaker will Tunnel thru TOb to reach them. This is very useful, but it at times requires some thought as to whether this is desirable for a given layout design.

                   

                  If you separate your TO's into groups that are not interconnected, then you will not be able to access the other related tables not part of the group. You may want to web search the term "anchor buoy" to see examples of what I am describing.

                   

                  But this does not mean that you have to use separate relationship groups. If you are setting up a layout to work with a specific subset of People records, simply do not add fields from the TO's that are not relevant and you have effectively excluded those TO's from use on the current layout.