3 Replies Latest reply on Feb 26, 2012 9:44 AM by PatrickJackson

    displaying names belonging to two sets of related records

    PatrickJackson

      Title

      displaying names belonging to two sets of related records

      Post

      I have a database containing three tables: PROPS, PLAYS, and ASSIGNMENTS. The first two consist of records naming props and plays respectively; the third contains records that contain the field propname and playname. PROPS and PLAYS are related to ASSIGNMENTS in the way that you might suspect, using propname and playname as key fields. I am able to use portals to pull a list of props for a given play and a list of plays for a given prop. What I would like to do is to have FileMaker display a list, for any two plays, of props that are used in both plays. My intuition is that this ought to be possible using portal filtering (I am using v.11) but I can't quite figure out the syntax. Can anyone help? Do I need to create a fourth table and somehow relate it to the others to make this work?

       

      Ideally I would like to be able to select the two plays from pop-up menus (from a value list containing the names of all the plays) and have the names of the relevant props show up on the page at that point. Then if different plays were selected, different props would show up.

        • 1. Re: displaying names belonging to two sets of related records
          RonCates

          " PROPS and PLAYS are related to ASSIGNMENTS in the way that you might suspect, using propname and playname as key fields "

          The first thing to address is your use of data fields for key fields. Each table should have an auto enter serial number ID field and all relationships should be based on these. that way if you change the name of a propname or playname your relationships will not be broken. This is basic relational database 101. It is extremely important. Should you continue to build relationships based on data the way you have you will without doubt run into numorous problems in the future.

          To filter the portal the way you would like you may be able to use FileMaker 11s filtered portals but I am still using filtered portals the way we did before 11. To have a poortal display props from more than one play you would have a relationship based on a global field that would hold the play ids in a return seperated list. You might use a script to populate this field based on your dropdown selection. So the field my contain play IDs like this;

          GlobalDs
          1011
          1013
          1025

          The relationship to a TO might be;

          TO:Plays (global ID )---<TO:AssignedView (playID )  ( PropID ) >--- TO:PropsView ( ID )

          • 2. Re: displaying names belonging to two sets of related records
            PatrickJackson

            I inherited the database, and haven't had a chance to switch it over to serial number IDs yet -- that's on the to-do list :-) but first I would like to understand how to get this "props used in mutiple plays" thing working. I'm new at this so I want to make sure that I understand your explanation. A global field in PLAYS containing the playID of each play I select (for the sake of argument, just two plays) from a pair of dropdown menus is matched to the playID field in ASSIGNMENTS; propID in ASSIGNMENTS is then matched to propID in PROPS. But then why wouldn't a portal in PLAYS show all the props in either play? Unless I am missing something, which is very likely the case...

            • 3. Re: displaying names belonging to two sets of related records
              PatrickJackson

              Actually figured out a solution to my own problem. Create second instance of ASSIGNMENTS table, and a new table OVERLAPS. Records in OVERLAPS have two drop-down menus that use value-lists of the names of all the plays; these fields are play1 and play2. OVERLAPS is then related to the ASSIGNMENTS table using  play1 as a key field (yes, I know it should be serial number IDs, but one thing at a time), and ASSIGNMENTS is related to the second instance of itself (ASSIGNMENTS2) using the propname as a key field. Then, in OVERLAPS, each record contains a portal that displays the propname of related records from ASSIGNMENTS2, filtered with the following condition:

              ASSIGNMENTS2::playname = OVERLAPS::play2

              Selecting the name of two different plays from the dropdown menus gives me a list of props that are used in both plays.

              it might not be the prettiest or most textbook-orthodox solution, but it works for my purposes at the moment.