Charting a New Course
By Lorne Walton (walton@bc.sympatico.ca)
BONUS FILE: CHARTING.FP3, SCATTER.FP3
PLATFORM: Macintosh & Windows
In this thrilling episode, we take a quantum leap forward in terms of the glamor factor and look at the bleeding-edge Charts plug-in from Vince Menanno and Waves in Motion. If you have not already downloaded and installed this plug-in, the accompanying bonus file will give you a rough idea of what you're missing. To really appreciate the speed and elegance of this extended functionality for FileMaker 4.x, download the plug-in from http://www.wmotion.com/ (we would have included the plug-in with the magazine, but the download is a couple of megabytes). The authors offer a unique and generous shareware trial: from the time you start up FileMaker, you have an hour to use their plug-in. After that hour, you must quit FileMaker and restart.
One of the most-requested major features for FileMaker has been the ability to chart data. There have been various workarounds involving repeating fields, complex layout redesigns, and slow screen redraws. The best FileMaker-only charting solution I had seen prior to trying this new plug-in was painfully laborious, memory-intensive, often slow to execute, inflexible and at times aesthetically unappealing. And it was better than the alternatives. Solutions combining FileMaker with such applications as Excel and Claris Impact can do the job, but require the added work of exporting data each time it changes, or else AppleScripting, which leaves out the Windows users.
All that has changed. You can now make your charting fully scripted and user friendly, just like FileMaker itself. Waves In Motion Charts offers these chart types:
Pie
Line
Horizontal Bar
Vertical Bar
Horizontal Stacked Bar
Vertical Stacked Bar
Scatter
High-Low
The Bonus Files
The file Charting.FP3 contains 36 records, as a simulation of a mutual funds portfolio. It tracks monthly values of 3 funds over a 12-month span. This example lends itself well to the following 4 chart types, which are displayed in the file:
(1) Pie Chart: Illustrates the proportion of the 3 funds in the portfolio, at the date of the current record.
(2) Vertical Bar Chart: Shows the fluctuation in quote value, month-to-month, for the current fund.
(3) Line Chart: Compares the percent change from the first month, for all three funds.
(4) Vertical Stacked Bar Chart: Demonstrates the total value of all 3 funds, month-by-month.
A Thing of Beauty
The Charts plug-in package is a work of art. A handsome pdf documentation file, and license agreement, well constructed sample files, and the plug-ins themselves, for Mac & Windows. [Note that the Mac plug-in is much larger than the Windows version, because the former contains ten custom graphic backgrounds while the former does not. Waves In Motion is addressing this shortcoming.] The cost of the single-user version is $99. Site licensing is offered, and a developer license is priced at $500.
This package is so customizable, it'll leave you reeling. [Or if you're totally unflappable, charting.] While it may appear daunting at first, a careful read of the pdf manual and following the examples will get you to a point where the only thing remaining is a little tweaking of your output. It is possible to produce the graphics in a file other than the main file, so that the data and the chart may be viewed in simultaneous, separate windows. One Pie Chart segment may be "exploded" away from the pie for emphasis. Axis and label colors may be commanded from a palette of 18 color names or by specifying red, green & blue values between 0 and 255. You may set a background color in the same way, and similarly color your lines, pie segments and bars. Line thickness may be varied for chart lines, axes, grid lines and "tick marks".
Missing is the ability to add a text label to the axes: in the bonus file it would have been nice to display "Dollars" on the Y-axis and "Month" on the X-axis, with "Jan" through "Dec". WIM is working on that.
It's not stated in the documentation, but the increment between tick marks and grid lines must be a positive integer. The plug-in will crash FileMaker if fractional parameters are used. In the bonus file, the line chart shows data which are percentage changes, fractions with absolute value less than 1. The workaround: either (1) No grid or tick marks, or (2) a calculation that scales the data. I chose to multiply the data by 100, and then set the increment to 5. Too bad I couldn't specify that "5" meant "5%".
Otherwise, it was a surprise how tolerant the plug-in is, i.e. how seldom the databases would crash, given incorrectly formatted input parameters. In most cases, nonsense data would simply result in a nonsense plot, rather than a crash.
Gather ye data
Assembling the data to pass to the charting functions requires some thought, since the data will generally span several records. This may be done by a looping script or by having your script jump invisibly to a single-field layout, Copy All Records, and process the data from there; the latter method is generally faster. The bonus file shows it both ways. Note that both the "Line Chart" script and the "Vertical Stacked Bar Chart" script access all 36 records, but the Line Chart executes more slowly because it uses the looping script method. The Troi Text Plug-in, which is also reviewed this month, has the ultimate answer: a call to the SumText function can create a return-delimited or "pipe-delimited" list ("a|b|c|...") virtually instantly.
How Do I Use It?
The procedure of dropping the plug-in into the FileMaker Extensions ("System" if you use a Windows machine) folder and then relaunching FileMaker Pro 4.x, is standard. Check under Preferences:Application:Plug-ins to make sure it's there, and turned on. The basic sequence is as follows (Step 1):
Set Field ("gDummy", External("OzCh-Init", parameter))
where "parameter" specifies the chart type and the file and container field and recordID that will receive the finished chart, and the dimensions of the chart. Parameter might look like:
"vbar|" & Status(CurrentFileName) & "|gGraph|g|" & gWd & "|" & gHt
In this case rather than specifying a container field and a RecordID to receive our graph, I've used a global container field and "g" for global in place of "RecordID". Following that, one or more script steps like (Step 2):
Set Field ("gDummy", External("OzCh-Points", parameter))
where "parameter" specifies the data in string form to be passed to the charting function. The format of this string varies greatly depending on the graph type, and may include color values and numeric labels as well as the data itself. Finally (Step 3):
Set Field ("gDummy", External("OzCh-Draw", ""))
which does the work. Along the way you may have tossed in steps like:
External("OzCh-Background", "rgb(124/94/187)")
External("OzCh-Options", "axisfontcolor=purple|axisfontsize=11")
External("OzCh-YGrid", "color=ltgray|size=1|major=5")
to set the background, fontsize, fontcolor, X & Y-axis specs plus grid size, spacing and color. Creating a legend is as simple as:
External("OzCh-InitLegend", "")
External("OzCh-LegendData", "pointfontcolor=pink|pointfontsize=9|
Tom#red|Dick#green|Harry#blue|Esmeralda#cyan")
External("OzCh-DrawLegend", "")
Et voilà ! Of course you may script the building of the LegendData and the Points data, rather than plugging in text as shown above. Flexibility is the middle name of the Waves in Motion Charts plug-in. The Bar Chart script shows how to use FileMaker's Random function to set a random color for the bars, and there are plenty of examples of how to script the points parameters.
You don't have to remember how to spell those OzCh function names, because they're listed under "External functions" in the ScriptMaker definition window; this is the way all plug-ins behave. You will need to keep the documentation handy, because the "parameter" formats vary considerably from one function call to the next. The plug-in does all the grunt work of placing the axes in your container field and scaling everything to fit. [Note to Windows users: your script must take the added step of pasting the graphic into its container.] Negative x & y values are handled with ease - don't try those with pie charts. You have full control over your layout.
Because Charts does all the grunt work, one cannot specify where one wants the axes drawn. The X-Axis is always drawn at Y = zero, which is mathematically correct, and where one wants it in many cases, to make clear the comparison to "baseline". But other times it would be useful, in order to emphasize the incremental change along the X-Axis (i.e. magnify the scale of the Y-Axis,) to set the baseline of the chart to a different number. It would be cool if WIM would consider additional axis parameters like min & max:
Set Field ("gDummy", External("OzCh-XAxis", "min=10|max=12"))
or:
Set Field ("gDummy", External("OzCh-XAxis", "min=" & Int(Min(relation::value)) & "|max=" & 1+Int(Max(relation::value))))
whereby the user may choose to indicate a value for either min, max or both, and have default settings prevail if either parameter is not set.
As a bonus bonus file, included is SCATTER.FP3 which shows how the Scatter graph function can be used for scientific plotting. (No conspiracy theories, please!) Note how the background colors may easily be changed, and how several function plots may be distinguished from each other by use of color and point styles. Grid lines for both X & Y values are charted. A third method of assembling the points data is utilized: generating the numbers on the fly. Also seen is how a complex graph may be plotted from a single busy script - not to imply that this represents the ideal scripting style; you're trading "elegant" for "compact". Again, there's a sample graphic you get to look at if the plug-in is not installed.
A Scatter plot is a collection of points without any line or curve to organize them. It would be interesting to see a mathematician apply a "least squares" or similar technique to create order from such a plot. Conversely, it would be nice if "point styles" included a "null" selection for line graphs. One doesn't always need to see the "cross" or the "diamond" marker for each plot. In the case where many points are being plotted, the cross point style just gives the plotted curve a shaggy appearance.
Background check
The only way to synthesize ones own custom backgrounds is with ResEdit. WIM might want to explore making this easier to modify. I expected to find the background graphics stored within a repeating field, but this helps explain why the background implementation for Windows is still in the works.
An incidental use of the "Background" call: create color-filled graphic containers holding any of the 256x256x256 colors that may be viewed on a monitor having 8-bit color depth.
The author of the FileMaker Pro-only solution mentioned at the top of this review argues that his solution does all the charts and graphs for you WITHOUT a plug-in, which (1) relieves you of the cost, complexity, and user licensing of a plug-in, and (2) his solution charts are calculations which automatically update with your data. The plug-ins require scripts to run to update the charts. I would agree with his assessment except the complexity issue. The Waves in Motion Charts plug-in is far easier to use, and it is light years ahead in terms of flexibility. And speed. And file size.
The people at Waves in Motion plan to resume enhancing their package after MacWorld where they'll likely pick up many new ideas from their users. It's difficult not to get excited about this package, and it is only going to get better. Perhaps we'll soon get to work with 3-dimensional bar plots, layers of bars behind bars. We are witnessing the dawn of a new era of speed and customizability for FileMaker charting.
Perhaps the most complete listing of plug-ins for FileMaker Pro 4.x is to be found at Bev Voth's DIG-FM site, <http://www.pc-mac.com/digfm/fourO/plugs.html>. You may also be interested in signing up for the FileMaker Plug-ins mailing list by visiting <http://www.troi.com/lists/> and clicking on the appropriate link.
All of my testing for these reviews will take place on a Mac, but most plug-ins are now being built with two versions, to run on both platforms. Next time, we'll take look at Peter Baanen's set of plug-ins, which may be downloaded in demo version from <http://www.troi.com/>
## END ##