Troubleshooting Many-to-Many Player-Team Relationships in sport club app

Ask for Help
1

Hello,
I am building an app to manage a volleyball club.

The club has various team (for the sake of simplicity let’s say 2: TeamA and TeamB).

Each player can belong to one or multiple teams.

For example Jack belongs to TeamA only; Jill belongs to TeamA AND TeamB

I’ve got 3 tables:

  1. Players_Teams Table: A junction table connecting players to teams

[Team | Rel_Player
TeamA | Jack
TeamA | Jill
TeamB | Jill]

  1. Players Table: Contains player information, with a “Rel_Teams" column showing teams they belong to
    (relation: Relate to items where the value in: Name Matches the value in:Players_Team–>Players_Name [match multiple])

[Name | Photo | Position | Rel_Team
Jack | photo | OH | TeamA
Jill | photo | S | TeamA TeamB]

  1. Teams Table: Contains team information (Team_Name) and Rel_Player with related players

Expected:
[Team_Name | Rel_player
TeamA | Jack Jill
TeamB | Jill]

Observed:
[Team_Name | Rel_player
TeamA | Jack
TeamB | ]

The values are correct in the Players Table and Players_Teams Table.
However, in the Teams Table Jill (who belongs to both TeamA and TeamB) does not appear.

My hypothesis is that it has to do with the data structure and the many to many relation, but I cannot figure out what’s wrong and how to fix it.

Any help is much appreciated.
Cheers

2

Can you show how the relation in your Teams table is configured?

3

Oh dear …
I was pointing to the wrong column.
So sorry to bother. I promise I’ve been trying for a couple of days #blush

Anyway this is how it works (now).
Name: Rel_Players
Type: Relation
Relate to items where the value in: Team_Name
Matches the value in: Players–>Lookup_teams [match multiple]

Where in the Players Table
Name: Lookup_team
Type: Lookup
Relation column: Rel_Team–> Team

And Rel_Team is
Name: Rel_Team
Type: Relation
Relate to items where the value in: Name
Matches the value in: Players_Teams–>Player_Name [match multiple]

I did that because I could not reference the Rel_Team column directly.

1 Like
4

So it’s working okay now?

Just one additional comment I would make… instead of matching on names, it’s generally a better practice to add RowIDs to your tables and use those in relations. This is because RowID values will never change, but names could change (which will potentially break things if they are used in relations).

3 Likes