The char() function, introduced in FileMaker Pro 10, is a rarely used tool that not only can make certain tasks easier, but can also help developers extend the out-of-the-box capabilities of FileMaker Pro. This function takes a single numeric parameter representing a Unicode decimal point. So it allows developers to easily and natively represent any characters in the Unicode character set in our FileMaker calculations. For example, Char(10003) will return a checkmark ✓. Char(9664) and Char(9654), respectively, return a left-pointing triangle ◀ and a right-pointing triangle ▶. These characters can be generated anywhere the FileMaker Pro calculation engine is available, and because they are just characters, they can be formatted using the formatting tools available in FileMaker Pro, including conditional formatting and the text formatting calculation functions.
A specially noteworthy character that can be generated using this function is the zero-width non-breaking space character, or byte-order mark, whose Unicode decimal representation is 65279. This is essentially a null character which is invisible when included in a string. In this post I will refer to byte-order marks as BOMs. Lets now look at an interesting use for BOMs.
Using BOMs in value lists
When we have a dynamic value list composed of 2 fields (say an internal ID and its corresponding user-facing value), and we want to store the values in the first field but only want to display the values in the second field, FileMaker Pro only gives us the option to sort the value list by the values in the second field, which I believe is rather limiting. However, we can bypass this limitation by using char(65279). So here is what you can do broken down step by step: imagine we have 2 tables, Order and OrderStatus, and in a layout based on Order, we want to display the values in OrderStatus in a custom order. In OrderStatus, create a numeric SortOrder field, and in each record enter a number representing the order in which the corresponding Status is to be displayed in the value list, as shown below.
Then create a calculation field with the following formula:
_PrependWithByteOrderMarks ( StatusName ; SortOrder )
Here I use a simple recursive custom function to insert as many BOMs in front of StatusName as determined by the value in SortOrder. So for example,
_PrependWithByteOrderMarks( "Green" ; 12 )
returns the value "Green" with 12 byte order marks in front of it. Here are the signature and definition for this custom function:
_PrependWithByteOrderMarks( value ; sortOrder ) Let([ v = value; so = sortOrder; ch = Char(65279) ] ; If( so = 0 ; value ; _PrependWithByteOrderMarks( ch & value ; so - 1 ) ) )
Now we just need to create a dynamic value list that displays the values sorted based on our custom order, like in the screenshot below. We use the OrderStatus primary key for the first field (this is the value that will be stored) and the calculated field with pre-pended BOMs for the second field (the field users will see). It’s important to select the option to re-sort the values based on Unicode, otherwise the BOMs will be ignored.
Then if we associate this value list with the Status ID foreign key field in the Order table, this is what the user sees:
If you need to display the list of values using different custom orders in different places in a solution, simply create another set of fields (SortOrder and StatusCustomSorted fields) and a corresponding value list.
This technique is limited to 100 value list values. The reason for this is that FileMaker Pro utilizes the value index for value lists, and the value index consists of the first 100 characters of each line in a text field. So as long as you don’t need to display more than 100 values, I think the technique described in this post is an easy and native way to extend FileMaker Pro’s value list sort options.
If you would like to download a very simple demo file that showcases this technique, you can do so using this link: SortValueListUsingBOMsDemo A.fmp12
There are other uses for BOMs, and reviewing some more of them may justify a follow-up post. So stay tuned!