Foosball Stats Table


#1

I am creating a table to record games and scores from foosball games with some friends. I am trying to calculate a few different stats and having trouble or always feel like I am doing it the hard way. Also curious what else could be done with the data available.

Anyone have some tips/inspiration?

I was going to share the table but I am not able to post a link. It basically includes all games, scores for each side, winner/loser, date. Interested in calculating stuff like avg. score differential, head-to-head matchups, longest win-streak, and whatever else. Just having fun with it and learning Airtable. The right answer to this could also be Airtable isn’t the best for this.


#2

How have you structured your tables? It sound like you should have one table that contains all of the TEAMS and another one containing all of the MATCHES. You should then have a Home Team field and an Away Team field in the MATCHES table. These would both be Link To Another Record fields that link to the TEAMS table.

With this structure you should be able to use Roll Up fields in the TEAMS table to calculate quite a lot of stuff. Generally you want to calculate something like Score Differential as a field in the MATCHES table. You can then use a Roll Up field in the TEAMS table to get the Avg Score Differential.

You should even be able to create a League Table view in the TEAMS table by ordering by Wins, Goal Difference etc.

Something like longest win-streak might be tricky. You’d need (something like) a formula field in the MATCHES table called Win Date that displays the Date if the team won. You would then need a Roll Up field in the TEAMS table with a complex aggregate formula to determine the length of the longest distance between dates in the rolled up array.

Head to Head matchups would require having columns for each team in both tables I think. Sort of like a Matrix. You could end up with a lot of columns.

I haven’t explicitly tried all of these things but I reckon they are do-able. I have created a somewhat similar structure for a cricket team I play for. Some of it will require quite advanced formulae. It should be fun to try though.


#3

Yeah I have one table with players and one table with games. I used the players table to tally wins/losses/win percentage goals scored, goals against. I don’t use Home/Away, but winner/loser. That made it easier to tally how many times a record showed up in a win field vs. a loss field. I’m not sure if that’s the best way. It would probably be smarter to actually determine a win or loss with a formula instead. Although not immediately sure what benefit that has. It would be helpful if I could share the link.


#4

Winner/Loser might work as well.

Here is a quick example of how I would do it:

When creating a new game you just link the Home and Away players in the relevant fields. Then enter the Home goals and Away goals. The Home or Away win is calculated using a simple formula.

Home wins, Away wins, Home goals scored, Away Goals Scored, Home Goals Conceded and Away Goals Conceded are all rolled up in the PLAYERS table. Formulae are used to get the totals for these. I’ve added a League Table View that orders the players by Total wins, then Total Goal Difference, then Total Scored.