2 Replies Latest reply on Jun 10, 2011 9:05 AM by philmodjunk

    Help with a Join relationship and Portal



      Help with a Join relationship and Portal


      Hi there folks,

      This problem seems to be getting the best of me any help is appreciated.

      I have two tables Performers and Rehearsals and I have a Layout that is drawn from the Rehearsals table with a portal show records from the Performers Table.

      I have created a join table called Rehearsals by performers where I have the id field for perofrmers name and the Id field for Rehearsal number showing up.

      My qyestion is in two parts.

      1. When i create a new record in the Rehearsal layout I want the the portal to Performers to show all performer names when the record is created as Each performer can be in every rehearsal, I then want the user to be able to click a radio button that creates a record in the Rehearsal by performers table that has the following information: Performer name, Rehearsal number, Rehearsal Date, Rehearsal Time, and a calculation field that shows the number of minutes late a performer is.

      I am having trouble getting all the performers names to show up when I create a new record.

      2. The calculation field is also a little tricky for me I want the reslut to be in whiole minutes I am currently using (Get(currenttime) - Rehearsal start time)/3600 but it does not display the correct result.

      any help would be greatly appreciated.



        • 1. Re: Help with a Join relationship and Portal

          Regarding the second bit, you could just change the field to "time" and use the time formating on the inspector. What format do you want the user to enter into the time fields? What is more natural for them?. 

          Your first part can be solved with a script attached to the radio button (would personally just use any old button). The rehersal join table is just a simple join table or does it have more information? Could you get away with a few lookups in the rehersals table? Just some ideas to help along the process.

          • 2. Re: Help with a Join relationship and Portal

            Hmmm, "All Performers" means "All performers in the database" or "all performers for this performance?" Leaving that aside for the moment and assuming All performers in your database, you can set up this relationship in order to get a portal listing all performers:

            Rehearsals::anyfield X AllPerformers::anyField  (AllPerformers is a new table occurrence of Performers.)

            Now you can place a portal to AllPerformers on your Rehearsals layout to list all performers.

            If you place a button inside the portal's row, it can perform this script to do what you want:

            Set Variable [$PerformerID ; Value: AllPerformers::PerformerID]
            Set Variable [$RehearsalID ; Value: Rehearsals::RehearsalID]
            Freeze Window
            Go To Layout [Rehearsals by performers]
            New Record/Request
            Set Field [Rehearsals by performers::RehearsalID ; $RehearsalID]
            Set Field [Rehearsals by performers::PerformerID ; $PerformerID]
            Go To layout [original layout]

            You may want to consider an alternative approach. You could choose to use a script to create a record for all performers in the Rehearsals by performers table for each rehearsal record. Then you can add a radio button field to mark the listed performers present or absent and a script trigger on this field can log the time so you can calculate minutes late.

            PS. You appear to be using performer names as your performer ID's this is not the optimum way to do this. Names are not always unique, they are subject to change and correcting a misspelled name after related records have been created can be tricky. You can avoid such issues by using an auto-entered serial number for the Performer ID.