Team Pickems with active leaderboard

Ask for Help
1

Hello my fellow gliders,

Im looking into converting my excel sports pickems sheet into and app. Currently, i send out a pick sheet where participants pick a winner for each match-up, with a total of 16 match-ups per week. I they also have to insert a number (total combined points) for a selected game to serve as a potential tiebreaker.

I then input their selections (picks) in my databases where the total list of participants will be competing against each other for the most correct picks for the week. I use several different formulas to generate the active leader as the matches conclude, with the most correct picks at top in a descending order.

Has anyone seen or done anything similar on glide that can point me in the right direction? My main goal right now is to make it possible for any user to sign in to their user profile, make their picks for the week, and have it populate to the list of all the players for the week.

I hope this question makes sense and appreciate any info you can provide.

Thank you,
Andres

2

Hits very close to home as a Fantasy NBA player.

Here’s my suggested data structure:

1. Users Table (No Changes)

  • Columns: UserID, Name, Email, Profile Pic

  • Sample Data:

UserID Name Email Profile Pic
U001 Andres V andres@example.com (image_url)
U002 Bob Smith bob.s@example.com (image_url)
U003 Alice Green alice.g@example.com (image_url)
U004 Charlie Brown charlie@example.com

2. Weeks Table

  • Columns: WeekID, Week Number, Start Date, End Date, Status, Tiebreaker Game (Relation to Games.GameID)

  • Sample Data:

WeekID Week Number Start Date End Date Status Tiebreaker Game (Links to GameID)
W001 1 2024-10-22 2024-10-28 Completed G004
W002 2 2024-10-29 2024-11-04 Upcoming G020

3. Games Table

  • Purpose: Lists actual NBA games. Admin enters results.

  • Columns: GameID, Week (Relation), Game Date/Time, Away Team, Home Team, Away Logo, Home Logo, Winning Team (Admin Input), Game Status

  • Sample Data (Week 1 - W001):

GameID Week Game Date/Time Away Home … Winning Team Game Status
G001 W001 2024-10-22 19:30 ET Lakers Celtics … Celtics Final
G002 W001 2024-10-22 22:00 ET Suns Warriors … Warriors Final
G003 W001 2024-10-23 19:00 ET Nets Bucks … Bucks Final
G004 W001 2024-10-23 21:30 ET Nuggets Jazz … Nuggets Final
G005 W001 … Heat 76ers … 76ers Final
… (G006-G016) … W001 … … … … (Result) Final

4. Picks Table

  • Purpose: Records each user’s prediction for each game.

  • Columns: PickID, User (Relation), Game (Relation), Week (Relation), Selected Team, Tiebreaker Score

  • Sample Data (Week 1 - W001):

PickID User (UserID) Game (GameID) Week (WeekID) Selected Team
P001 U001 G001 W001 Celtics
P004 U001 G004 W001 Nuggets
(…14 more picks for U001…) U001 … W001 …
P017 U002 G001 W001 Lakers
P020 U002 G004 W001 Jazz
(…14 more picks for U002…) U002 … W001 …
P033 U003 G001 W001 Celtics
P036 U003 G004 W001 Nuggets
(…14 more picks for U003…) U003 … W001 …
P049 U004 G001 W001 Lakers
P052 U004 G004 W001 Nuggets
(…14 more picks for U004…) U004 … W001 …

5. UserMatchups Table (Simplified)

  • Purpose: Defines head-to-head pairings and determines the winner based on weekly pick scores, handling ties via tiebreaker.

  • Columns:

    • UserMatchupID (RowID - Primary Key)

    • Week (Relation to Weeks.WeekID) - The week this matchup occurs.

    • User1 (Relation to Users.UserID) - First user in the pairing.

    • User2 (Relation to Users.UserID) - Second user in the pairing.

    • User1_Score (Number) - Calculated: User1’s total correct picks for this Week.

    • User2_Score (Number) - Calculated: User2’s total correct picks for this Week.

    • Winner (Relation to Users.UserID) - Calculated: UserID of the winner (or NULL if a perfect tie on score and tiebreaker).

    • Outcome_Description (Text) - Calculated (Optional, for clarity): Describes how the win occurred (e.g., “Score”, “Tiebreaker”, “Tie”).

  • How the Calculations Work (Conceptually in Glide):

    • User1_Score and User2_Score are calculated first (e.g., using Rollups on the Picks table filtered by User and Week, counting correct picks).

    • To calculate Winner, Glide needs:

      • The User1_Score and User2_Score.

      • The tiebreaker difference for User1 for that week (let’s call this TB_Diff1, possibly looked up from the Users table where it was calculated).

      • The tiebreaker difference for User2 for that week (TB_Diff2, also looked up).

    • The Winner column logic (e.g., using an If-Then-Else column):

      1. IF User1_Score > User2_Score THEN User1

      2. ELSE IF User2_Score > User1_Score THEN User2

      3. ELSE IF TB_Diff1 < TB_Diff2 THEN User1 (Scores tied, User1 wins tiebreaker)

      4. ELSE IF TB_Diff2 < TB_Diff1 THEN User2 (Scores tied, User2 wins tiebreaker)

      5. ELSE NULL (Scores tied AND tiebreaker differences tied)

    • The Outcome_Description column logic (Optional):

      1. IF User1_Score != User2_Score THEN “Score”

      2. ELSE IF TB_Diff1 != TB_Diff2 THEN “Tiebreaker”

      3. ELSE “Tie”

  • Sample Data (Week 1 - W001 with Simplified Structure):

UserMatchupID Week (WeekID) User1 (UserID) User2 (UserID) User1_Score (Calc) User2_Score (Calc) Winner (Calc → UserID) Outcome_Description (Calc)
M001 W001 U001 U002 11 9 U001 Score
M002 W001 U003 U004 10 10 U003 Tiebreaker
M003 W001 U005 (New) U006 (New) 12 12 NULL Tie
1 Like
3

Thank you kindly! I didn’t expect this detail of a response, I truly appreciate it. I’m going to see if I can set this up properly. Will this setup work for an every man for themselves (80 -100 users) setup, where the winner takes all outcome? I would also need to create a pick sheet form that each signed in user can make their weekly selections from.. but that’s a problem for another day, lol

Thanks again!

4

Yeah I assume that doesn’t take into account matchups. You can just count the correct picks inside the picks table for scoring.

5

Awesome. I’m just starting to learn Glide so I’m still having trouble relating columns and what not. Do i need to prefill all the Week rowIDs, Game IDs etc.. ?

6

RowIDs are just my way to show you how those link together. In reality, you just add a rowID column in each of those table to use as rowIDs.

However, when you create linked records, say a pick for a game in a week, you must record the relevant gameID and weekID from relevant tables.