FileMaker Pro 9, ESS and Those Strange Box Characters

Anyone who has had to deal with exchanging data between a FileMaker Pro database and data stored in other database systems (MySQL, SQL Server, Oracle etc) will be familar with handling characters that FileMaker doesn’t appear to handle all that well. This is especially true when retrieving data via the web, e.g. an HTTP POST response back into a FileMaker field. Many of you for example are familar with the strange square box character that appears. If you’re working with ESS tables you more than likely going to have to come up with a way of dealing with these characters, which are generally ASCII characters that you normally don’t see in FileMaker, such as Line Feed, Vertical Tab and Carriage Return.

Here’s an example that we came across. If you fill out our Contact form on our website you might enter something like this:

Note the returns in the Comments field. This form data is being stored in a MySQL database which we access using the great ESS feature of FileMaker Pro 9 (no more laborious copy/paste of data from an email or trying to parse the email form into the different FileMaker Pro fields!). When you view the ESS table and this field it now (at least on Windows with FileMaker Pro Advanced 9.0v3) looks like this:

Notice the square box characters that appear – it’s obviously not ideal to display these to your FileMaker users. There are a number of ways to deal with this but the way we’ve approached it in this case is to create a supplemental field in our ESS shadow table (remember you’re limited to calculation and summary fields when adding supplemental fields to an ESS table) and take advantage of a great new function that’s part of the Troi File v4.0 plugin: TrFile_AsciiValueToText function. As you’ll see shortly this makes easy work of dealing with special or invisible ASCII characters. We simply created a new calculation field with a text result using this formula:

Substitute ( comments; TrFile_AsciiValueToText( “-Unused” ; 10 ) ; “” )

Now if you look at the original MySQL field on the left and the new FileMaker supplemental calculation field on the right you’ll see the difference:

In the past we have tried to use the native Substitute function in FileMaker to handle these characters with mixed results. We tried copying the square box character to the clipboard and pasting into the calculation dialog:

So far so good. Now click OK to save the formula:

 You can’t see the box anymore but it looks like something’s still there – let’s go back in to the calculation and check it:

Hmmm that’s interesting the square box character has gone. Once you get to this point and do some replacements using the Substitute function you don’t get very far. We also noticed some other strange visual behavour. If you have a field with this same character in it and view it at 100% zoom level in Browse mode it looks like this:

Change the zoom level to less than 100% or more than 100% and you’ll see something like this:

Our suggestion is to find a tool that can help you identify which ASCII character you are dealing with. If you’re using the Troi File plugin make sure you upgrade to v4.0 or later so you can use the TrFile_AsciiValueToText function and simply call that within a Substitute function referencing the ASCII character you wish to replace. It’s much easier and cleaner than trying to copy/paste characters which don’t survive a copy/paste, at least on Windows.

There’s more details in this article Entering Clean Text (or: avoiding unwanted characters) and if you’re a FileMaker TechNet member search the TechTalk archives for other approaches and comments. You can get more background info on ASCII characters at this WIKI entry.