6 Replies Latest reply on Mar 3, 2009 7:08 AM by TSGal

    Help with "triggering"  related records into a Portal

    Guy

      Title

      Help with "triggering"  related records into a Portal

      Post

      I'd be glad of help with this Portal trigger issue.
       
      Using FM9
      One file for sport league.
      Three relevant tables 
      Tables:
      Layout
      Divisions/Teams
      Game Schedule

      Layout is just that. A layout that contains one field called Division
      PLUS two portals. One portal to the Div/Teams table and one to Game Schedule.
      All the relationships appear to be set up OK......
       
      On Layout the field Division-- is a value list that lists the Divisions in a sports league. Selecting a division from the VL (eg. Div 1, Div 2, Div 3) delivers the teams in that division to the portal Teams in Division. (Between 8-12 teams per Division.)

      Using these team names as a button/trigger I want to now choose any one of the teams in the portal (Teams in Division) and have that teams schedule of games show up in the Portal Game Schedule.

      In the Schedule table my match field to any team name will be a calculated field called game. How do I script this (I would attach to team name) to have the selected team's list of game records show up in this Schedule portal.

      Help would be greatly appreciated. Thanks!

        • 1. Re: Help with "triggering"  related records into a Portal
          Guy
            
          Up to now, no reply so thought I'd give it a second try.
          Further explanation to my previous request. 
           
          I use a Value list of Divisions 
          Div one
          Div two
          Div three
          etc
          Using this VL I select a Division. Selecting a Division pours the teams in that Division into a Portal called Divisions/teams--Below left.

          PORTAL Teams in Division                   PORTAL List of games
          Team one                                                  [ Game 
          Team two                                                  [ Game
          Team three                                                [ Game
          Team four                                                  [ Game
          Team five                                                  [ Game
          Team six                                                   [ Game

          The portal Above on the right is related to the Schedules table which contains all games to be played in all divisions.

          I want to use the teams in the Portal Teams in Division as buttons to deliver the selected teams schedule into the portal on the right. I have tried various approaches and am still stumped. I'd be grateful for clear help on how to script this for a button attached to team names.
           
          (FYI--Currently, I have a VL approach in a field called Teams. By selecting a team from the VL, this delivers that teams list of games into the Portal List of games  so I am pretty sure my match field is OK.)

          Thanks for reading!
          • 2. Re: Help with "triggering"  related records into a Portal
              

            From your posts I understand that you are trying to create a game-planning. To do that i think you'll need to slightly alter your approach:
            I would expect a league to contain Divisions. (table: Division)
            A Division contains teams. (table: Team)
            A league would have a weekly game schedule. (table: Schedule)
            A schedule contains games. (table: Game)

             

            The fields in Division:
            PK_Division
            Division_name

             

            The fields in Team
            PK_Team
            FK_Division
            Team_name

             

            The fields in Schedule
            PK_Schedule
            FK_Division
            FK_Season (not in ERD)
            Schedule_week

             

            The Fields in Game
            PK_Game
            FK_Schedule
            FK_Team_Home
            FK_Team_Guest
            Game_Date

             

            The 3 first table contain the info that you want to show in the 4th table,
            so the relations could look like this:

             

            FK_Division from Team connects to PK_Division
            FK_Division from Schedule connects to PK_Division (from schedule)
            FK_Schedule from Game connects to PK_Schedule (from game)
            FK_Team_Home from Game connects to PK_Team
            FK_Team_Home from Game connects to PK_Team

             

            The valuelist for team that you create should be created from the TableOccurrence "Team" but that will only work after you have entered the FK_Division in the current game-record. (of coarse you'd already have populated the Team-table with teams and enter a FK_Division for each team)

             

            Hope this helps.

            regards, Menno







            • 3. Re: Help with "triggering"  related records into a Portal
              Guy
                

              Menno, thanks for your reply. I apppreciate the thought you put into it.

               

              In fact, my set up is very similar to the one you outlined. I already have Tables for Division, Teams and Games. And another table which pulls everything together much like your description. All of my relationships seem to be OK.

               

              Keep in mind, I do already have a functioning Value List approach that populates the PORTAL:SCHEDULE OF GAMES with a selected teams games. This works thru the following sequence. VL in field Division to select a Division. Then another VL in field Team_names to choose a team.  The field team_name is related to a calculated field Game  in the TABLE:SCHEDULE (Which contains individual records for each game. Approx 1500 games across all Divisions.)

               

              However, what I want to do is use the team_name field in the PORTAL:TEAMS IN DIVISION as a button that triggers a script to populate the PORTAL:SCHEDULE OF GAMES with that teams games for the season. I am floundering over how to create this. Any ideas on this would be greatly welcomed!

               

              (And FYI, i am not trying to create a schedule. The schedule already exists. I import it into my DB for admin purposes.)

              • 4. Re: Help with "triggering"  related records into a Portal
                TSGal

                Guy:

                 

                You can create a script and attach it to a button that populates values in a related table.  As an example, your script step might look like:

                 

                Set Variable [ $team ; <team name> ]

                Go to Related Record [ <from current table> ; <layout of related table> ]

                New Record/Request/Page

                Set Field [ <team name> ; $team ]

                 

                This should get you pointed in the right direction.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Help with "triggering"  related records into a Portal
                  Guy
                    
                  TS, thanks for your advice. Unfortunately I think I've lost the plot somewhere! Below is the script I attached to the Portal that contains the teams in Division.
                   
                  Set Variable [ $team_name ;Value:GAMES::Gl_Team_name]
                  Go to Related Record [Show  only related records;Match found set; From Table:"SCHEDULE", Using layout <Current Layout> ]
                  New Record/Request/Page
                  Set Field [ SCHEDULE:: Sched_for_suspension]***
                   
                  (*** This is the field that contains a calculation displaying one game of the selected team and looks like this: Game number; On 10/06/2009 :Team One vs Selected Team. 
                  The PORTAL for the Schedule contains 20 rows for 20 games with this field.)

                  I have tried various iterations of the script and get the following message when I activate the script.

                  "This operation could not be completed because this layout cannot display the result."

                  Below are the relevant relationship links.
                  In my Relationship Graph there is only ONE occurrence of each of the following four tables. 
                  The TABLE:GAME is the layout I am working in. This shows the Schedule Portal and the Team_details Portal (which contains Division plus other team data). The TABLE:GAME has the following relevant fields Division, Team_Name, Gl_Team_name, 

                  TABLE:GAME                    TABLE: DIVISION 
                  Division----------------------Division name

                  TABLE:GAME                     TABLE SCHEDULE
                  Division----------------------Division 
                  GL_Team_name------------------Sched_for_Suspension (Calc field)*** 

                  TABLE:GAME                     TABLE TEAM DETAILS
                  GL_Team_name------------------Team_name
                  Division-----------------------Division

                  When you have a moment perhaps you would tell me what I'm missing here. Thanks!
                  • 6. Re: Help with "triggering"  related records into a Portal
                    TSGal

                    Guy:

                     

                    In your second script step, you are using the current layout.  You should go to the layout that contains the information from the table SCHEDULE.  Then, add the record there.  As of now, since you are in the current layout, you are adding a record to the GAMES table.

                     

                    TSGal

                    FileMaker, Inc.