UTF, WTF! Writing HTML and JSON from FileMaker


Recently, I was working on a Timeline creation database. A FileMaker Pro database is used to store the events that will be displayed on the timeline. When the data has been entered, the database is designed to export two files, one an HTML file for the main page display and the second a JSON file containing the timeline elements. The two files (HTML and JSON) then interact with a set of Javascript and CSS files to create an interactive visual timeline and slideshow combination.

For those unfamiliar with the term JSON it stands for JavaScript Object Notation. JSON files are text files that are often used in place of XML files to store and retrieve multi-level data. They have become popular because they allow you to represent complex data structures in a less ‘wordy’ way than XML.

In the process of attempting to build the scripts to write out these two files to disk, I came across some annoying shortcomings in FileMaker Pro’s export options that I would like to spend some time discussing.

FileMaker has two methods of exporting data:

  1. one script step from the Record level and
  2. one from the Field level.

FileMaker developers can select to either Export a single field’s contents by using ‘Export Field Contents’ or Export a batch of Records using ‘Export Records’. When generating an HTML or JSON file, it is often more convenient to put all your text into a single field and then just export that field. There is a big gotcha in using Export Field Contents however—it exports to the UTF-16 character set with no option to select UTF-8. This can create problems because JavaScript files are usually interpreted by the browser as UTF-8 encoded.

UTF-8 vs UTF-16

wpid2664-media_1346268132539.png

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.

‘Export Field Contents’ vs ‘Export Records’ Approach

wpid2659-media_1345590912742.png

Which one to use? Well it depends.

Using ‘Export Field Contents’

wpid2656-media_1345590477311.png

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.

Specify Output File

wpid2655-media_1345590391878.png

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.

wpid2665-media_1346276426665.png

The Solution: Employ ‘Export Records’ step instead

wpid2660-media_1345591239061.png

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.

Specify Export Order dialog box

wpid2657-media_1345590641650_1.png

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)

Select UTF-8

wpid2658-media_1345590657079.png

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

Sample file open in TextMate in UTF-8

wpid2663-media_1345591520272.png

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.

Still not working? It is probably the Line Endings.

wpid2661-media_1346087861065.png

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.

The Ultimate Solution for exporting text documents for the web — use a plugin

wpid2662-media_1346088541768.png

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.

Footnote: FileMaker Go vs FileMaker Pro

wpid2666-media_1346691676340.png

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.

Advertisements

9 Responses to “UTF, WTF! Writing HTML and JSON from FileMaker”

  1. Good and valid tips! On the Mac one can also use “iconv” in a shell script. I’m sure there’s a Windows equivalent…

    • Thanks David. Good idea but it still is another hoop to jump through. My dream is to just click a FileMaker dialog option and be done with it.

      • I just tried iconv in Mac OS X and it worked as you suggested. Try running something like this from the Terminal:

        iconv -f original_charset -t utf-8 originalfile > newfile

        more specifically:

        iconv -f utf-16 -t utf-8 /Users/<>/Desktop/FMTimeline/FileMaker-Pro-Timeline.json > /Users/<>/Desktop/FileMaker-Pro-Timeline.json

        When I tried just overwriting the file, I got a blank file, so it seems like you have to save it in a new location, not just overwrite. It didn’t solve the line ending issue however. It still showed up as Mac Classic CR.

    • that’s what I used too! I forgot…

  2. “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.”

    I think it would be nice if FileMaker could add JSON as an Export type.

  3. “XML Export with an XSLT stylesheet” seemed to work well while testing, but the export time seems to get exponentially longer, the larger the field gets.
    For me on Pro 13 Advanced against a 13 Server:
    125 KB took 1:24 (minutes:seconds)
    150 KB took 2:43
    200 KB took 6:42
    300 KB took 20:02!
    I’m trying to export a 5MB field. 😦

Trackbacks/Pingbacks

  1. Export Calendar files from FileMaker Pro – Virtual List Technique | HomeBase Software - May 22, 2014

    […] options and it is possible to generate UTF-8 files. I have been over this issue before in a previous blog post, so I will not belabour the point. Perhaps this functionality will make it into FileMaker Pro […]

  2. Export Calendar files from FileMaker Pro - May 27, 2014

    […] of the more universal UTF-8 formatting is a mystery. I have been over this issue before in a previous blog post, so I will not belabor the point. Perhaps this functionality will make it into FileMaker Pro […]

%d bloggers like this: