When showing a layout in layout mode, it's important to show the text to the right of "Table:" in the status toolbar so we can identify the "context" specified for that layout.
What is the purpose to the CrewDates table? What does one record in that table represent?
It would seem that you really need these relaitonships:
So that you can set up a completely new set of RoleCrew records for each performance date.
Hi Mark, You are using dates as the reference for all of your crew scheduling. Dates are not unique, so you need to crate a unique field.
I think that your simplest solution is to make each performance unique, then any crew / cast assigned to that performance will not change when you add a new performance. As an example, let's say that you are doing west side story (WSS) at the globe theater (G) on January 15, 2015. Normal performance time is 8P, so you could create a code for that performance as WSS_G_15Jan15_8. If you add a matinee at 3PM. The code for that performance would be WSS_G_15Jan15_3 This field is the result of a concatenation of the various fields. You can then associate the cast by group or individual to that specific performance. It would be best to have a table of all shows with the associated codes West side story - wss , all theatres Globe - G . This way adding shows is easy (just copy the record for the same show and change the date or time. You can expand now any way you want, If you have 2 shows at 2 different theaters on the same day, no problem or confusion.
I hope that helps.
the first image is a layout based on the table SHOWS, the second image is based on the table DATES. The ROLECREW table keeps track of which crew member has what role in which show (since the same artist is choreographer in one show and dancer in another) so i dont think i need to have a relationship between each date and each role crew. But maybe i misunderstand you.
I understand that a date is not unique but i use the term "date" (not being a native English speaker) as a combination of an actual date, a show and a venue (if we have two shows on a same day as you specify it still comes in one DATE. A date is in fact the product i sell to the venue (so it comes on one invoice even if there are two shows and i pay the crew on the same contract even if they dance two shows.) So a date combines the day in the year, the venue where the show will be performed and the name of the show (+ additional information like the number of shows, the price agreed on etc).
I now tried to make a table CREWDATES which would have a unique ID and add to that table the unique DateId and the different unique CrewID's.
the CrewDatesID would be 1
the date ID for the show on 15 jan 2015 would be 1
there would be multiple CrewID's for each crew member involved.
I was trying to add a button to the portal in the DATES layout that shows all the crew members of a specific show to select the ones that are actually performing that one show (by adding their id's to the CREWDATES table, but i can't seem to make that work.
So i see what you mean with the association of cast members to a specific show but that is what i'm trying to do and which doesn't seem to work.
So to repeat: What is the purpose of the CrewDates table? What does one record of that table represent?
It's possible that all you need do is modify your second layout to use a portal to RoleCrew 2 rather than RoleCrew.
the purpose of CrewDates tables is to assign a Crew member to a Date. But i'm not succeeding in doing that (i succeed in doing that manually but i would like it to go through a script (as i tried with the button on the DATES Layout).
I thought i would not need that CrewDates table if i managed to lookup the CREW members which i selected as touring and assigning them to a date
What you described was not a button but a check box that marked crew members as "touring" unless I missed something here. That definitely wouldn't work for what you describe.
Let me recap my understanding to see if I have a correct picture of the issue.
Each show has multiple performance dates. Each crew member is assigned one or more roles for a given show. You can have two crew members assigned to the same role for the same show, but for different performance dates. Correct?
If I am correct, then putting a portal to RoleCrew 2 on your Dates layout is the first step towards what you need. The next part is creating the CrewDate records in order to assign crew members to specific performance dates.