Set up these tables and relationships:
Actors::__pkActorID = Roles::_fkActorID
Performances::__pkPerformanceID = Roles::_fkPerformanceID
A "role" field can be defined in Roles to record the role placed by a specific actor in a specific performance.
A list view layout based on Roles can show the Performance info in the header, but list the actor names and roles in the body. You would perform a find or use Go to Related Records to pull up a found set on this layout of just the Roles for a given performance.
The list will need to show all the actors that have ever played, so all plays and all roles, is that possible with this structure? Really appreciate your help.
Yes, it is possible. but so far we have discussed this only in terms of performances, not plays. You may need an additional table linked to performances that lists each of the plays in your database.
Layouts based on Roles can be set up in a variety of different ways--including listing roles from multiple plays and/or multiple performances in the same report.
I need to see a list of all the cast members, the record, will show the name of the cast member the role they played (the role being the one and the cast name being the many) and the date of the event.
In basic terms it looks a bit like this:
Fields: Name of play, Role 1, Role 2 etc
Fields: Various fields relating to specifics of the event, date (Perhaps in a separate table), cast member name with relationship to the play
This of course the theoretical layout, my current file looks quite different. Please will you therefore explain in greater detail how I should structure this and in layman terms any relationships, calculations and scripts that I will need to use.
I really appreciate your help.
What I see here, in terms of FileMaker is really two different reports. Report one lists the play and all roles specified for that play. Report two lists the event (performance) and each actor participating in that event and the role that they played in that event.
The second part is the one that I had in mind when I said it could be printed from Roles. The first is also possible, but I think you'll want to add another table for that one, which in turn provides an easier option for that first part of the report.
Please note that I am renaming the original "Roles" table for better clarity here:
Actor_Role documents a specific Role played by a specific actor in a specific performance (Event). The new Roles table lists each role needed for a specific Play. The Play to Roles Relationship can be used to set up a conditional value list to use for selecting an Actor's Role in the Actor_Role table so that the only roles listed are those specified for the performance's specified play. ("Macbeth thus won't appear in the value list of Roles to select from for a performance of "cat on a hot tin roof"...)
For the first part of your report, you have two options: You can print it from Roles with a list view report with fields from Play put in the header and the role field placed in the body or you can use a portal to Roles placed in the header or a leading grand summary part on the layout that I am about to describe for the second part--listing the actors and roles for a given performance (event). To print out your list of roles and details about the play, you can either set up a button that uses Go to Related Records or a scripted find to find all records in Roles linked to the current record in Play.
To List the actors and roles for a specific event ( performance), You can use the same technique from a selected Performances record to pull up all the actor_role records linked to that performance. (It's also possible to list all performances (events) for a given play or just certain performances (such as those falling in a specified date range) instead of just 1 performance if you want.)
This would be a list view report based on Actor_Role with the Actor name from actors and the Role field from actor_role in the body of your layout.
Is there any chance that you could send me a screenshot of the relationships and some labels to describe the conditional value list, I'm slightly lost in your instructions, apologies for being slow here.
Thank you so much
First things first. We need to make sure that your relationships are correctly set up:
Actors::__pkActorID = Actor_Role::_fkActorID
Performances::__pkPerformanceID = Actor_Role::_fkPerformanceID
Play::__pkPlayID = Performances::_fkPlayID
Play::__pkPlayID = Roels::_fkPlayID
You will probably also need to add in a second Tutorial: What are Table Occurrences? of Roles that you link directly to Actor_Role so that you can include info about the role (such as the character's name) on your layouts and reports that refer to records in Actor_Role. But get the above relationships in place first.
For an explanation (with screen shot examples) of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained