Connection Multiple Tables


Here’s the scenario for a fantasy football app.

I have a table called “Players”. It connects, via a Many-to-One connection, to a table called “Teams”. So, any relevant Team info can also be viewed when looking at players. (Players-Patrick Mahomes shows info for Teams-KC)

The next table is called “NFL Schedule”. This connects, via a Many-to-One connection, to the “Teams” table. This table has records, by week, for all games in a season. So, it’s set up with the fields “Week”, “Opponent”, and “Location” (home vs away). (Teams-KC shows info for Schedule Week 1, Schedule Week 2, etc.)

If I build a grid for “Teams,” I can easily add the fields stored in the “NFL Schedule” table when I view record details. So, I can see each team’s full schedule by week.

But how can I see the same information when looking at “Players”? Because that table isn’t directly connected to the “Players” table. Essentially, when looking at Patrick Mahomes, I’d like to see his NFL Schedule for the year. Is it possible?

Is the only way to solve this by directly connecting the “Players” table to the “NFL Schedule” table? That would be a hassle because each week of the NFL schedule needs to be connected to all the players on the team. In other words, Week 1 is connected to KC and to each player on KC we are tracking. Doing this for every week of the year seems a bit crazy.


Does this work?

Teams Page:

Add Schedule Page:

Team’s Detail Page:

Player’s Detail Page:

Don’t know anything about football so I’m not sure this matters, but the Player’s schedule will always match the Team’s schedule - yes?

Wow. Thank you, Peter. It works. I love it.

I recreated what you built and now need to apply it to my full app. I hadn’t considered connected fields for the Away and Home teams—that’s a fantastic idea.

Don’t worry about the lack of football knowledge. I can’t say my football knowledge created any winning fantasy teams last year! It didn’t. You’d probably beat me!

Thanks again. I appreciate your time.

Glad to hear it, David.

Oops. After I posted the above yesterday I added the following Players grid:

This way if you forget which team a player belongs to you can just search on the person’s name. The “View Player Details” link takes you to the same Player Details page shown above.

Awesome. Thanks.

I haven’t implemented the Home and Away fields. Right now, the schedule is just linked to the Parent team.

The only thing I can’t figure out is the strange way the grid appears in the live app. It looked just like your version in the test app, but any thoughts on why individual records are grouped like this? This is the “Teams Detail Page”.

But I did figure out where I went wrong before your help. It’s the “Connection Link” field. That was my big problem.
Screenshot 2024-05-16 at 10.29.40 AM

NVM. That was a simple mistake. I created the connection from “Teams” to “NFL Schedule” as a many-to-one relationship. I deleted the connection and built it in the other direction, and it works like a charm.

Thanks again, Peter.