Google Chart and FileMaker API – Direct Impact Solutions

Integrating a Google Chart API with a FileMaker Solution

A Google chart API can be integrated easily with FileMaker Pro using just the WebViewer. This simple approach can be accomplished in minutes and has few moving parts.

First, here is the wide array of Google charts available. There are simple charts like bar graphs and pie charts, combination charts that show both line and bar graph data, and even advanced charts that show animation or maps.

This article has a video and a sample file to show the “Hello, World!” program and demonstrate integration of the simplest chart. We start by taking code directly from Google, paste it into a text field in FileMaker, and use just that field in a WebViewer. Just like that, we have a Google chart data that we can update manually!

<html>
  <head>
   <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
   <script type="text/javascript">
      google.charts.load('current', {'packages':['gauge']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['Memory', 80],
          ['CPU', 55],
          ['Network', 68]
        ]);

        var options = {
          width: 400, height: 120,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
          minorTicks: 5
        };

        var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

        chart.draw(data, options);

        setInterval(function() {
          data.setValue(0, 1, 40 + Math.round(60 * Math.random()));
          chart.draw(data, options);
        }, 13000);
        setInterval(function() {
          data.setValue(1, 1, 40 + Math.round(60 * Math.random()));
          chart.draw(data, options);
        }, 5000);
        setInterval(function() {
          data.setValue(2, 1, 60 + Math.round(20 * Math.random()));
          chart.draw(data, options);
        }, 26000);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 400px; height: 120px;"></div>
  </body>
</html>

Notice that there are three code sections we need to know about to integrate with FileMaker. These are the top part that defines the language (HTML) and names the Javascript library on Google.

The second part is the actual chart data. This starts with var data = and you can see the lines that set values for Memory, CPU and Network. That is the part we can populate with data in your FileMaker database.

The last part is where we select which type of chart to draw – in this case that’s the line that includes google.visualization.Gauge

We also see some code that adds a randomizer to animate the chart. This makes it look like gauges in a car, where we can see values change. For our use, we don’t need that code, so it’s easy to remove. The rest of this section has code that defines the size of the chart to draw, and this should match the size of the WebViewer on your FileMaker layout.

To integrate this into FileMaker, we break out these three sections to three fields in a table. We then modify our WebViewer to join these three fields together.

The last step is to write a script that gets whatever data you want to chart in your database. We’ll use it to populate the middle field, called Chart Data in this example.

Note that Google Chart requires that each line needs to be formatted in a specific way, with square brackets, single quotes, and a comma, like this:

[‘ABC Tech’,100],

[‘XYZ Systems’,375],

[‘AppWorks’,227],

The script will loop through the records, setting the value of a script parameter as we go, and then setting the field with the resulting chart data. There are probably many ways to do this, such as with JSON functions, but this way seemed straightforward to me. To make the code easier to write inside of the Loop, I defined script variables with the same punctuation as we used for the square brackets.

This is the code for line 10. I am using the List() function to build each line as we go, which is a very simple way to do this. Then for each line, I use the $pre $mid and $post variables to make it easier to read. The two bits of data pulled from my database are the name of the company and the invoice total.

List ( 
  $data ; 
  $pre & Invoices::Company & $mid &  "," & Invoices::Total & $post 
)

Once that is done, you can call the script and see the chart.

One of my favorite aspects of this is that if you go into this line of code:

var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));

You can change the word ColumnChart to BarChart or PieChart or LineChart and it works. (This is very satisfying to try!)

I hope you have fun using the Google Chart API with your FileMaker database. If you have questions about the content in this article, feel free to contact us!

*This article was originally written for AppWorks, which has since joined Direct Impact Solutions. This article is intended for informative purposes only. To the best of our knowledge, this information is accurate as of the date of publication.