Exporting to Excel
Five years ago, Kevin Frank developed and wrote an exhaustive series of articles on exporting to Excel. The whole point of his series was to bypass the very limited options provided in FileMaker to give the end-user a nice looking spreadsheet without having to do a lot of manipulation in Excel after opening the exported file. This included adding custom header fields selected by the user as the first row of the spreadsheet:
Pretty powerful stuff. You’ll find the original posts here:
Frank updated the posts recently to speed up exporting over a Wide Area Network (WAN) using techniques from Beverly Voth combined with his techniques. The latest posts contain six (yes, six) free downloadable technique files.
Needless to say, you’ll want to read all of these and save the downloads in a safe place.
I stumbled across this post from Daniel Wood recently and thought it worth sharing. It’s a bit dated (2010), but still seems to be a great technique.
The problem stems from summing a large number of related records on the parent record table. We’ve all seen the “Summarizing field:xxx” when opening a layout, something that can take a long time, especially over a WAN.
Wood credits one elegant solution to Tom Elliot, and later in his post shares an even better and faster method:
I have subsequently implemented this technique in a customers solution with great results. In the end I did not use the Last() implementation, rather I used the Get(FoundCount) method. This involves creating an unstored calculation on the table you want to count records from, and set it’s calculation to “Get ( FoundCount )”.
Subsequently, you can count records from any portal by placing on the layout this calculation field, obtained via the portal relationship you want to count. This method works brilliantly, and can be used on both unsorted and sorted portals (the last method requires the relationship to be unsorted.
Be sure to read the whole post, and to browse the comments. This trick saves time for the user and reduces load on the database.
ULearnIT is an Australian based website has a series of very informative posts on various aspects of FileMaker. The post linked above is a detailed look at the Container field, including inserting, getting meta data, formatting, and more.
Other posts on the site include Dialog boxes, Encryption, Mentoring Services, Business Efficiency, and more.
Check it out when you have time to learn. Or even if you don’t.
James Bryant Conant (1893 – 1978)