Manage Relational Tables not working
Platform: Windows 7 64bit, XAMMP, Site PHP
Trying to build a MRT page for a sporting site. In this site I want to be able to manage my Teams and their players. There will be many Teams and Players can be a member of more than 1 team.
So I was wanting to build a page so that as a Player registers (insert player) then they would choose which teams they will be playing for in the season. When the season is complete, before the next one starts, that player would update their details which would include updating their new team list (update player details)
I have been getting nowhere for the last few days trying to work out this MRT behaviour.
So for the purposes of this document, use the following:
Primary Table: Players
pkPlayerID - PlayerName
1 - Bob
2 - John
3 - Sam
Secondary Table: Teams
pkTeamID - TeamName
1 - Team A
2 - Team B
3 - Team C
Link Table: TeamPlayers
pkTeamPlayer - fkPlayerID - fkTeamID
1 - 1 (Bob) - 1 (Team A)
2 - 1 (Bob) - 3 (Team C)
3 - 2 (John) - 1 (Team A)
4 - 2 (John) - 2 (Team B)
5 - 2 (John) - 3 (Team C)
6 - 3 (Sam) - 3 (Team C)
So I created the 2 DA Insert / Update / Results page for "Teams" and "Players"
Then I went to update the Player_Insert page to add the MRT code.
Followed all of the steps outlined in a previous post showing how to create an MRT by Erik (showthread.php?p=10544)
1- I added a recordset to list the available Teams (pkTeamID, TeamName)
2- Created a new 2x2 table in the insert form and placed the TeamName in one col, and a checkbox in the 2nd col.
3- I highlighted the checkbox and clicked on the dynamic bolt, and selected pkTeamID as the value and chose 1 as the Equal to value.
4- Applied the Repeat Region behavior to this table row = my Teams Recordset.
5- Made sure that the redirect on the Insert Page was removed (had to do this manually as the Insert Record Wizzard would not remove it)
6 - Applied the MRT behaviour selecting the table "TeamPlayers" and selecting WADA_Insert_PLayer Session Variable as the Primary Link (pkTeamID) and the checkbox as the secondary Link (fkTeamID)
Uploaded the content and what I found was that because I selected "1" as the Equal to Value for the Dynamic Checkbox then the Repeat Region table would always display a tick against "Team A" on page load.
I would then tick various teams for that new Player and click on the insert button.
I would then look at the SQL Data and nothing would be inserted into the linked table.
I can understand pretty much of how the MRT works, but I am a bit confused on the checkbox and how to "Bind it" to a autoincrement Primary Key field. So I think that this part maybe where i am getting into trouble.
I have also tried working this the other way ie Insert_Team and choose which players are in that team. Same result no data entered into link table.
I have also removed the pkTeamPlayerID field from the link table, just keeping the 2 foreign keys. Same result no data entered into link table.
I have been going around in circles for days over this.
Regards