1 2 Previous Next 20 Replies Latest reply on Sep 2, 2015 11:11 AM by jeff@jeffmillard.com

    Defeated by FileMaker charts


      As a scientist, charts are a tool that I use most days to visualise data. When FileMaker introduced native charting in version 11 back in 2010, the idea of being able to use FileMaker to automatically and dynamically prepare charts using filters and relationships for various experimental treatments was profound. And in the very, very, very simplest of charts this can work well. Unfortunately, FileMaker charts have changed little since version 11, and the options for chart types and control are from my perspective quite limited. A recent experience led me to use alternative charting approaches because FileMaker was not capable of consistently producing the charts I needed.


      I required a simple line chart whereby 2 Y data series of continuous data were plotted against 1 X data series, with separate Y axes for each Y data series. In addition I required a vertical line that identified the maximum value of one of the Y series of data. This line would rise from the value on the X axis that was the calculated maximum to the peak of one of the Y series. See required chart below.




      Fortunately in my case the X series was simply a list of equally spaced numbers from 0-100 in increments of 1. If my X series was not in this format, that is the list of numbers were not equally spaced (eg. 0, 5, 7, 10, 23, 70, 100), FileMaker will place these values as though they are equally spaced categories, or discrete data when a line chart is chosen. In this case, why not use a scatter plot instead? OK, I could do that, but then FileMaker does not provide the option to join the points with splines or polylines to end up with a smooth curve, even though I could have the points on the scatter chart turned off.


      Fortunately the data I was creating was equally spaced in increments of 1, and I could use a line chart to represent my data. However, the 2 Y series I was charting were on different scales, and the scale for the second or right hand Y axis changed dynamically depending on the data I was filtering. My left hand Y axis always ranged from 0-100, but the right hand Y axis could range from -2000 to +2000. FileMaker includes the same scale on the left and right hand Y axes, and clearly without being able to independently scale the data for each series to a specific Y axis scale would make my data visualisation somewhat pointless. Even if this was possible natively in FileMaker, I also wanted to increase the thickness of my lines from the default 1 pt, to 2 pts. Again, not an option in FileMaker. I also wanted to set the colours of the lines for each Y series to specific colours, however I can only choose a single colour, or use one of the specified colour schemes.


      With these limitations, I set about to prepare a number of chart and other objects and overlay them to provide me with the chart I needed. Or so I thought. I prepared 7 objects. I prepared a line chart with a single colour for X and Y series 1, and another line chart with a different single colour for X and Y series 2. For each of these charts the background was transparent, as were the text labels, and text labels were set at 2pt. No X and Y titles were included for these 2 charts. I prepared another scatter chart with a white background that included no Y data, but used the same X data as the 2 line charts previously mentioned. I can still scale the Y axis without and Y data. This scatter chart provided the axis titles and scales for my Y series 1 data, but retains an annoying right hand Y axis that I don't need. FileMaker always includes both the left and right hand Y axes, and these can't be turned off independently. So a white rectangle in the appropriate stacking order was used to obscure the right hand Y axis of this scatter plot. The line chart for Y series 2 data was duplicated and then reduced in size horizontally so that only the Y axis scale(s) could be seen. It can be further resized from the right hand side handles, but not the left, so that you can only see the left hand scale. This is not ideal as the labels are left aligned rather than right aligned. This object was placed over the white rectangle, and the position adjusted to give an approximate right hand Y axis for Y series 2. A text object was created for Y series 2 axis title and rotated into position, and placed in the stacking order. Now for that pesky vertical line that identified the maximum value in one of the Y series data sets. Firstly I tried a line chart with 2 data pairs that used the same X value and Y values of 0 or 1, with appropriate scaling on the X axis. But the line chart doesn't deal with X data as continuous data, and so you can't produce a vertical line using a line chart. Then I tried a bar chart, using the same X series as the first 2 line charts, and with all the data set at 0, except where the maximum occurred a value of 1 was assigned. As long as the chart was narrow enough a vertical line resulted. All axis labels set to transparent on the bar chart overlay.


      Now with all the objects assembled, spend a few hours getting all the objects lined up - yes, a few hours. All of these charts include a bounding box that cannot be turned off. You would think if all these charts had the same X axis range irrespective of the dynamic Y values, you could simply resize the objects so that the bounding boxes aligned and the extent of the chart in layout mode matched and voila. Not that simple. Some charts had to be wider than others for the chart overlays to render correctly. And just when you had it set up perfect for one set of data, you try a different set of data and the bounding boxes shift so that there are 2 or more vertical lines for the left or right hand Y axis offset by around 2 to 5 or more pixels. Even for identical data retrieved from related sources or from the current record delimited data the bounding box would shift differently. It looked, well, awful. If the bounding box could be turned off, the overlayed chart objects would have "approximately" represented the final chart that I wanted. See FIleMaker rendition of my chart below. For this dataset the 2 Y series have been offset from each other and multiple vertical lines appear for the left and right Y axes even though testing on previous data showed them lined up.




      So, I gave up on native FileMaker charts to achieve what I needed, and had to learn something completely new, that is d3, javascript and SVG. I don't have experience with javascript, or SVG, but looking closely at online examples and Matt Petrowsky's FileMaker magazine examples, and with help from an online d3 group I finally got the chart I needed implemented in a web viewer. Future charts implemented like this will not be easy as I still know little about javascript and SVG, but the result will likely be consistent, and customisable to my needs. The d3 result is the first image posted above as the example of what I needed.


      Recent versions of FileMaker have allowed design changes at a more atomic level with dynamic styling of layout objects, including the use of SVG buttons in v14. And whilst charts have been available for more than 5 years, there has been little change in this part of the product. Don't get me wrong, I love FileMaker, the data constructs, ease of use and modification, multi-user support are perfect for me as a scientist. Please FileMaker, implement atomic level native charting in FileMaker, or rebuild the charting components so that they operate with the same sentiment as the rest of the product. And look at some of the d3 examples, and scientific charting programs (Aabel) to get some inspiration for the rebuild.

        • 2. Re: Defeated by FileMaker charts

          I explored a range of charting options, including Google Charts. At the end of the day, the solution being developed in my case needed to work offline, for farmers in areas where internet connectivity is poor or not available. Secondly the solution was being prepared for FM on iOS and the data had to be made available from within the FM solution, not external to it. Having explored a number of javascript examples, I was able to structure my FM data using calculations to satisfy the needs of d3 and create a completely offline standalone solution. Google Charts as far as I am aware requires an internet connection. Thanks for your reply.

          • 3. Re: Defeated by FileMaker charts

            yup. Google needs internet. I wish I could help you more with this one... one would hope that filemaker might do something about improving charts...

            • 4. Re: Defeated by FileMaker charts

              I have been playing with d3 too, is there  chance you could share your final solution to help all of our learning?

              • 5. Re: Defeated by FileMaker charts

                I will pull out the d3 related bits to share over the next day or so.

                • 6. Re: Defeated by FileMaker charts

                  I'm a big fan of the Hightcharts javascript charting library. Works on or offline depending on how you implement it.

                  • 7. Re: Defeated by FileMaker charts

                    Filemaker charting = http://www.x2max.com/home/

                    • 8. Re: Defeated by FileMaker charts

                      Agree with the original post.

                      There are charting (graphing) applications and there are smarter charting (graphing) applications.


                      For the record, I thought the charting in Microsoft Excel was awesome too, until I tried to chart similar (x, y ) coordinates like the example above. Similar problems to FileMaker Pro occur.



                      Michael Richards

                      Brisbane (Australia)

                      • 9. Re: Defeated by FileMaker charts

                        Here is a link to an unlocked FM14 file that contains 2 d3 examples. One is the line charts described above, and the other a d3 gauge. Try the buttons on the portal to see each. For those unfamiliar with how this works there are some text objects on the layout that contain various bits and pieces of d3/javascript code. One of those is loaded into a global variable on startup using GetLayoutObjectAttribute ("textobjectname"; "content"). The other text objects are accessed as required using the same GetLayoutObjectAttribute function either by a calculation in the web viewer, or by the Set Web Viewer script step.


                        There is also a custom function that concatenates values from 3 lists into a format required by d3. The result of this function is placed into the relevant d3 code by using the substitute function, as is the global variable that is loaded at startup. This custom function could be made more portable by including function parameters representing the required "prefix" text for the data.


                        Note that there is an invisible button on the layout placed over the leftmost button that runs a script and subsequently displays the relevant line graph for the chosen portal row. Normally web viewer calculations do not retain their context in portal rows, however, in this case the web viewer is referencing global fields on the UI layout using the custom function, and this works fine (mostly). It seems to fail after you have clicked through more than one of the portal records and go back to the record you commenced on without closing the popover. This can be corrected by making the popover modal and only dismissed using a button.


                        Please let me know if I should be sharing this file somehow on the FileMaker Community rather than via DropBox as I couldn't see how to share the file and directly on this discussion.


                        File now attached



                        1 of 1 people found this helpful
                        • 10. Re: Defeated by FileMaker charts

                          weaverd wrote:

                          I couldn't see how to share the file and directly on this discussion.


                          Use the “Advanced Editor” which sports an “Attach” feature/link.


                          btw, here's a simple trick that turns a highly informative, yet long, solid and unwieldy block of text into something much easier consumable by eye & mind: place the cursor at a place where a new idea/thought/topic starts, and press the return key two times. Repeat until readable.


                          There. Isn't that neat?


                          And of course thanks for the sample.

                          • 11. Re: Defeated by FileMaker charts

                            Thanks - I have now attached the file, and made the reply a little more readable. By the way, you probably recognise the custom function

                            • 12. Re: Defeated by FileMaker charts

                              weaverd wrote:


                              By the way, you probably recognise the custom function


                              Who in this day & age would write code that flows so freely, yet knows it bounds so clearly?


                              No idea …


                              (Actually, I was wondering, since from my observations, few people know/use the internal iterator “trick” and employ that particular bracing style.)

                              • 13. Re: Defeated by FileMaker charts

                                Very neat, like the recursive custom function too.

                                Of course clicking popover buttons bottom up somehow produces an empty popover until clicked into.


                                A bug, a popover in a portal issue or something else? ( like only happening on my yosemite 10.10.3 with Fm14 )

                                • 14. Re: Defeated by FileMaker charts

                                  My guess is its a web viewer in a popover/portal issue. You could resolve this by placing the popover outside the portal, and adjusting the scripts accordingly, or perhaps by making the popover modal.


                                  I sometimes get an empty popover if I do not dismiss the popover by either clicking in some blank space on the layout, or clicking on the original popover button again to dismiss the popover.


                                  MacOS 10.9.5 with FM14

                                  1 2 Previous Next