1 2 3 4 Previous Next 54 Replies Latest reply on May 8, 2013 1:25 AM by adhutchinson

    Recording Statistical Data?

    JamesCoe

      Title

      Recording Statistical Data?

      Post

      Hi Guys,

      I'm involved with a local soccer league and I would like to be able to record a database of statistical information about the performance of each player over the course of a season. Each week, I get given a match report for every match that is played including which players were on the pitch (and for how many minutes) and details of any yellow/red cards that were awarded. In addition to this, I am given information about each goal (goalscorer, assister, time).

      I would like to be able to see at a glance the total number of goals, minutes and assists for each player as the season progresses, whilst also being able to drill down into which matches those goals occurred in if required.

      I don't even know where to start with this project, so any help would be greatly appreciated.

      Thanks

        • 1. Re: Recording Statistical Data?
          GuyStevens

          Let's start with the beginning shall we.

          I always start out just writing the structure down on paper.

          So what do we need?

          A table "Players" for all the information about the players that doesn't change. Like:

          Players  (Table)

          Id - A number field with auto enter serial number
          FirstName
          LastName
          c_FullName  - A calculation field that calculates: FirstName & " " & LastName
          Adress
          Phone
          ...

          Then a table for every match played.

          Match (Table)

          Id - A number field with auto enter serial number
          Date  -  A Date field
          Location
          OtherTeamIdFk  -  Could be linked to a list of other teams. So you'll need a table "Teams"

          Then you need to note the details of the match. I'm guessing here but I'm thinking something like:

          MatchDetails (Table)

          Id - A number field with auto enter serial number
          MatchIdFk
          PlayerIdFk  -  A number field so you can link to a player
          TimePlayed  -  Or you could note TimeIn and Time Out and calculate the amount of time he played. You could even do this real time and have buttons that set the time someone goes on the field and the time someone goes off.
          YellowCards  -  Number field where you enter the amount of yellow cards the player got.
          RedCards  -  Is probably only 1 So you could also make this a checkbox on the layout with a value of 1
          Goal  -  Could again just be a checkbox with a value of 1 and then you just make a new record for every goal this player makes.
          Assist  -  Could again just be a checkbox with a value of 1 and then you just make a new record for every assist this player makes.
          s_SumGoals  -  A summary that shows the total of "Goal"
          s_SumAssists  -  A summary that shows the total of "Assist"
          s_SumTime  -  A summary that shows the total of "TimePlayed"
          ...

          Maybe you could add something about injury, or ...

          Then you need to make the following relationships:

          1) Players::Id----[=]-------MatchDetails::PlayerIdFk
          2) Match::Id-----[=]--------MatchDetails::MatchIdFk

          And possibly the OtherteamIdFk to the Id field of a "Teams" table.

          Define the second relationship to "Allow creation of records via this relationship" on the MatchDetails side.

          I think this would be a good start.

          You make a layout for your players info. Based on the Players table.

          And a layout based on the Match table. On that layout you add a portal that's based on the MatchDetails table.

          You add the PlayerIdFk field and make it a dropdown to get it's date from the Players table. The first field is the Players::Id the second field is the Players::c_FullName.

          You can set this to "Only show data from second field"

          And in the portal you also add the other fields:
          - TimePlayed
          - YellowCards
          - RedCards
          - Goal
          - Assist

          If you are that far let me know and we'll continue  :)

          • 2. Re: Recording Statistical Data?
            GuyStevens

            Also, do you use Filemaker 11 or 12?

            • 3. Re: Recording Statistical Data?
              JamesCoe

              I'm using Filemaker 12, although I've only had it for a few days, so I'm still figuring out how to actually do things.

               

              So, I've now created the tables that you've suggested, although I've changed them slighty (hopefully not to the extent that it doesn't work anymore).

              I've got a table called 'Players' with the fields Id, FirstName, LastName, c_FullName, Team, PrimaryPosition, SecondaryPosition and Age.

              The next table, 'Match' has the fields Id, Date, HomeTeamIdFk, AwayTeamIdFk and Result.

              The table 'MatchDetails' has Id, MatchIdFk, PlayerIdFk, TimePlayed, YellowCards, RedCards, Goal, Assist, s_SumGoals, s_SumAssists and s_SumTime.

              The final table 'Teams' contains two fields Id and TeamName

               

              I've also made the relationships that you suggested and have linked the HomeTeamIdFk and AwayTeamIdFk to the Teams::Id field (apparently I needed to create a duplicate table to do this and I'm not sure whether I was supposed to). Would it also make sense to link the Players::Team field to Teams::Id?

              What should I do next? Do the calculation fields (FullName and the summary fields) work automatically or do I have to set them up somehow? What about this layouts thing? I don't really understand what that's for either.

              • 4. Re: Recording Statistical Data?
                GuyStevens

                (apparently I needed to create a duplicate table to do this and I'm not sure whether I was supposed to)

                You did not need a duplicate table but you did need a second table occurence for this to work.

                I hope that's what you did.

                In fact it would be best if you made two new table occurences (using the two green arrows) and named them "TeamHome" and "TeamAway"

                And left the original "Team" Table Occurence for what it was.

                Then you made these relationships:

                Match::HomeTeamIdFk-------[=]---------TeamHome::ID
                Match::AwayTeamIdFk-------[=]---------TeamAway::ID

                That way later on it will be clearer which fields you need to get from where.

                What should I do next? Do the calculation fields (FullName and the summary fields) work automatically or do I have to set them up somehow?

                They work right away, that is if you entered the calculation in the c_FullName field like the calculation I gave.
                Also the summary fields work right away.

                First of all, let's make a few corrections:
                You created a Team field in the Player table. But you also made a teams table. So I would change that "Team" field in the Players table into a Number field called "TeamIdFk"

                And relate it to yet another TO of the Teams table. Maybe here you can use the original TO.

                Players::TeamIdFk-----[=]-------Teams::Id

                Then you should have this:

                Relationships

                What about this layouts thing? I don't really understand what that's for either.

                Now that you have created your tables you have been given a few layouts for free :)

                But we will keep them for later on. We will be using these layouts mostly in table view to see the actual data that's in the table.

                Now we need to start making layouts. So we can enter data in a pretty looking and easy form.

                I suggest we start with a layout for the players. Go to one of the existing layouts (let's take players) and go to Form view and go to layout mode. Now at the top click "New Layout"

                Let's Call it "LayPlayers" and make sure it's based on the Players table. Add all the fields you need and choose a cool theme.

                Now you can start making it easy fir yourself. For instance on the Position fields you could add a dropdown list with all the possible positions. Then in the future you can just select a certain position.

                See how fun layouts are :)

                The TeamIdFk is the first tricky part. It needs to be a dropdown.One like this:

                Dropdown

                And next to the dropdown you put the "TeamName" field from the related Teams table.

                And that concludes the lesson for today :)

                • 5. Re: Recording Statistical Data?
                  GuyStevens

                  Here's my file untill now:

                  http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Soccer_League.fmp12

                  If you are stuck with something.

                  • 6. Re: Recording Statistical Data?
                    JamesCoe

                    Fantastic, you're a hero!

                     

                    I've now managed to build myself a layout based on my Players table, like you suggested, and I have the duplicate TOs of the Teams table and the TeamIdFk sorted. Out of interest, what does the 'Fk' part actually mean?

                    I don't seem to be able to make the summary fields work out properly; the "Options for Summary Field" dialog box has the 'available fields' panel grayed out when I select total. It would let me set it up to count rather than total, but I don't think that's right.

                    Apart from that, though, I appear to be following what you're saying. Yay!

                    Thanks so much for helping with this.

                    • 7. Re: Recording Statistical Data?
                      GuyStevens

                      Out of interest, what does the 'Fk' part actually mean?

                      You have two types of "Keys" a primary key. (PK) That's the ID field in a table. For instance every Player has an ID, That is the unique value in that table that uniquely defines that certain player. No other player has the same ID. Two players can hace the same name, but none have the same ID.

                      Now if you want to refer to a certain player from another table (like the matchdetail table) you need a field where you can store this players ID. This field is called the Foreign Key (FK). Because you are storing the ID of a Player from the Player Table in another table.

                      I don't seem to be able to make the summary fields work out properly; the "Options for Summary Field" dialog box has the 'available fields' panel grayed out when I select total. It would let me set it up to count rather than total, but I don't think that's right.

                      Every field has a type. In me explanation of tables I always stated what type your field needed to be. It can be Text, Number, Date, Time, Timestamp, Calculation, Container, Summary.

                      You can't create the total of a Text field. Only of a number field, or a time field.

                      Your YellowCard, RedCard, Goal and Assist fields all need to be number fields, otherwise you can't calculate the total. In these fields we will be typing numbers.

                      The rest of the lesson continues in a bit, so get ready  :)

                      • 8. Re: Recording Statistical Data?
                        JamesCoe

                        Wonderful. I've updated those fields now; probably should've figured that out on my own, but oh well. I've also just filled in the names of my teams in the teams table. In the Field Options for the Id field I've set it up as a serial number like you said at the beginning. I await the next lesson Laughing

                        • 9. Re: Recording Statistical Data?
                          GuyStevens

                          In my next example I made it so that you can immediatly enter new teams from within the player layout.

                          But you can check that in my example later. I don't have much time now so this lesson will go mighty fast :)

                          Now we need a new layout based on the Match table.

                          Go into layout mode, click new Layout, make sure it's based on the Match table, add the fields Date, Awayteam, HomeTeam, Result.
                          Make sure you choose a cool theme (or the same one, otherwise it might get a little to funky)

                          Make the date a dropdown calendar "in the inspector "Data" tab undet "Control Style". Include icon to show and hide calendar.

                          Now the home and away teams can use the same dropdown we already created before for the player layout.

                          But the field you need to put next to them to see the correct team needs to come from the correct table occurrence.

                          If you have any doubts go to File- Manage - Database and the tab "Relationships" to see what TO is connected to what field. But we should have given them clear enough names to make it simple.

                          So add:

                          Teams_Home::TeamName
                          Teams_Away::TeamName

                          to your layout by dragging down the "Field Tool" button: Field Tool

                          Now you should be able to select the two teams and see their names show up.

                          If you want we can also make it so thatn you can create teams from here.

                          Moving on.

                          Let's make the layout a litthe bigger by draggin out the sides.

                          Now create a portal on the layout, make it nice and big. Make sure it's based on the MatchDetails table. And add following fields:
                          MatchDetails::PlayerIdFk
                          MatchDetails::TimePlayed
                          MatchDetails::YellowCards
                          MatchDetails::RedCards
                          MatchDetails::Goal
                          MatchDetails::Assist

                          Click ok and go to "File" - "Manage" - "Database" - "Relationships"
                          Double click the [=] sign between Match and Match Details and
                          - Allow creation of records
                          - Delete related records in this table when...

                          On the MatchDetails side.

                          This means that we can enter match details from the match layout and when we delete a match, the details are also deleted and we aren't stuck with a bunch of orphaned data.

                          Now the PlayerIdFk needs to be a dropdown like this:

                          Dropdown

                          And I'm going to post this first, because if I loose this post I'll flip :)

                           

                          • 10. Re: Recording Statistical Data?
                            GuyStevens

                            Now we are presented with a problem.

                            Because we have the ability to select a player. But there is no way to display this players name because the Player table is not linked to the Match Details table.

                            Let's fix that.

                            We are going to use the Anchor Bouy method here, it's not really important what that is, but you can alway look that up. We are going to create a new TO (Table Occurrence ) of the PLayers table so we can use that date in the Match Details table. And this way our relationships dialogue stays nice and neat.

                            Change it's name to "PlayersMatchDetails" so we know what it is and relate it like so:

                            Relationship

                            Now you are going to learn a trick. What we want is to be able to select a player but not need to worry about his ID. We don't need to see it.
                            Let's copy the PlayerIdFk field by holding Ctrl (on windows) while dragging the field away (a little plus sign will appear.) When you let go a "Specify Field" dialogue appears.

                            Select the "PlayersMatchDetails" field and the c_FullName field. Uncheck "create lable. Now the trick is to change a few settings in the Data tab of the inspector.

                            Make sure the C_FullName field is an "Edit Box" and has Browse mode unchecked under "Field Entry"
                            Make sure the PlayerIdFk field remains the dropdown but has "Find Mode unchecked.

                            Now comes the magic: Select both fields and use the Align buttons under the position tab of the inspector to but both fields on top of each other. The idea is that the PlayerIdFk field is below the C_FullName, but that should be the case.

                            Check it out in browse mode and you should have something like this:

                            Image

                            Now add some titles to the top like the Players title I made and resize your fields.

                            • 11. Re: Recording Statistical Data?
                              GuyStevens

                              A few questions: What do you put in the Time played field? The amount of minutes? Or the time like: 1:30

                              If you want to enter minutes you need to make this a number field.

                              For the other fields, do you put the totals in there? Like if a player has 2 yellow cards, do you then enter 2 or do you create two records for yellow card and do you just want to check a check box under Yellow card.

                              The same goes for the goals and assists. So you put the total amount there, or do you just check it once and then make a new record for the same player when he scores again?

                              I need to go now because I'm terribly late, we'll continue later if I'm not back to late.

                              Here's my file for now:

                              http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Soccer_LeagueV2.fmp12

                              Also: for the dropdown technique: You can use that to for the teams, but then you won't be able to add new teams from within the other layout.

                              Like you can in my Players Layout.

                              Check It out.

                              P.S. Another question, what do you put in your Result field? Do you just put: Won, Lost or tied?

                              • 12. Re: Recording Statistical Data?
                                GuyStevens

                                Another Question:

                                If you select two teams, do you want the ability to immediatly import all players from one of the two teams (your team) into the match details portal?

                                So you don't need to manually select every player.

                                There might be players entered that haven't played that way, but you can remove them.

                                (Just thinking ahead)  :)

                                • 13. Re: Recording Statistical Data?
                                  JamesCoe

                                  I'm actually trying to keep the details for every team (I'm working with the guy that organises the league, rather than any particular team) so if I could automatically import the players from both teams, that would save a lot of time.

                                  The times will be in minutes and (I think) I've already made that a number field. If not, I'll do it now.

                                  As for your other questions, I'm not really sure, to be honest, because I don't understand enough about what goes in each field and how the records are compiled at the end. Maybe if I explain what I want to achieve, you can advise the best way of doing it. I would like to have player specific fields that update over the course of the season with their total number of minutes, goals, assists and cards (split into yellow and red would be good). I would also like a record of information about each goal (match, scorer, assiter, time). I have a spreadsheet to calculate the expected win-probabilities for each team before and after a goal is scored, which is then converted into an index for how 'important' a goal is (for example, the goal scored in a 1-0 win is more important than the 5th goal in a 6-0 win). If possible, this would then link back to the player so that I can see the average importance index for each of the goals that they score and assist.

                                  The dropdown thing should be fine, though, because the teams for next season have been confirmed and won't change, so the ones I enter now will be fine and I can go back to the table view to change them for next season if necessary.

                                  And yes, the result fields is just going to be a simple win-loss thing; I wasn't originally going to put it in at all, but figured that as there was a sensible space for it there was no reason not to.

                                  • 14. Re: Recording Statistical Data?
                                    GuyStevens

                                    I'm working with the guy that organises the league, rather than any particular team

                                    Ah, that changes the situation a little bit. I thought you were part of one particular team.

                                    Then we are going to change our structure a little bit. No problem, just a little remodeling. :)

                                    I just made some changes in my new file and I'll try and backtrack them for you so you can follow.

                                    What I would like to do is to create two portals on the Match layout so you can see the players from different teams in different lists.

                                    You select two teams, click the "Import Players" button and your players appear.

                                    Then you enter their data and the score is automatically shown. I don't know however what you will be doing with your Result field.

                                    What I have in mind is a little like this:

                                    Match Layout

                                    An additional problem is that you can select a player from any team and add it to the match details.

                                    But you might want to limit that to showing only players for the selected team.

                                    I don't know if you ever have players change teams, or play with another team.

                                    But we'll deal with that later. Let's put it on the To-Do list :)

                                     

                                     

                                     

                                     

                                    1 2 3 4 Previous Next