" 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;
The relationship to a TO might be;
TO:Plays (global ID )---<TO:AssignedView (playID ) ( PropID ) >--- TO:PropsView ( ID )
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...
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.