3 Replies Latest reply on Nov 16, 2016 4:24 PM by philmodjunk

    Hockey Game with Home Team and Visitor Team


      OK, I apologize for bringing this up again but I think I have lost my mind because I had this working and now, again, after turning off my computer I get the "<no values defined>" message.


      I would like to have a way to select a Division and from there a conditional value list will only show those teams within that division.

      I would then like to have the teams selected (Home team and Visitor Team)show up on another layout I call "Hockey Game" which I can then add actions that happen during the game such as Goal, or Shot or Icing etc.


      Here is a snapshot of my relationship diagram:

      Hockey GameA1.PNG

      Here is a snapshot of what I would like to see for the game:

      Hockey Game1.PNG

      I can get the conditional value list to work if I use the Team Layout but when I move to the Game layout I get the message <no values defined>.


      How do I get the names (Not the primary key) as shown above to show on the layout?

        • 1. Re: Hockey Game with Home Team and Visitor Team

          If you change context by changing layouts, your relationship based value list no longer matches to any values to use for your value list. You would need a different relationship that works from the context of that layout and you'd need to use it as the basis for your conditional value list. This is a commonly encountered limitation of basic conditional value lists. It is not your only option.


          And the relationship between Team and J_Game_Team is not one that I would expect to match to any records unless your game is the very unique case of a team playing itself and thus being both the home and visiting team.

          • 2. Re: Hockey Game with Home Team and Visitor Team

            thanks philmodjunk,

            That explains why it won't work but is there any way to make this work? Or is there a better way to set this up?


            The only way I can make this work using my above, albeit not great, solution is after first putting in Teams with Divisions I can then have two more fields in the Team table; one called HomeTeam, the other VisitorTeam and by using 'only include related values' of Division, it seems to work. I can then transfer the info to the Game table.


            However this adds more and more records to the Team table and if I ever have to change or add teams or their divisions this doesn't seem a very good way of doing things.

            • 3. Re: Hockey Game with Home Team and Visitor Team

              The first thing that I would fix is the use of your join table to manage relationships to visiting and home teams.


              Since every Game will have two teams, your Games table and your Join Table can be on and the same table. It can link to two different occurrences of Teams like this:


              Teams|Visiting::__pk_TeamID = Games::_fkVisitingTeamID

              Teams|Home::__pk_TeamID = Games::_fkHomeTeamID


              Keep in mind that Teams|Visiting and Teams|Home have one and the same data source table.


              Your overall occurrences and relationships, squished into a single line, might look like this:




              Disvision|Visiting and Division|Home would also be occurrences of the same Division table. You'd use two so that you can get any useful Division data about both teams from the context of a record on a layout based on Games.