UTF, WTF! Writing HTML and JSON from FileMaker

Who knows what this chart is talking about, but according to Wikipedia, “UTF-8 (UCS Transformation Format—8-bit) is a variable-width encoding that can represent every character in the Unicode character set. It was designed for backward compatibility with ASCII and to avoid the complications of endianness and byte order marks in UTF-16 and UTF-32.”

In the early days of computing, the ASCII character set was sufficient, but it was limited in the number of characters it could contain. With the development of the Internet, there was a demand to represent international languages, some of which had many more characters in their alphabet, so Unicode was developed with a much larger character set. Different methods of representing this character set were developed, but UTF-8 is the one that is getting used on the web.

Which one to use? Well it depends.

In this case I have concatenated a series of fields into a single global field that contains the JSON text, so my first inclination was to employ the ‘Export Field Contents’ step.

Once I select the field to export, I am given the option to Specify the Output File’s name and path. In this case, I have generated a meaningful name and the path is set to my Desktop. The only thing missing here is there is no way to select the file character set format and the problem is that FileMaker Pro defaults to UTF-16 at this point. The Javascript files that will read this JSON file are formatted to use UTF-8 characters.

It would be very useful if the next revision of FileMaker Pro included the ability to select different character sets in the Export Field Contents dialog box.

At this point, I tried re-saving all my Javascript files as UTF-16 to see if this would solve the problem. It sort of worked. I could view the files in Safari on a Mac properly. There were two issues though:

1. In my situation, the switch to UTF-16 caused problems because when the Javascript (TimelineJS) I was using reached out to Google’s Youtube API (which is UTF-8) it became confused and would not load a preview of a YouTube video.
2. Firefox would not load the UTF-16 files.

Back to the drawing board, I decided to try the other Export route, Export Records, because it does have the ability to specify the character set.

Fortunately, there is a workaround. The steps are:

1. isolate the record first and
2. then employ the ‘Export Records’ step.

We are using a step designed for exporting multiple records on a single field of single record, a workaround necessitated because the popup is missing in the Export Field Contents dialog box.

The script steps to add:

1. Show All Records,
2. Omit Record,
3. Show Omitted Only
4. and finally, use the ‘Export Records’ step instead of ‘Export Field Contents’.

Another way to achieve the same result would be to put the record ID you are on into a script variable and ‘Perform Find’ on it. Choose whichever technique works best for you.

The net result is to isolate a single record for the purpose of exporting. When FileMaker does a ‘Show All Records’ it remains on the record you started with. When you Omit that record and Show Omitted Only, you end up back where you started, but with only one record showing. Since we are exporting from a Global Field and a single record, we should have no repetition in the exported file.

Once the output file has been named, it is necessary to specify the Export Order. In this case, the export consists of a single record and a single global field. (JSON_Export)

Now select the correct format from the ‘Output file character set’ popup option. For most web uses, the format should be UTF-8.

1) To illustrate that the ‘Export Records’ approach worked, when I opened the JSON file in a text editor (TextMate) and did a Save As, the default format indicated is set to UTF-8.

2) That same file when saved in the UTF-16 format, (using ‘Export Field Contents’) looks like the second item in TextMate. The default is UTF16(Little Endian) file encoding.

FileMaker’s text export still might not work for your web files because FileMaker Pro does not give you control over the line endings in your export file. The screen shot above from TextMate shows that FileMaker is exporting old-style Mac OS Classic Carriage Returns and what you really want is a Unix style Line Feed.

It is possible to open the exported files in something like TextMate and re-save the files, specifying the LF Line Ending, but the goal is automation, not more human intervention.

This is the point where I started to look at plugins or AppleScript to achieve my goal. I dismissed AppleScript because it was not cross platform. Next up, I had a look at two free plugins: BaseElements free plugin and ScriptMaster from 360Works both of which offer the ability to write out text files. I found the documentation for 360Works easier to follow and settled on it, although BaseElements doesn’t require Java, so it might have been the better option. I hope to have a demo of FM-TimelineJS posted soon which will demonstrate the technique I employed with ScriptMaster.

In the end (ian) it seems like a lot of hoops to get what you want — it would be great if the development team at FileMaker Inc. saw fit to add that little dialog box in Export Field Contents. It would be even better if they were to include options for line endings in the text file exported.

The problem with all this stuff is that it is hidden, you think you are exporting a text file. Text file is a text file right? Not so fast. It took me hours, much head scratching and calls for help to figure out what was going on.

Again, going back to the Wikipedia entry for UTF-8, they note: “UTF-8 has become the dominant character encoding for the World-Wide Web, accounting for more than half of all Web pages. The Internet Engineering Task Force requires all Internet protocols to identify the encoding used for character data, and the supported character encodings must include UTF-8.” That definitely seems like it might be something you would want to be able to export to.

The fact that FileMaker Pro does not support the UTF-8 standard fully and still offers only the option of OS 9 Carriage Returns, is disappointing in this web-oriented age. We remain hopeful that this situation will improve.

In the FileMaker Go 1.2 manual it states that “FileMaker Pro (fp7) exports text as UTF-16, but FileMaker Go exports text as UTF-8.”

You may be able to use this ‘feature’ (anomaly? strangeness? inconsistency?, act of brilliance on the part of the FileMaker iOS dev team?) in your FileMaker 11/FileMaker Go 1.2 and FileMaker 12/FileMaker Go 12 solutions. Since FM Go 1.2 does not support Export Records step, but does allow you to Export Field Contents, you can have a branch step.

Some pseudo-code might look like:

• If platform is FM Go,
• use Export Field Contents (which is UTF-8 on FM Go)
• else
• (on Mac and Windows) use Export Records.
• end if

This in theory at least may allow you to skip the use of a plugin to generate files on iOS.

Update: 2012-10-01 Many thanks to Beverly Voth for her help in making my Timeline solution plugin free. Beverly commented in the FileMaker Facebook group that I should try using an XML Export with an XSLT stylesheet to transform the export to whatever I was aiming for (in this case UTF-8 with Unix LFs instead of Mac Classic CRs). I protested that XML was too complicated (she wrote the book on FileMaker and XML). She created a very useful follow up blog entry over at FileMaker Hacks, including a FileMaker demo file that illustrates the technique. After a bit of back and forth, I was able to achieve the desired effect. The trick is to export an XSLT file from a FileMaker Container field, so that the text in the XSLT file doesn’t default back to UTF-16. It is still too many hoops to jump through in my opinion, but in the end, it works and what else can you ask for? To see this technique in action, have a look at my blog posting on creating Timelines in FileMaker Pro. There is a demo file included with the post.

Update: 2014-12-02: I am pretty convinced now that until FileMaker Inc., decides to add a formatting popup to the Export Field Contents dialog box, the best route to exporting HTML, .ics, vCard files etc is to use the Virtual List Technique. The technique is detailed elsewhere, but you can find specific examples of it in action here.