7 Replies Latest reply on Jan 30, 2012 1:44 PM by PeterW45

    Help with tennis database set up and calculations

    PeterW45

      Title

      Help with tennis database set up and calculations

      Post

      I am new to FileMaker Pro. I have a flat file database that I use to track 10-12 players two times a week. We usually have one or two courts, as needed. I schedule who plays with whom and who makes a court reservation with the club and brings new balls. I simply post this on Google docs. So, I have date, matchID, P1, P2, P3, P4, and reservation. For P1-4 and reservation, I use the last names. 

      I would like to display the number of times a player made reservations and brought balls. This needs to be a ratio compared to the number of times they played. 

      I would also like to create a table that shows a who played with whom. We like the variety of combinations that can result from our number of players. I don't want to keep putting the same four guys together. When someone signs up, I want to know who they have played with the most and the least. Ideally, this would be a table with their initials across the top and down the sides. The intersection of each player would equal the number of times they have played. 

      In trying to do this, it is easy to find who made reservations as they are in a single field. Calculating how many times each has played requires a find with their name in each P1-P4 fields. Is that the best way? After that, I am confused. Do I need a calculation field or is this solved with a number of scripts?

      I am hoping someone can follow the logic of my request and suggest the structure and tools that I need to use to solve this problem. 

      Thank you in advance,

      PW

        • 1. Re: Help with tennis database set up and calculations
          philmodjunk

          You should break this up into separate records so that P1, P2, P3, P4 are the same field in 4 different records. Since you have  MatchID, this will take you from a flat file to a relational database as you'll have one table for each Match and a related table to display the players in a portal.

          Matches::MatchID = Players::MatchID

          Add your fields for who made the reservations and who brought balls to the Players table and you'll be able to compute and display your stats using summary fields on a layout based on the Players table.

          You may even want to take this system a level further to implement a many to many relationship:

          Matches----<Player_Match>----Players

          Matches::MatchID = Player_Match::MatchID
          Players::PlayerID = Player_Match::PlayerID

          With this structure, you have one record for each player in your group in the Players table and one record for each Match in the Matches table. You'd use a portal to Player_Match to assign specific Players to a specific Match. The ball and reservation fields would likely be part of Player_Match if you use this approach.

          • 2. Re: Help with tennis database set up and calculations
            PeterW45

            I just want to thank Phil for his answer. I had tried putting the data into a single field on my first attempt, but I didn't know enough to make this work. I thought perhaps separate files might have worked better. I have it back to a single field to hold each players name. I have a MatchID to group the foursomes into their matches. I have a portal so show the players on each group. So far so good. 

            This has taken me a little while to do because I needed to learn to parse the instructions. I am working on this and doing tutorials as well to familiarize myself with some of the tools needed to accomplish my goal. At least I am heading in the right direction in the early database design. I'll report back with further progress or if I need more help. 

            PW

            • 3. Re: Help with tennis database set up and calculations
              PeterW45

              Okay, my database contains a tables of:

              • MatchID, Date
              • MatchID, Name
              • ReservationID, Name
              • First, Last, email1, email2, phone1, phone2

              I have a portal for who played in a given match on a give date and who made the reservation, a report that shows who played in each match on each date, and a portal that shows the MatchID's for each player. 

              I haven't been able to figure out how to create a report that takes the MatchID's for a player and create a report that shows all of the players who played in those matches (the records from the MatchID-Name table from the list of MatchID's). Since I can get the MatchID's, I just don't know how to use that list to create a new list based upon it. 

              Is the solution one of how to create the proper report or do I need to add something to the structure of my database? 

              I have ordered a couple of Filemaker books from Amazon.com. 

              • 4. Re: Help with tennis database set up and calculations
                philmodjunk

                You haven't quite set up what I suggested here. Name should not appear in any table but the last table, (Players?) PlayerID should be a serial number field defined in the Player table. Using a player's name in place of an ID number is not a good idea. Player names aren't always unique and people change their names.

                I can't tell from your post which table is which when compared to what I suggested nor how you have related them. I described 3 tables and you appear to have 4. What is ReservationID?

                • 5. Re: Help with tennis database set up and calculations
                  PeterW45

                  This has been dormant for some time and now I wish to renew my effort. 

                  If I hadn't set up the database as suggested, it is because I am unfamiliar with this terminology. 

                  Matches::MatchID = Player_Match::MatchID
                  Players::PlayerID = Player_Match::PlayerID

                  This is what I have. I have a text file of MatchID, P1, P2, P3, P4, Res and another file of MatchID, date. I have imported these into FileMaker. I have tables of:

                  • Matches_Date: MatchID, date
                  • Match: MatchID, LastName, (PlayerID)
                  • Reservations: ReservationID, LastName, (PlayerID), MatchID
                  • Player_Dir: PlayerID, LastName, FirstName, em1, em2, ph1, ph2

                  When I import the match data, I get a table with Match ID, LastName. I repeat the import for P2, P3, P4, Reservation. The PlayerID field is empty. PlayerID is present in the Player_Dir and gets assigned upon creation. I am unable to import the Player_Dir PlayerID into the Match table. Relookup Field Contents gives an error. I don't know if this is critical as the original file contains the LastNames and not PlayerID. While it has been suggested to use PlayerID in this table, I don't know if it is necessary or if it is, how to do it upon import. 

                  I can create tables showing the number of times a player has played, their MatchID, and for a give MatchID, the players. What I don't know how to do is to take the list of MatchIDs that I can obtain by doing a Find for a LastName and use that list of MatchIDs to create a new table. 

                  This is still a little awkward for the import. I have to change the Google.doc to enter the matchID and change the format so the fields are entered as I indicated, but that is another story.  

                  I think the structure of the database is okay. I don't know whether the IDs are right. It would seem ReservationID is redundant with PlayerID. I could also enter a field in the match table for reservations. I have a problem with the empty ID fields. 

                  • 6. Re: Help with tennis database set up and calculations
                    philmodjunk

                    What you have is very different from what I have recommended:

                    Matches----<Player_Match>----Players

                    Matches::MatchID = Player_Match::MatchID
                    Players::PlayerID = Player_Match::PlayerID

                    The names to the left of the :: are table names. The names to the right of :: are field names.

                    Thus, this is a text version of Manage | Database | Relationships with the relationship details spelled out below to tell you which fields should match to which fields in the relationships.

                    Players is my name for what you have named Player_Dir it is linked to Player_Mathc and only player match. It is linked by PlayerID, not by last name. What you have will fail if you get two players with the same last name which is a pretty likely possibility.

                    When you import data from a text file, the playerID field won't get any value unless you either enable the "allow auto-enter" check box or:

                    Go to layout for Player_Dir
                    select Show All Records from the records menu
                    put the cursor in the PlayerID field
                    Select Replace Field Contents from the records menu
                    Select the serial numbers option and permit it to update entry options
                    click OK

                    Player_Match and Matches don't fully match up with any table in what you have shown me. Player_Match is used to link two players (or four for doubles) to a given Match record. Matches would be similar to Matches_date in your file as you would have one record for each match played and a MatchID field would be set up in it as an auto-entered serial number.

                    • 7. Re: Help with tennis database set up and calculations
                      PeterW45

                      I have created the database as suggested. This stripped down database lets me understand that in this form, the database is simply flat file of Player_Match. The Player data is not needed at this point. The MatchID can be stripped out as a separate table. 

                      The solution to the next step that I was seeking is simple, perform a find, sort the records, and use a summary to show a count of number of times each player has played. 

                      Re: PlayerID vs LastName

                      Since this data is from a table, the names are unique. If they weren't, then the schedule would be ambiguous. Making them unique is simple anyway. If I had Adam and Bill Jones, I would label them JonesA and JonesB. Adam and Bill could read the schedule. With PlayerIDs, not so much.