Beverly Voth, Level: Intermediate, Version: FM 14 or later, XML

Excel Exports using XML and XSLT

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

Continue reading “Excel Exports using XML and XSLT”

ExecuteSQL, Level: Intermediate, SQL, Version: FM 12 or later

User-Friendly Excel Exports, part 7

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading “User-Friendly Excel Exports, part 7”

Level: Intermediate, Version: FM 12 or later

User-Friendly Excel Exports, part 6

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.

Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.

Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.

Demo files:

Continue reading “User-Friendly Excel Exports, part 6”

Beverly Voth, Level: Any, Version: FM 16 or later

An In-Depth Look at “Export Field Contents”

Have you ever wanted to export a single field and maintain all the characters in that field? This article explores the possibility with XML Export and the use of a simple XSLT. But first we’ll explain the good, the bad and the ugly of some different standard ways to export TEXT out of FileMaker.

Using this sample text in one field (and one record) we will make different exports and review the results:

Continue reading “An In-Depth Look at “Export Field Contents””

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 3

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

So far in this series we’ve been looking at exporting data to Excel from a single table (Contacts). Today we’re going to extend the technique to encompass a second related table, Donations, and without too much trouble we can leverage existing work from earlier entries in this series (part 1, part 1.1 and part 2).

To avoid unnecessary repetition, this article assumes familiarity with the aforementioned predecessors, and you can follow along in today’s accompanying demo file, excel exports, part 3, if you are so inclined.

Continue reading “User-Friendly Excel Exports, part 3”

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 2

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

[Note: To avoid a huge amount of redundant repetition, this article assumes that you are familiar with part 1 and part 1.1 in this series.]

Today we’re going to take a look at streamlining the approach introduced in part 1 by reducing clutter on the Relationships Graph, going from this…

…to this, with no loss in functionality.

…and you can follow along in today’s demo file, excel exports, part 2, if you are so inclined. Continue reading “User-Friendly Excel Exports, part 2”

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1.1

Bruce Robertson emailed me off list with an improved version of my demo from part 1, excel exports, part 1.1, which I am sharing with his permission. The improvements are…

a) live preview fields showing the data to be exported.

b) an “Eval Error Tester” script to make sure the output code in the alias table is well-formed.

Thank you Bruce for making this available.

Level: Intermediate, Version: FM 9 or later

User-Friendly Excel Exports, part 1

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Many end users appreciate FileMaker’s ability to export a found set of records in Excel format — it’s fast, straight-forward and empowering. One request that I have received from clients multiple times over the years is: make the exported field names in the top row of the spreadsheet “human-friendly”…

For example, instead of this:

…perhaps they would rather see this:

Another request has been to allow data from multiple source fields to be combined into single export columns, as per columns A and C here:

And a third request has been to provide an interface so that users can pick and choose fields to export, including (where appropriate) related fields, without having to navigate the complexity of the native FileMaker export dialog. Continue reading “User-Friendly Excel Exports, part 1”