Triangular relationship quandary

[dysong3]

I am stumped with this problem.  I am sure that it is just me being dumb but I could do with a hand.

 

I am trying to set up a file which displays :

 

    • the scenes in a play, with the name of each actor and each role in each scene.

    • the names of the actors with the scenes they are in and the role(s) they are playing in each scene

    • the names of the roles, with the names of the actors playing the those roles and in which scenes.

 

In the attached file, steps one and two work fine, but I can't figure out how to accomplish step three.  Would someone be kind enough to put me on the right track?

 

 

Actors in Macbeth.fmp12.zip

[eos]

You should have a structure like

 

{ Play (implicit in this case) --< }

 

Scenes --< Appearances >-- Actors

                                           >-- Roles

 

where the focus is Appearances, with each record describing an actor in a role in a scene (of a play). 

 

This structure should allow you to compile all the desired lists.

[comment]

 the names of the actors playing the those roles and in which scenes.

 

Do actors change roles between scenes? Does an actor play more than one role in a play? Is a role played by more than one actor?

[dysong3]

Thanks eso, I think you just turned a light on over my head.

 

Yes comment, actors can change roles between scenes, an actor can play more than one role and a role can be played by more than one actor.  I should have mentioned that in the exposé.

[comment]

In such case, you do need a "star" join table to join Scenes, Roles and Actors - assuming they stay constant during a scene at least.

[dysong3]

Yes they don't change roles within a scene.  Thanks for that.

[dysong3]

Thanks for the help.  It was just what I needed to get the thing going.  I now have a further problem which should  probably be a different topic but as I don't know what the best approach would be I am not sure under which category I should post it.

 

In the "Scenes" table, I already have a portal showing all the actors playing in a given seen.  What I would like to do next is have a display of the actors that are not in the scene.  

 

One way I have thought of doing this is to have a function take the "active" actors out out of the "actors" value list. But I can only get it work taking one actor's record out of the value list at a time.  I need to group all the actors in a scene and remove them from the list.  (the custom function is "remove value" from http://www.briandunning.com/filemaker-custom-functions/recentlist.php)

 

But maybe there is a simpler way of doing this.  Can anyone point me in the right direction?

[eos]

If , as suggested, you have

 

… Scenes --< ActorInSceneWithRole >-- Actors

 

the seemingly easiest solution is adding another Actors TO behind the portal's Actors TO, related via actorID ≠ actorID, and show a portal into that TO:

 

… Scenes --< ActorInSceneWithRole >-- Actors ≠ Actors_notInScene

 

Problem: if you haven't yet assigned any actors to a scene, there is no related record to “look through”.

 

Better approach: in Scenes, create a calculation field, say, cListOfActorsInScene, type text, as 

Let (
  actorList = List ( ActorInSceneWithRole::actorID_FK ) ;
  Case ( IsEmpty ( actorList ) ; 0 ; actorList )
)

Then relate the new Actors_notInScene TO directly to Scenes, matching

 

Scenes::cListOfActorsInScene ≠ Actors_notInScene::actorID_PK

[comment]

Another option is to use an x relationship and filter the portal.

[dysong3]

eos thanks for that. I think I have applied your directions correctly, but in what field are the actors not in the scene now displayed in?

[eos]

in what field are the actors not in the scene now displayed in?

 

There is no field; what you have done was to create a match field that allows you to create a relationship that matches actors not in a scene. 

 

You said …

 

I already have a portal showing all the actors playing in a given seen.  What I would like to do next is have a display of the actors that are not in the scene.

 

… so I assumed that the display is also supposed to happen in a portal – which would be a portal pointing at the Actors_notInScene TO.

 

If you want to display them within a single field, create a calculation field, type text, with the formula 

Substitute ( List ( Actors_notInScene::name ) ; ¶ ; ", " )

or some other delimiter instead of comma/blank.

[dysong3]

Sorry to be a bit of nerd, but even though I have followed your instructions, I just can't get this to work.  Would someone mind looking at the file below and let me know what I am doing wrong, or what I need to do to display the list of actors that are not in a scene?

 

Actors-Roles-Scenes.fmp12.zip

[eos]

I have followed your instructions, I just can't get this to work

 

That's because you haven't followed those instructions.

 

You need to relate the Actors_NotInScene TO to a Scenes TO (not an Apparition TO, since that is an entirely wrong context) – and more to the point, the Scenes TO that the layout is based on where you want to place the portal.

 

Use the calculation field in Scenes as match field against the name field in Actors …

 

… which brings me to the point that you don't have a primary key in Actors. So let me suggest that before you continue working on this, create a auto-enter serial key in all of your tables, then redefine your relationships to use those primary keys.