5 Replies Latest reply on Jun 26, 2013 10:33 PM by tomo17

    Create chart based on two sorts

    tomo17

      Title

      Create chart based on two sorts

      Post

           I am trying to create a chart in FM12 to display some information but it has got me stumped.  I am unsure whether i have not put it in the right layout or if i need to put it in a specific layout part.  The database is a sports statistics one.  

           I have two tables that i am trying to create the chart from.  Table 1 is match information/fixtures ie date, home team, away team, location.  Table 2 is one that is used to record stats relevant to each game.  These two tables are linked by a match Id field ie MatchFixture::MatchID=MatchScoring::MAtchID.

           In the match scoring table i have three fields that i am trying to create the chart from: MatchScoring::Club (option of two clubs - home/away), MatchScoring::ScoreOrigin (three options ie free kick, general play, stoppage), MatchScoring::sScore Origin (summary field, running with restart, sorted by ScoreOrigin)

           What i would like to be able to do is choose a match and from a MatchFixture layout see a column chart that has comparison information for each of the clubs in each of the score origins.

           ie Stoppages - Team A=3, Team B = 1

               Free Kick - Team A=1, Team B =2

              General Play - Team A=4, Team B =3

            

           Whilst the list may be long and varied - what am I missing??

            

           Thanks

        • 1. Re: Create chart based on two sorts
          philmodjunk

               You'd need some filtered portals that filter by team and ScoreOrigin and a non running summary field to put in those portals to get your columns of data for a given match record.

               On the other hand, if you based your layout on the scoring table, the correct sorting and use of sub summary layout parts could be set up more simply to produce a report of this format:

               Stoppages -
                    Team A=3,
                     Team B = 1

                   Free Kick -
                       Team A=1,
                        Team B =2

                  General Play -
                        Team A=4,
                        Team B =3

          • 2. Re: Create chart based on two sorts
            tomo17

                 Thanks for the reply Phil.  I have been able to create a report which as you suggested is a more simple exercise however the chart is the alternative that allows for a quick visual that could occur in a small break during a game.  

                 I am not sure I understand your solution using filtered portals - am I right in assuming that there is more than one portal(one for each combination of team and origin type)? I think I understand that part but i am lost how you can then link the chart to grab that information.

                 Thanks for any further assistance you can provide.

                  

            • 3. Re: Create chart based on two sorts
              tomo17

                   I can do this in excel and get a graph like this one - I just dont know whether a.) i can do this in FM and if so b.)how

              • 4. Re: Create chart based on two sorts
                philmodjunk

                     Ok, I had wondered at your reference to "chart" in your first post. My description of using portals would be to create a tabular chart of rows and columns of data, not a Filemaker Chart object.

                     Charts can be set up to chart sub totals of groups of data produced by a specific sort, but they have a limitation not encountered in a summary report: the method only works for a sort order based on a single field and the sort order you need here is from data in several fields.

                     To get the chart to work, you'll need to construct a calculation field that combines the Team ID with the score origin in such a way that sorting on this single field produces the same groups as I showed for the summary report when sorting by both team and score origin.

                • 5. Re: Create chart based on two sorts
                  tomo17

                       Thanks Phil - your solution works a treat!