displaying names belonging to two sets of related records
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.