6 Replies Latest reply on Dec 4, 2014 6:16 AM by philmodjunk

    In each record, portal should show fields with the value of entries as they were and not change when...

    markhelm

      Title

      In each record, portal should show fields with the value of entries as they were and not change when the value of the field is changed

      Post

      Hello all,

      this is my first question on a forum so i hope this is clear :)

      I am working for a dance company and am trying to make a database to keep track of our bookings. We have different shows with different crew members and in some shows we have double crews of which only one is touring at any given time. I managed to link shows to venues on specific dates and i now want to add the crew to that date. In my table "DATES" i use a portal that shows the "CREW" that is associated to a specific show. But i only want to show the crew that is actually going to perform on that specific date. I manage to filter the portal on base of a "touring" checkbox in the "crew" portal on the "SHOW" table. But if i need to make another Date for the same show but with another crew (and check other crew members as touring), my original list of crew members in the first DATE is changed. This is a problem since i use the list of CREW to calculate the price i should charge and to automatically make the contracts for the crew members. Can anyone tell me how i can LOOKUP the "CREW" members or copy them to another table that is then shown in the portal. I tried adding a button to the complete crew list to select the ones touring and copying it in another Table but this is not working. I'm really stuck on this. thank you

       

      filemakerforum.jpg

        • 1. Re: In each record, portal should show fields with the value of entries as they were and not change when...
          philmodjunk

          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:

          Dates----<RoleCrew>-----Crew

          So that you can set up a completely new set of RoleCrew records for each performance date.

          • 2. Re: In each record, portal should show fields with the value of entries as they were and not change when...
            JohnMcGlade

            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.

            John

            • 3. Re: In each record, portal should show fields with the value of entries as they were and not change when...
              markhelm

              Dear PhilModJunk, 

              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.

              Dear Mark, 

              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.

               

              • 4. Re: In each record, portal should show fields with the value of entries as they were and not change when...
                philmodjunk

                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.

                • 5. Re: In each record, portal should show fields with the value of entries as they were and not change when...
                  markhelm

                  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

                  • 6. Re: In each record, portal should show fields with the value of entries as they were and not change when...
                    philmodjunk

                    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.