Google Charts

    1. Google charts are fast, free, and easy to connect to QuickBase -- with a little help!

      QuickBase upgraded its charts last year, and added some nice features like being able to change the size of a chart, and save it as a jpg. But we still get a lot of charting requests that QuickBase can't fill. In these cases, our "go to" option is Google charts.

      The Google Charting Engine is fast, free, and fairly easy to use. But a fair word of warning: it does require coding (we use javascript) so it's not as easy as QuickBase. You'll need to know a little javascript to follow the example in this article. But I'll give you some sample code, and I think you'll be excited about what you'll be able to do.
      And of course, if you would like us to set it up for you, give us a yell at at info@DataCollaborative.com.
      Start by looking at Google's gallery at developers.google.com/chart/interactive/docs/gallery.
      Pretty cool, eh?
      Chart types like the speedometer and the bubble chart are completely different from anything in QuickBase.

       In virtually every application, the user interface is the most important part. If you can convey to users the information they need to know in a way that they understand immediately, you're halfway to your goal. So having a large array of visual tools is extremely important for all developers.

      How to get these charts in QuickBase?
      I'll show you how to create the gauges in this application, which is a very simple db in which the user enters students' test scores, and the gauges (aka speedometers) show how well the students are doing. Once you create a page with these charts, you can include it on a dashboard with the "web page" widget for dashboards.

      The code for the gauges looks complicated but you can copy what I did for your own application. The code goes on a QuickBase "Code Page". To create one, go to your application's home page, click on Settings (if you do not see that, then you do not have permission to work in Settings - you can request permission from your application administrator), Pages, then New Page, and then Code Page. Name your page anything you want, but with no spaces, and .html at the end.

      For the code, you can copy the code in yellow at the end of the article exactly as it is -- except remember to put it your own AppToken!
      The code in green (below the yellow) grabs the data and averages the test scores. You will probably need to write your own code here, because it will vary depending on what you are extracting from your tables: averages, totals, count of records, etc.
      Regardless, you will need to include your dbid, and probably do a "DoQuery" statement like the one I included. My DoQuery is completely generic. If you want to filter records, put a query string in the second argument (click here for details.) The third argument allows you to select fields (in a period-delimited list like this: "4.6.9.12") and the 4th argument allows to sort the records, also in a period-delimited list.
      I always check for errors after doing any I/O, because if you don't, the script quietly fails and the user has no idea what is happening. Likewise, you need to check if any records have returned (the length>0 check) because otherwise there is a silent failure. Once we have gone through all of that, we just add up the scores and take the average.
      The code in blue (after the green) just defines what the gauges look like - how big they are, how they are labelled, etc.
      There you have it - for just a few lines of code, and you'll end up with a very helpful integration with Google charts.



      <html>
      <head>
      <script type='text/javascript' src='https://www.google.com/jsapi'></script>
      <script language="JavaScript1.2" src="/js/QuickBaseClient.js"></script>
      <script type='text/javascript'>
      var qdb=new QuickBaseClient();
         google.load('visualization', '1', {packages:['gauge']});
      google.setOnLoadCallback(drawChart);

      function drawChart()
      {
      //setting tokens - if your application uses an app token, enter it here
      qdb.SetAppToken("")
      // Keep everything above this line exactly the way it is, except enter your app token if you are using app tokens.

      // Below this line is where I get the data
      // code for querying
      var DBID="bitw2uya8";// this is the DBID of the table you are querying
      var xDom=qdb.DoQuery(DBID,"","a","3","")// this queries your records: all fields, all records
      if(qdb.displayErrorAlert("Could not query because: ")) return;// check for errors
      if (qdb.selectNodes(xDom,"*/table/records/record").length >0 )// we check to see if any records came back
      var mathTotal=0;
      var languageTotal=0;
      var recs=qdb.selectNodes(xDom,"*/table/records/record")
      for (var i=0;i<recs.length;i++)// if they did, we cycle through all of them
      {
      var rec=recs[i]
      mathTotal=mathTotal + getFieldValue(rec,7);
      // and here we use a little function I wrote to get the value of the math grade, based on the field id (7 in this case)
      languageTotal=languageTotal+getFieldValue(rec,8);
      }

      // Here is the code to define what the gauge looks like
             var data = google.visualization.arrayToDataTable([
               ['Label', 'Value'],
               ['Math', mathTotal/recs.length],
               ['Language', languageTotal/recs.length],
             ]);

             var options = {
               width: 600, height: 180,
               redFrom: 0, redTo: 60,
               yellowFrom:60, yellowTo: 70,
               greenFrom:70,greenTo:100,
               minorTicks: 5
             };

      // don't change anything under this line
             var chart = new google.visualization.Gauge(document.getElementById('chart_div'));
             chart.draw(data, options);
           }

      function getFieldValue (iDom,fid)
      {
      var retval=qdb.text(qdb.selectSingleNode(iDom,"f[@id=" + fid + "]"));
      return parseInt(retval);
      }
         </script>

      </head>
      <body>
         <div id='chart_div'></div>
      </body>
      </html>

       

  • Leave a Reply

    CAPTCHA
    Type the characters you see in the picture. Not case sensitive.
    Image CAPTCHA

    Enter the characters shown in the image.