Experiences of a FileMaker Pro Database Developer

Charting

FileMaker Pro version 11 has new charting options. These are nicely integrated directly in the layout options. For simple charts, FileMaker’s built-in options are a great choice.

I needed something a bit more powerful and interactive. My solution was to use Highcharts, interactive JavaScript charts. Here’s an example of a chart created from one of my FileMaker databases using Highcharts: Highchart Example.

By clicking the legend, you can turn the graphs on and off. You can also drag and select to zoom in on specific areas of the chart.

In order to integrate with FileMaker,  use the substitute function and building pieces of the chart using calculations formatted as html snippets. Next, build a master chart with variables that when processed with the substitute function, the variables are replaced with the html snippets. Click here for an example of the master chart that I used. You will need to view the source in order to see the html.

I dump the master chart into a global text field and then process that field with the substitute function. Use a separate substitute for each variable.

Make sure to check out the demo gallery to see all the charting options available.

Post to Twitter

Comments

  1. Hi, I had also put together a solution with HighChart+FMP running WinXP with approach to the one you described. I put the html+js files in a global field, and in insert data series by running calculation in webviewer with something like “data:text/html,”& (all the substitution work..)

    However, have you run into the problem that the memory taken up by FMP in task manager stacking up and increases as browsing through each records when the chart for each record is generated? Eventually the chart plots really slow until I go into layout editing mode that the memory usage will drop back to normal. I did notice the generated page are stored in a temp directory. I’m suspecting the memory usage by FMP gets stack up is because does not flush the generated page from its memory. I’m wondering if you have run into this problem, and if so, how did you fix it.

    thank you!

    • Hi Kevin, I have not experienced this problem specifically. I am not generating the HTML in a web viewer. I’m actually exporting a calculated HTML file and opening it directly in a web browser. This client I develop the solution for wanted to deliver the HTML files as reports to their client. I was also having trouble rendering the charts directly in web viewer. It worked fine on my Mac, but not on the client’s PC. The primary problem I believe was due to security settings in IE. By the way, the charts render and look much better in Firefox. Due to my time constraints with implementing the charts, I did not trouble shoot the web viewer issue completely. I will try again.

      What version of FileMaker are you using? I’m using FileMaker 11 and my files are hosting on FileMaker Server 11. Are your files hosted or local? Let me know and I will test your specific problem and will do a search if there are any known memory “leaks” using Web Viewer.

  2. Hi Matt:

    Thank you for your response.

    I’m using FMPA 11 without server. I notice the memory will flush whenever the layout is switched. The chart renders fine for me under PC and IE. What version of IE is your client runnning? My PC is currently running IE 7, using the latest version of Hightcharts.

    Thank you for your help!

    • Hi Kevin, I did some testing today and I see what you are referring to. I also tested the web viewer using the built-in Google maps option and noticed the same pattern of memory usage (actually a bit more with each record load than with HighCharts).
      I believe FileMaker does not have much control over the content in the web viewer. It’s almost like a browser within a FileMaker layout. My guess is that the memory usage is similar to running both applications within one. I’m just guessing here though.
      The best work-around that I could come up with is to navigate to a layout that does not have a web viewer on it. I just went to my main menu (a layout with buttons only) and noticed the memory usage dropped back to normal. Then I went to the next record and then to the web viewer layout. This could easily be script and you could use a blank layout kind of like your ram “flush.” I don’t believe you would even notice navigating to the next record. Do something like this and attach to a “forward” button:
      —-
      Go to Layout (Blank)
      Go to Record (Next)
      Go to Layout (Original)
      —-
      Create another similar script that Goes to Record (Previous) and attach it to a “back” button.
      Let me know if this helps!

  3. Hi Matt:

    THAT’S CLEAVER! When I tried to solve the problem, I’ve been looking for a script step to “flush” sort of like refresh window step but couldn’t find one. All this time I had discovered it will flush when a layout is switched, but never hit me “that is the solution!”

    I used your idea and did this script:

    Go to Layout(blank)
    Go to Layout (original)
    Halt Script // to avoid infinite loop

    to “Record Exit” script trigger.

    Works great! and you’re right, it is barely noticeable. GJ!

  4. Sorry Matt:
    That last script I wrote didn’t work.
    I’m used to browse through record using mouse scroll wheel w/o using any button.
    That’s why I was thinking how to fix this w/o using a button.
    Here is what I came up and worked:

    Script Trigger: RecordLoad

    If( $$flush = 1)
    Set Variable ($$flush = 0)
    Halt Script
    Else
    Go to layout (Blank)
    Set Variable ($$flush = 1)
    Go to layout (Original)
    End If

    • Glad you got it going, Kevin. Smart idea about using Layout Script Triggers and no need to add additional navigation elements. Sometimes I forget about the newer features, like triggers. Old habits are hard to break…

      Kind of odd how OnRecordLoad is always invoked during a layout change, even though the record may not change. I guess the record has to load “different” data depending on what’s on the layout, thus forcing OnRecordLoad to run each time the layout changes even if the table occurrence does not change.

      To my knowledge there is no script step to allow you to do this. FYI, the option under Refresh Window to flush cached join results or cached SQL data only forces the cached data to be reloaded. If your script does not change this cached data, flushing it will only slow down performance. The slowdown is really felt if you are connected to an ODBC data source and using SQL or your data is shared over the network like in a FileMaker Server hosted solution.

      Good luck with your projects!

Leave a Reply to Matt Cancel reply

*