3 Replies Latest reply on Nov 1, 2016 12:34 PM by jbrown

    Multi-Series Google Bar Charts


      Hi, I can't seem to get filemaker pro to plot google charts the way I want them in web viewer. 

      I want the graph to look something like this: bar-chart1.png
      ...Seems it would be pretty basic at first but I can't get this particular multi-series chart to work. It needs to plot ALL the records and MORE THAN ONE series.
      I've created one table called 'MyTable' with 3 fields 'CarName', 'CarSpeed' and 'CarWeight'. I've populated the table with 5 records.
      CarName on the Y-Axis, and against this, CarSpeed and CarWeight are plotted on the X-Axis as 2 series. i.e. series one is 'CarName' vs 'CarSpeed', series 2 is 'CarName' vs 'CarWeight'


      I've managed to successfully create 2 graphs, first one plots the series ok but only works for one record. The second, in which I've used an ExecuteSQL command to collect the table data from FMP, shows all the records but for only one field on the X-Axis. Any help appreciated. I'm running filemaker pro 14, and I've attached the fmp file, as well as the javascript code from the web viewer of each graph.

      Any help greatly appreciated

        • 1. Re: Multi-Series Google Bar Charts



          I think the problem is coming about as as result of the formatting of the data in the executeSQL statement. For one, you're missing the CarWEight in your SELECT clause. 2nd, the numbers doesn't need the single quotes around them (quotes around numbers arent used in the api documentation), and that is happening because you need single quotes around the car name. The ExecuteSQL statement is trying to collect and format the data all at once, and that gets messy.


          I propose a different solution: Use a script to collect and format the data, then plug it into the correct spot in the JS to create the chart.

          see the attached file. I created a new layout and a new web viewer that contains how I do this kind of work.


          Basically I'm collecting the data from the table, formatting it correctly for the api and then placing it in a field "Data". I then substitute that into an HTML Calc field.

          There's many advantages to this method:

          1. You don't have to deal with escaping quotes needed for the quotation marks.

          2. You can store the data, which makes the web viewer faster to render.


          Personally I'd never use ExecuteSQl inside a web viewer. You cannot control when it runs, and the running of ExecuteSQL could take awhile depending on how many records it has to go to, thus the user would be stuck not knowing what is happening.  And, as I said above, ExecuteSQL is good at collecting the data, not so good at formatting it correctly.

          The revised web viewer shows the data much faster. Of course you do have to actually gather the data from a  script, but that isn't a problem if the data doesn't change too often.


          Check out my revision of it. It takes a  bit more to set up but it, i think, is more efficient.

          1 of 1 people found this helpful
          • 2. Re: Multi-Series Google Bar Charts

            Jeremy, you my friend are a legend of the digital world. Its all working beautifully over here now, thanks for the tips and lengthy reply and awesome technique with the script to collect all the data, love it. I'm a newbie so this stuff is new to me and the example is gold dust, its to have a simple working solution that I can understand the logic behind and be able to pull apart and replace with new fields. I've added a load of additional series to the graph and its working great. Syonara Filameker native charts, never liked them anyway! I've added a button beside the graph to run the script and update the graph. Thanks again jeremy you've saved my bacon

            • 3. Re: Multi-Series Google Bar Charts

              You're welcome. I'm glad to help. The Web Viewer is a great tool.

              I've done some blog posts about it as well as have some colleagues. I am currently working on a library of integrations that can be used by anyone using the same principle i showed you (my library further breaks down the CSS/ JS into fields and such).

              Blog | FileMaker, Salesforce, Web | Soliant Consulting

              Check out our blog for more ideas. Search for the keywords Javascript, HTML. My colleague logicurio and Ross Johnson have some other great ideas. And stay tuned to the blog post(s) and videos that show off an entire library of integrations.

              2 of 2 people found this helpful