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.
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.