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:

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

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

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

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.

Thanks, @Russell_Findlay! :grinning_face_with_big_eyes:

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

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

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

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.

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

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

Hi Russell,

How would you be fixed for a Teams Call?

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

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

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

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

here you go

Airtable - Sports Team

if you copy the base then you can see what you wish to use.

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