Jul 31, 2022 05:54 AM
Hi all,
I am seeking help with a base that records football data that I am a coach for.
I currently have a Base with two tabs. One is for all the players names, positions, address and general emergency contact information.
The second tab is the ‘Games’ tab. Here I record the team playing, Date/Time, Location, Type of Game (Tournament/League), Home or away option, who was in the team (Attendance) score and who scored. I am having huge issues trying to get the data to add up the goal scorers when they score more than 1 or 2 goals and how to best record that so it can show a total on an interface.
Is there anyone able to help please, happy to jump on a teams call to discuss if this helps. Airtable support are scratching their heads at this one at the moment or would seem to over complicate the system.
Thanks in advance
Jul 31, 2022 06:05 AM
Welcome to the community, @Dan_Halliwell !
You can’t do what you want to do with only 2 tables.
You would need a 3rd table to create a many-to-many relationship. Here is Airtable’s guide on creating a many-to-many relationship:
Jul 31, 2022 06:31 AM
Hi Scott,
Thanks for your reply, very much appreciated. I will give this ago, limited knowledge and experience and I say so fingers crossed but will give it a go.
Thanks again
Dan
Jul 31, 2022 06:46 AM
Hi Scott,
Really Struggling to figure this out if I am honest. I might be over complicating it.
How would it be best to record a squad of 20 players, who will either be a Team A, B or C on a particular date as the teams change weekly, to then capture how many goals they scored each game. E.g. Today we had 2 x Teams of 8 players. Each team played 5 different teams and I recorded the result and goal scorers.
Is this possible to then capture on a 3rd tab that is easily configurable to recorded certain data against each player?
Best Regards
Dan
Jul 31, 2022 06:51 AM
This is what I am working off at the moment. you can see from each game I record the goal scorers. I have had to produce additional columns to record a single players 1st, 2nd and 3rd goal as I can only select one player at a time per column and was the only way I could capture that they scored more than 1 goal in that game.
I would love to include other recordable data such as Red/Yellow cards, substituted or not etc
Best Regards
Dan
Jul 31, 2022 10:31 AM
I think @ScottWorld ’s solution will work fine here
You want three tables (at least)
Players
Fixtures
Appearances
The appearances table will be what is called a junction table - and is a combination of player and fixture
So if
You have a player, called Dan,
Playing in the game on Sunday of the 1st team vs Manchester United (easiest to call this Fixture 1) that will be an appearance -
and it is against that appearance that you will create fields for goals scored, yellow cards etc.
You may wish to create other tables to help you create the fixtures or tk summarise data by Team … but this is optional.
Making good use of forms / views and interfaces will help you make this an easy tool to use.
Best of luck.
Jul 31, 2022 12:17 PM
Thanks, @Russell_Findlay! :grinning_face_with_big_eyes: :raised_hands:
Jul 31, 2022 12:45 PM
Thanks Russell very much appreciated. I think I get that and will see if I can implement that. It’s just getting it all to work together I guess, sounds easy reading your explanation ha but will give it a good go.
Many thanks
Jul 31, 2022 01:23 PM
Not quite - I can show you an outline tomorrow.
But in short your fixture should link to the player appearance and not the player.
Then the player appearance will have a number field for goals … etc
Jul 31, 2022 01:24 PM
Thanks Russell, really appreciate your input. Once I see where and how I will memorise that but struggling to see how it will all then link in to the Interface in the correct way. Thanks again for your help
Jul 31, 2022 01:44 PM
Hopefully this is closer for when you look. I am presuming a lot of this is ‘Linked to another record’ and selecting the ‘Players’ to add them in. I think I understand all the separation tabs and then links but I am still struggling as to where I can accurately record Player A who scores 4 goals in one game and 4 in another game and so on to then have an overall total in the ‘Interfaces’ section.
Aug 01, 2022 01:19 AM
Hopefully this will help you @Dan_Halliwell
It gives an overview of structure of the base and how the tables should connect to best be able to capture the data you need.
What it doesn’t show is how you can most easily input the data… the best thing is to get an understanding of the structure and then look at who enters the data and how
Soccer Team video explainer
and here is the layout (schema) of this base
Aug 01, 2022 01:34 AM
Hi Russell,
This is superb and can’t thank you enough. I have seen the links and excited to look at this later on. The video will be superb so thanks again. Will let you know how I get on.
Many thanks
Aug 01, 2022 12:17 PM
Hi Russell,
How would you be fixed for a Teams Call?
Aug 02, 2022 01:54 PM
Hi - I may struggle on this. I am trying to fit I. These comments on commutes etc.
I don’t really do consulting work but happy to help with any specifics
Aug 02, 2022 02:10 PM
Hi,
Completely understand and apologies for requesting. I have copied a lot over now but needed to go through each page and each column setup to ensure I had the correct code and function in place. I just thought it might be quicker if I shared my screen and we quickly went through it rather than going back and forth as I am sure this is not what you expected. Really do appreciate your help with this. The team will be made up
Aug 02, 2022 11:56 PM
No worries and happy to help (otherwise I wouldn’t be here) - just this is the practical best way for me to do it.
Fire away further questions - or I can share the prototype I set up … the challenge is that your needs will no doubt be a little different to what I have understood they may evolve.
Best of luck
Aug 03, 2022 02:10 AM
Hi Russell,
You are superb! if you would mind sharing me the base so I can configure it too as I can then see how the coding works on the formula. This would be amazing and thanks again.
Many thanks
Dan
Aug 03, 2022 04:18 AM
Sep 25, 2022 05:46 AM
Hi Russell,
Sorry for the delay in getting back. I wonder if i ask if you would be willing to jump on a teams call for me to explain the setup and see how you think this would look. I must admit my learning style is physically doing it and given the requirement i need i suspect this would be easier to discuss live time. Would this be OK with you? Could save lots of explaiming too but naturally only if you are happy to.
Best
Dan