8 Replies Latest reply on May 30, 2016 10:06 AM by katier08

    google charts value list

    katier08

      Hello everyone, I would like to insert a value list in my pie chart, how can I do?
      I am attaching the code, thanks for helping me

       

      var data = google.visualization.arrayToDataTable([

                ['Task', 'Hours per Day'],

                ['Work',     11],

                ['Eat',      2],

                ['Commute',  2],

                ['Watch TV', 2],

                ['"& recipes::nation & "', " & recipes::${0000R} & " ]

              ]);

       

      I wish I was in the graph the value list of nations, Italy, Japan, U.K., however the figure only shows one nation at a time, but I would like all those in the value list (nat)  that contains all the nations

      in this instance I should say japan 1, Italy 1,mexico 1

      Instead he tells me italy 3, because there are only three records, they shall be divided by country

       

      I am attaching two photos, google chart 1, 2 fm chart, I wish that the google chart becomes like that of fm as in the picture

      google charts

       

      Screenshot_36.jpg

       

      fm chart

      Screenshot_21.jpg

        • 1. Re: google charts value list
          erolst

          Assuming you have a table with fields for country and value, you could try

           

          Let ( [

            begin = "var data = google.visualization.arrayToDataTable([

                    ¶['Country', 'Sum'],¶['" ;

            end = "],¶]);" ;

            dataSection =

              ExecuteSQL ( "

                SELECT country , SUM ( theValue ) /* note that 'value' is a reserved word in SQL */

                FROM DataTable

                GROUP BY country

              " ; "', " ; "]¶, ['"

              )

            ] ;

            begin & dataSection & end

          )

           

          which would return e.g. this:

           

          var data = google.visualization.arrayToDataTable([          

          ['Country', 'Sum'],

          ['France', 15]

          , ['Germany', 22]

          , ['Italy', 12],

          ]);

          • 2. Re: google charts value list
            katier08

            hello thanks for your answer makes me error in the table, I write the fields

            recipes::nation= text

            recipes::${0000R}=summary by name

            nat=value list

            thank you

            Screenshot_23.jpg

            • 3. Re: google charts value list
              erolst

              1. Did you copy the calculation from the post, or type it in by hand? If I copy it from here, I don't get an error message (not the least because I copied a working version and pasted it here …)

               

              2. You need to adapt the calculation to your field and table names.

               

              Also, if you already have summarized values, then there is no need to summarize them again. OTOH, if the field you showed is a summary field, then that is no good for ExecuteSQL(), because it depends on the found set … so you should use the field that that field summarizes.

               

              Anyway, for your field names, try

               

              Let ( [

                begin = "var data = google.visualization.arrayToDataTable([

                        ¶['Nation', 'Summary'],¶['" ;

                end = "],¶]);" ;

                dataSection =

                  ExecuteSQL ( "

                    SELECT nation, \"${0000R}\" \* use the field that is being summarized here instead */

                    FROM recipe

                    GROUP BY nation

                  " ; "', " ; "]¶, ['"

                  )

                ] ;

                begin & dataSection & end

              )

               

              btw, notice the {} around one of your field names? That's because it starts with a digit, which is a no-no for object names in any programming language. FileMaker is so accommodating to allow it anyway (then added those extra characters), but you should have received a warning when you created that field. Heed those warnings! (and rename your field).

              • 4. Re: google charts value list
                katier08

                sorry but I copied the code in the web viewer, right?
                but var data I enter a field ??? because I always fail as in the picture

                Screenshot_41.png

                used FM13

                "data:text/html;charset=UTF-8,

                <html>

                  <head>

                    <script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>

                    <script type=\"text/javascript\">

                      google.charts.load('current', {'packages':['corechart']});

                      google.charts.setOnLoadCallback(drawChart);

                      function drawChart() {

                 

                        var data = google.visualization.arrayToDataTable([

                          ['Task', 'Hours per Day'],

                          ['Work',     11],

                          ['Eat',      2],

                          ['Commute',  2],

                          ['Watch TV', 2],

                          ['"& recipes::nation & "', " & recipes::${0000R} & " ]  

                        ]);

                 

                        var options = {

                          title: 'My Daily Activities'

                        };

                 

                        var chart = new google.visualization.PieChart(document.getElementById('piechart'));

                 

                        chart.draw(data, options);

                      }

                    </script>

                  </head>

                  <body>

                    <div id=\"piechart\" style=\"width: 900px; height: 500px;\"></div>

                  </body>

                </html>"

                 

                if I can not, I can attach a file?

                thank you <3

                • 5. Re: google charts value list
                  erolst

                  Well, you can't just slap the calculation in like that …

                   

                  This works for me (see below for calc):

                   

                  Screen Shot 2016-05-29 at 21.07.44.png

                   

                  "data:text/html;charset=UTF-8,

                  <html>

                    <head>

                      <script type=\"text/javascript\" src=\"https://www.gstatic.com/charts/loader.js\"></script>

                      <script type=\"text/javascript\">

                        google.charts.load('current', {'packages':['corechart']});

                        google.charts.setOnLoadCallback(drawChart);

                        function drawChart() {

                   

                          var data = google.visualization.arrayToDataTable([

                            ['Country', 'Sum'], ['"

                   

                  &

                   

                      ExecuteSQL ( "

                        SELECT country, Sum ( theValue )

                        FROM DataTable

                        GROUP BY country

                      " ; "', " ; "]¶, ['"

                      )

                   

                  &

                   

                  " ] 

                          ]);

                          var options = {

                            title: 'European Countries'

                          };

                          var chart = new google.visualization.PieChart(document.getElementById('piechart'));

                          chart.draw(data, options);

                        }

                      </script>

                    </head>

                    <body>

                      <div id= 'piechart' style='width: 350px; height: 250px;'></div>

                    </body>

                  </html>"

                  • 6. Re: google charts value list
                    katier08

                    thanks I tried but is blank page will send you my file with sample records, google chart, fm chart, value list, and all fields

                    chart1.fmp12 - Google Drive

                    I want the records should be by country, because the fm chart display them, but maybe that google does not work the same way I think.

                    thank you

                    Screenshot_43.png

                    • 7. Re: google charts value list
                      erolst

                      katier08 wrote:

                       

                      thanks I tried but is blank page

                      If you're asking for assistance, you should heed the advice you get (or at least try it out).

                       

                      You simply copied the exact calculation from my sample, not even the one I modified for you – that cannot work, since you have different names – and I said that you need to adapt these names.

                       

                      Find attached a working version of your file – but next time, please do your homework.

                       

                      katier08 wrote:

                      Screenshot_43.png

                       

                      So that is how a blank Web Viewer looks –  I always wondered …

                      • 8. Re: google charts value list
                        katier08

                        works thanks a lot ^ _ ^, I owe you a drink, but I can show numbers instead of%?, so that it is written to me, 2 italy, japan 3, at least become perfect as I will, and then you understand that for every nation there are the associated records, and then i can use it for other values lists
                        thank you very much <3

                         

                        the chart should show me for every item I post the number of those who are not the% for example,

                        name | nation | category | region |
                        name, italy, pizza, lazio
                        name, italy, pizza, Tuscan
                        name, japan, soups
                        name, USA,
                        name, mexico, seconds
                        name, u.k, first

                        3 on chart
                        NATION = italy 2, japan, 1, USA 1, mexico 1, uk, 1
                        CATEGORY = pizza 2, soups 1, seconds 1, first 1
                        REGION = Lazio 1, Tuscany 1

                         

                        the charts I have to put them in a control slider with 3 points, so that I can see them all 3, but this I know to do it, the only thing I did not understand was how to operate the chart of google

                         

                        if I can do as I want the charts by category and amount of numbers, they are in place and no more disorder XD

                        chartn1.fmp12 - Google Drive

                         

                        Screenshot_49.png