FileMaker - reporting with automatic vertical headers
I put quite a bit of thought into what I should call this blog entry with the hope that it would be easily located in web searches but in the end I realised that I couldn’t sum it up with anything short and snappy. Hopefully you’ll find the content more helpful than the title!
FileMaker allows for a great deal of control over the creation of reports. Aside from the main body part, we have layout parts such as title header, header, leading grand summary, sub-summary, trailing grand summary, footer and title footer. We have layout parts which automatically resize so that most of the ugly white space is banished and we can have page numbering with all manner of other effects which make data more readily digestible and visually appealing.
Some time ago a client wanted to generate a printable report where a vertical ‘header’ field would automatically resize based on the amount of text in the field to its right. Something like the image below.
As you can see, the pale purple box on the left, with Martin Luther King at the top, is the same height as the text in the white box to the right. That looks nice and easy and a perfectly reasonable request for the client to make but what the client didn’t know is that FileMaker cannot resize one field based on another. For example if we had a FileMaker layout with two fields each set with the sliding rules suggested below, it would look something like this in layout mode :
But in Preview mode, it would result in this :
Why not turn off the sliding? Well then we’d get text boxes which will only ever be one size i.e. the maximum size as defined on the layout.
The solution is simple.
You need a copy of the SpeechBody field (as shown above) which cannot be seen in preview mode but still takes up the same amount of space as its visible counterpart. The field will need to be exactly the same size as the visible SpeechBody field, with the same font style and font size and so on BUT it will need the pale purple background required in the SpeechPerson field. You now place this hidden SpeechBody field behind everything else, lay the visible SpeechBody field over the top and then add the SpeechPerson field. Don’t forget appropriate sliding for all fields and you should end up with something like this exploded version :
This allows for a vertical header beside a field rather than a horizontal header above a field.
I have included a file of the database (FileMaker 12) I used to generate most of the images included in this blog. Please feel free to download it and use it as required (toggling between preview, browse and layout modes would be a good bet). I have created two layouts - one with the 3 field approach as described here and one without (so you can see how it won’t work with 2 fields on their own).
If you choose to add any of these features to a database of your own, make sure that you uncheck “Delineate fields on current record only” under Layout Setup otherwise the field fills will not be shown in preview mode.
That's it for now. I hope you find it useful!
Please get in touch with your comments and thoughts.