1 2 Previous Next 22 Replies Latest reply on Nov 3, 2011 8:43 AM by MEh

    Problem with if / sum

    MEh

      Title

      Problem with if / sum

      Post

      Hi there,

      I've set up a database where players of a team and the goals they scored are stored. On every player record all matches the team played are shown (from another db called Games). Besides each match I have a (repeating) field Goals where I put the number of goals the particular player scored in that particular match. Besides that I want fields called "Total amount of goals National Competition", "Total amount of goals Competition" and "Total amount of goals International Competition". In the db Games I have a field Competition in which is filed in what competition the particular game is played (National, International or Friendly).

      I defined the field Total amount of goals Friendly as follows: 

      Field name: Total amount of goals Friendly
      Type: Calculation
      Options / comments: Indexed, If ( Games::Competitie="Friendly" ; Sum (Goals) ; "0" )

       Something is wrong here because all goals are counted no matter what competition they are scored in.

       Can someone point out what I am doing wrong?

      More about my db and the reason I use a repeating field here, can be found in this Insert one db into another. Help needed..

      Thx.

        • 1. Re: Problem with if / sum
          philmodjunk

          I imagine that your relationship links a given record to many records in Games rather than just one record.

          Games::Competiter

          Refers only to the first related record in games. If this field has "Friendly" stored in it, you'll get the sum of all repetitions of Goals. If this first related record does not store the word "friendly", you'll get zero.

          • 2. Re: Problem with if / sum
            DavidJondreau

            M Eh,

            You really ought to restructure your database to get rid of the repeating fields. You'd want a table named Participation where there's a record for each player/game combination. That is where you'd store number of goals scored. You can then easily perform aggregation functions on the related records.

            • 3. Re: Problem with if / sum
              MEh

              Thanks but this goes a bit beyond my level of knowledge I think. But I'm trying hard to understand it.

              I'll first explain how my db is structured.

              Table 1: Players
              Fields: player name / date of birth / nationality / fieldposition / Sign up date

              Table 2: Games
              Fields: Date / competition / Oponent / Home-Away / Goals Home / Goals Away

              Having said that: I've included Table 2 (Fields: Date / Opnonet / Competition / Goals Home / Goals Away) into the form of Table 1 so that on every players record all played matches are shown. Beside each match I can put the number of goals scored by that particular player in that particular match. I also have a field showing the total number of goals scored by the particular player in all matches played. Like I said I want to be able to see the total number of goals into goals scored in every competition.

              My thoughts: if I make a table Participation where there's a record for each player/game combination I get 25 (players) times 60 games = 1,500 reords with data which is already available in the other two db's. Besides that: I want to be able to see how many goals a player has scored in my Players db on all 25 player records. So my thoughts are I don't have to reconstruct my db but I have to add a table because the suggested table Participation won't show me how many goals a player has scored in one record. But on the other hand I might be able to use the information from the Participation tabel to include it into the Players table. Only thing that is bothering me is that I have to put the scores from each game in both the Games Tabel and (multiple times) into the Participation table. 

              • 4. Re: Problem with if / sum
                DavidJondreau

                Yes, you'll have 1,500 records. But you don't need the same data stored in the other dbs. Namely, the goal data for players, which is stored in repeating fields and not usuable in any real sense, will be stored only in Participation table.

                You can see how many goals a player has using a calculation in the Player table that Sum()s the data in the related Participation table, or in the Participation table by using a Summary field and a sorted layout.

                I don't know enough about the rules of scoring soccer, but you may have to enter the finished score in the Games table and the individual goal count in the Participation table. I suspect the goals attributed to all the Players on a Team don't always add up to the final score ( who gets credit when a player scores on his own goals, what if you can't tell who scored, etc ). If they did, you'd only need to enter in the Participation and then add up the related records.

                I know most of this may be over your head, but it's the only way you're going to get where you want to go.

                • 5. Re: Problem with if / sum
                  mgores

                  The game score should only be entered once, into the record for that game in the Game table.  The participation table would just contain the playerID, gameID, #goals (for that player) and a summary total.  That way when you look at a game, you can have a portal showing which players participated with how many goals they scored.  Or you can look at a player record, see what games they played in with whatever game info you want to include and how many goals they scored.  The summary field can be placed in a separate portal next to or under the portal showing the games to display the total goals over all of the games.

                  • 6. Re: Problem with if / sum
                    MEh

                    Is the following a solution:

                    In Tabel 2 Games I add the following fields:

                    -Scoring player name A
                    -Scoring player name B
                    -Scoring player name C

                    etc. 

                    -total goals scoring player A
                    -total goals scoring player B
                    -total goals scoring player C

                    etc.

                    So in each game record I can add names of the players that scoredand the number of goals they scored in that particular match.

                    Now I can add the following fields to Tabel 1 Players:

                    -total number of goals cored in a friendly match
                    -total number of goals cored in a national match
                    -total number of goals cored in a international match 

                    Definiton:

                    -total number of goals cored in a friendly matci: search for all records containing Competitoon="Friendly" and sum up all values from the field "total goals player A".

                    So I came to this: Filter ( Games::Competition="Friendly" ; Sum (Games::Goals Player A))

                    But (again) I'm doing something wrong here because the totalnumber is incorrect.

                    My questions:

                    1)is this a way to get what I want
                    2)if so, what is wrong with  Filter ( Games::Competition="Friendly" ; Sum (Games::Goals Player A)) 

                    Edit: I just realized Filter ( Games::Competition="Friendly" ; Sum (Games::Goals Player A)) is also not correct because it should not be the sum of the goals scored by Player A but the sum of the goals scored by the player the record is related to. On the record of Player B the total number of goals of Player B should be displayed, On the record of Player C the total number of goals of Player C should be displayed. So it should be Filter ( Games::Competition="Friendly" ; Sum (Games::Goals Player "variable")) . No idea how to do that.

                    Anyone can help me out?

                     

                    Thanks very much.

                    • 7. Re: Problem with if / sum
                      mgores

                      If you place the goals scored and total goals in the join table you won't need the extra fileds in the Player or Game tables.  A portal to the join table on a Game table layout can list each of the players that participated in that game, the goals each scored (the total goals would be stored as the final score in the game table).  On the player layout a portal to the join table can list every game he played in, the goals scored in each game and the total.

                      The purpose of the join table is to only have to have that data entered in one place and give the versatility to sort and display that data in many different ways.

                      • 8. Re: Problem with if / sum
                        MEh

                        I'm currently creating the Participation table. I've got a question about the "1,500 records": in my opnion there's no need to create a record for every single player / every single match. Only records for every match / every player that scored a goal in that particular match will do, won't it?

                        • 9. Re: Problem with if / sum
                          mgores

                          If you only want to see the scoring players for each match and only the matches that player scored in.  The 1500 records will not take up much room as they will only contain a few number fields and that table will not need to be viewed directly, only viewed through portals that display only the related records.

                          • 10. Re: Problem with if / sum
                            MEh

                            Thx.

                            It's not because of the space but because of the work / time that creating 1,500 records will take.

                            • 11. Re: Problem with if / sum
                              DavidJondreau

                              You shouldn't be creating these records individually. You should write a script that will create them. Creating 20 records by script takes about as much time as creating 1,500.

                              • 12. Re: Problem with if / sum
                                MEh

                                Ok, I designed the participation table as follows:

                                Field names: Game ID, Player ID, Number of goals

                                The Player ID gets the name of the player form the Players Table; the Game ID gets the Date, Competition, Home / Away, Oponent, Score from the Games Table.

                                Now I want to add a new field to the players table called Total number of goals scored in friendly matches (Friendly is a record of the Competionfield).

                                When I define the Total number of goals scored in friendly matches I have to filter the records from the Participation Table because I only need the records concerning friendly matches. But because the field Competion is not a field from the Participation Tabel but from the Games Tabel I cannot use this field in a definition of a Participation Table field, now can I?

                                My problem is that I don't know how to define the Total number of goals scored in friendly matches also because I don't know how to define a variable in the definition. By variable I mean that the number of goals from the player that the particular record (in the Players table) is about has to bee drawn from the corresponding player from the Participation Table.

                                Any tips?

                                I know I am asking a lot but besides the questions I'm exploring and learning how to use Filemaker. So it isn't I am depending entirely on answers given here. I just need an answer every now and then when I'm stuck exploring.

                                Any help is realy appreciated.

                                • 13. Re: Problem with if / sum
                                  mgores

                                  But you can filter the portal to the participation table with Games::Competition = "Friendly"  which would show the games that player played in where the match type is "Friendly".  If you set up another single row portal with the same relationship showing the field Partiicipation::total goals (a summary defined to be total of Number of goals), that will show the total number of goals the player scored in friendly matches.

                                  Now you could place a global field on the layout to indicate the type of match and replace the "friendly" with gMatchtype.  Then you could change that field and have the portals update (with a little refresh script) for the different match types.

                                  • 14. Re: Problem with if / sum
                                    MEh

                                    Thx, I understand. I created the first portal but I'm having problems creating the secons one (the one with the total number of goals. Additional question: do I need to create the first portal to be able to use the second? I'm asking because the total number of goals will be sufficient. I can see in what games thegoasl are scored in another portal sshowing all matches I created previously.

                                    1 2 Previous Next