Sample File Here


This is Matt at AppWorks and today’s video is going to be about Google chart API integration with FileMaker and how easy it is to get started on this process.

On this database, which is the FileMaker free template for invoices, we’re going to be taking a look at how I did this. In this database, there are some FileMaker charts that exist, so you can kinda see what you can get from FileMaker charting. I used and created a handful of invoices in here – here’s my sample data. We can see a list of those invoices. Before I get to charting the actual data, let’s take a look at the moving components of what exists. This is what my chart would look like of a simple dashboard that shows you certain numbers. This is about the easiest starting point I can think of – this is the entire html code that exists to draw this chart. As you can see, we have an html header, we have a Javascript that gets called. The area of the actual data is right here – that’s where it sets a label and the actual values. If I wanted to change a value from memory to be 95, I can change that here. The next areas below that are how big to draw the chart. That’s basically all there is.

When I look now, I have memory set all the way to 95. What if I want to chart my own data? I have another layout in this database called Google Chart B, which breaks it into 3 fields. So in this one, I’m going to do a couple things. I’ve also got another record in here that doesn’t show the dashboard gauges, but it does show column or pi, or other types of data. It looks like this. It gets data directly from the database so the only part that took a little bit of work, because this is really just 3 fields that are on the web viewer – lets take a look at the web viewer. It’s basically global top, data, and bottom. Those are just 3 fields in a table. Right here is where those three fields are, so you could just type any data in and it happens to be html code. What makes my chart work is the part in the middle – it gets data from the db. I could manually change it, like Levenger 0. It doesn’t even show. If I put a dollar, its too small to actually see. 

Let’s talk about what it takes to get data from FM to this chart. I have a script here on the invoice layout. I’ll turn script debugger on so we can actually see the code run. What it does is it sets some prefixes for each line because the data has to be formatted a certain way. For each line, let’s take a look at what it’s actually building here in the data element. It builds the data structured in a certain way – it has to have a square bracket, prefix and post, and it also has to have a comma at the end of each line. The name of the company has to have a single quote. 

I’ll go ahead and step through the rest of this. It’s going to basically build that code for all five lines, then at the end, it goes to my chart layout and it populates that field with the data that it grabs from the script, so it’s a very, very simple script and then here’s what the data would be for my chart. Let’s say that I don’t want all five – let’s say I only want four invoices. I’m going to get rid of the XYZ Systems one, so I’ll click on that record and just omit it. So now I only have another set of data and if I chart it, I get only those four bits of data now, as opposed to the five, which is pretty cool, but you want to know what’s really cool? This is really cool – I can change one word in my chart, in the html code, from the word pie to, let’s say, bar. Now when I run my chart, I’ll get the exact same data, but it’ll show in a different way. If I wanted it line, I change this to line, if I want it in some other format, I can just change that one word. Maybe the best one is column. So this is really powerful and really simple. And with that change made, I can go back to my invoice list, maybe show all of my records, and then rerun that code and then I’ll get all five, but shown the other way. 

It’s a very powerful and very simple tool and I hope you have fun playing with the Google Chart API. Have a great day!