BlogFileMaker

Extending FileMaker Pro’s value list sort capabilities using the Char() function

By September 11, 2012 56 Comments

Introduction

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.

Value list

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.

Value list dialog box

Then if we associate this value list with the Status ID foreign key field in the Order table, this is what the user sees:

Resulting value list

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!

Have questions? You can contact my team directly for more insights. Or, check out our other FileMaker posts to learn more about customizing your solution.

Marcelo Piñeyro

Marcelo Piñeyro

Marcelo is a Senior Technical Project Lead with Soliant Consulting. He has been developing software since 2004. He lives in Madison, Wisconsin. In a previous life, Marcelo was an Agronomist and Animal Scientist. In his spare time he enjoys dancing tango, bicycle camping, remodeling his home, and sailing.

56 Comments

  • Avatar Daniel Wood says:

    Genius!! You sir, have done what FileMaker have failed to give us in however-many-years value lists have been in existence, kudos!!!!!!!

  • Avatar Nils Waldherr says:

    You can repeat a character (or string) much easier by using this little function:

    Substitute( 10^ n-1; 9; s )

    where s stands for the string to repeat and n is for the number of repetitions.

    Nils

  • Marcelo Pi├▒eyro Marcelo Pi├▒eyro says:

    Daniel, thank you very much for that generous feedback. I am glad to know that you like this technique.

    Nils, thanks for sharing that clever formula for pre-pending characters to a string. In the case of the technique described in this post, using that formula would work just fine, as the technique itself is limited to 100 iterations. However, your technique is limited to 404 “iterations” if you will (presumably due to FileMaker’s number fields only storing up to 800 digits of numeric precision, 400 to either side of the decimal point). So if you need to prepend a character or a string more than 400 times to another character or string, using a recursive custom function that implements tail recursion, like the one provided in the post, allows you to iterate almost 50,000 times.

  • Avatar Joel Shapiro says:

    This is very cool, Marcelo! Thanks for sharing.

  • Avatar john renfrew says:

    Brilliant

    And Nils clever bit works up to 10^404-1

  • Avatar Kevin Frank says:

    Incredible… this gets my vote for blog posting of the year… or the century. Thanks Marcelo!

  • Awesome! I was just dealing with this the other day and telling myself I was ok with sticking 1), 2), 3), etc. in front of values. You’ve solved a big issue! Congrats!

  • Avatar Rob Russell says:

    I love simple solutions. Very nice.

    r

  • Avatar Ray Santangelo says:

    Marcelo, you evil genius … you just made at least one person’s day over here (and many more, I’m sure). Thanks!

  • Mislav Mislav says:

    Great post, Marcelo.

    And I like Nils’s tip too. FWIW, the 404 limit that was pointed out can be extended to 163,215 by tweaking his function like this:

    Let (

    m = int ( Sqrt ( n ) ) + 1 ;

    Left ( Substitute ( 10^m-1; [9; 10^m-1]; [9; s] ) ; n )

    )

  • Avatar Joel Shapiro says:

    NOTE:
    This does not seem to work in Windows XP. Small squares display where the BOMs are. (Maybe a UTF-8 / UTF-16 issue?)

    Vista and 7 seem fine.

    @raybaudi: are you using XP?

  • Avatar raybaudi says:

    “@raybaudi: are you using XP?”

    Yes, Joel. 🙁

  • Avatar Howard Schlossberg says:

    Strange, but the demo did not work out of the box on my Windows 7 (64-bit) machine. If I change the StatusCustomSorted field’s storage to Unicode, however, it does work.

  • Avatar Andr├⌐s L├│pez Pascual says:

    This is awesome Marcelo! very smart and an elegant solution for this FileMaker limitation.

    I’m using your technique together with Andries Heylen’s “Magic Value Lists” to have an “actions” value list populated using an ExecuteSQL query to the “Actions” table.

    Actions display in the form of a pop-up with items showing depending on current layout and current user privilege, script trigger to perform the action when selected. Very cool for iPhone development. The missing link was the sorting of the pop-up … thank you for sharing!

    P.S.: Me alegro de haberte visto en DevCon, pr├│ximo a├▒o en San Diego!

  • Avatar Andy Hibbs says:

    Marcelo, we love this solution.
    We would be interested whether anyone has resolved the only problem come across so far using this with FileMaker Pro 11v4 on Mountain Lion
    When using this technique to enter an ID into a number field, “no values defined” is displayed. The value list displays perfectly in a text field and, upon upgrade of a v11 file to v12 the problem disappears and immediately the number fields allow the value lists to display. The Unicode sorts are in place, like Howard we had to reset this on the v12 demo file.
    We have tried both the custom function and Nils calculation, but it appears to be a behavioural difference between v11 and 12, which is not ideal as we are usually trying to set numeric ID fields.

  • Wow, this is one of those “just when I thought I knew it all” tricks. Nice elegant solutions on generating repeating text strings without custom functions, too.

    Andy Hibbs, I had no trouble getting this working in FM 10 & 11 using only numeric fields. Feel free to check out my solution at http://www.kupietz.com/2013/01/28/dynamically-sorting-relational-numeric-value-lists-by-custom-order-in-filemaker-pro-11/ .

  • Avatar Jason Tallman says:

    Thanks for this great article. I’ve implemented this into my vertical solution and my customers are loving it. Just as a heads up I have noticed on both Macs and PC’s running Filemaker v12.3 if you have a layout with a checkbox set the Export to PDF feature in Filemaker will prepend the boxes for each byte-order mark regardless of the platform or version of the OS. Printing works as expected but PDF export does not.

  • Avatar Nigel Browning says:

    Great partial solution to a long-standing bugbear in FileMaker. As with so many things in this endlessly frustrating application, one has to spend hours pushing at the edge of what it will do merely to achieve something that would be simple for the developers to include and is accepted practice in most other applications!
    The points made about the Unicode perhaps need reiterating: both the sort order in the Value List AND the Language in the storage of the field containing the BOMs need to be set to Unicode. In the demo file, the latter is not so set, so it does not work until this is changed.

  • Avatar Chris Cain says:

    Extraordinary, and at the same time simple and elegant. Thank you so much!

    I’ve been working on a method where I can dynamically create a value list of commands for the user. The technique has some exciting implications, at least for the solution I’m working on, and I worked out all the kinks to make it widely applicable in my solution… but I still had to number the value list entries (the list of available actions)to keep them from alphebetizing. Having solved some pretty big challenges to get the moving parts to work, those meaningless sequence numbers were really ticking me off, but I had given up and assumed they were a necessary evil if I wanted all the other cool functionality I was getting.

    Yesterday, geeking out on the phone with Kevin Frank, he pointed me to this article. Much pondering, 10 minutes or so of revision and poof! No more silly numbers! For me, this was super dramatic, since that dynamic value list is already in about a million places in the solution, and this change applied solution-wide.

    I actually sat there for awhile just clicking different drop-downs and pinching myself to see if I was dreaming. 🙂

    Thank you so much, Marcelo, for sharing.

  • Avatar Teresa Kenyon says:

    Honestly, This saved me! I’m creating a database for a clinical trial and we have to have an easy way to export data out in numerical format. So making sure that I have data coded prior to the export is phenomenal. THANK YOU THANK YOU.

    I am new to FM so when I see something like this I am in comeplete awe.

  • Avatar Jonathan Fletcher says:

    Wow. This is why I NEVER think I know it all. I keep running across stuff like this. I don’t mind feeling stupid at not having thought of things like this already. I’m just happy that there are giants out there that allow me to stand on their shoulders. Thanks!

  • Avatar Oreste says:

    Hi, Is it possible to use a Carriage return so that you have 2 levels. I think it will sort differently. For Instance

    111
    111
    Value1

    111
    112
    Value2
    111
    113
    Value3

    Make sense?

  • Avatar pixi says:

    REALLY great idea!
    but please consider to set the “StatusCustomSorted/OptionsΓǪ/Storage Options/Indexing/Default language” to “Unicode”.

    i was wondering why it didn’t work here until i figured out that this value was set to “English”.

  • Avatar Bruce Robertson says:

    Actually, the char() function handles more that just a single unicode character.

    Let(
    theString = “Actually char() handles more than one character.”;
    List( code( theString) ; char( code( theString)) )
    )
    =>

    460011400101001160009900097001140009700104000990003200101001100011100032001100009700104001160003200101001140011100109000320011500101001080010000110000970010400032000410004000114000970010400099000320012100108001080009700117001160009900065
    Actually char() handles more than one character.

  • Avatar himagine says:

    Use 3 zero-width chars for “10 digit 3 based number” instead of 1 BOM for variable length,
    it is able to order 0~59048 values.

    This is a function getting prepend string. (not recursive)
    Let ( [
    $n = order ;
    $len = 10 ;
    $c0 = Char ( 8203 ) ;
    $c1 = Char ( 8204 ) ;
    $c2 = Char ( 8205 )
    ] ;
    Evaluate ( Let ( $i = $len ; Substitute ( 10^$len – 1 ; 9 ; “Choose ( Mod ( Int ( $n / 3^Let ( $i = $i – 1 ; $i ) ) ; 3 ) ; $c0; $c1 ; $c2 ) & ” ) & “\”\”” ) )
    )

  • Avatar Joe says:

    Has anyone else encountered the issue with Windows (displaying squares)? I’ve discovered that changing the field’s font to Lucida Sans Unicode seems to hide the squares, but I’d hate to have to go in and replace the font for all the fields in a solution.

    I’ve set all the storage options to Unicode, as well as the VL settings to Unicode, all to no avail.

    Any and all info is appreciated!

  • Avatar TonyC says:

    Function not recognized…
    I have the demo file working now that I have set the two relevant options to Unicode as described in other comments and the demo works as described. However, I am trying to use the solution in another file. I have recently downloaded a trial version of FM Pro 12. When I put in the calculated field based on my table field choices the function is not recognized. In the Functions list I can see the Char (number) functions listed. Any suggestions would be welcome.

  • Avatar Jeff Drake says:

    Awesome job! Now for the coup de gr├óceΓǪ┬áhas anyone figured out how to make this work with separators? Char ( 65279 ) & “-“ΓÇöunsurprisinglyΓÇödisplays a “-” instead of a separator.

  • Avatar Steve says:

    Hi,

    I’m trying to use this technique with a list that has numbers in the values. For example I have records with these values:

    5m Fence
    6m Fence
    7m Fence
    11m Fence

    I’ve set the Sort Order field so they should sort in the above order, however my list is showing them as:

    11m Fence
    5m Fence
    6m Fence
    7m Fence

    Is there a way to have them respect the Sort Order in this example when I have numbers as the leading character?

    thanks,
    Steve

  • Avatar wanderer says:

    If your number is lower than 100 (the list shows max as 11), Sort Order expression is
    Substitute(10^GetAsNumber(valueField)-1;9;Char(8203))

  • Avatar marbel911 says:

    Hi,
    I’ve applied this fantastic idea in my application but doesn’t work; I’ve replicated the original Mr. Pinero’s application importing files and data, defining value list, fields and and it works.

    I explain better. If I reply the Mr. Pinero’s application in a new one, the BOM sorting works in the same way, but if I try to reply in my application also with the same tab names, fields and all, It’ doesn’t work: it sorts in alphabetical order.

    I’ve thought that there is a preference that is not set in the right way, but I’ve not found anything.

    I don’t understand which could be the difference.

    Sorry for my English and thanks to all that will suggest a solution
    Mario

  • Avatar wanderer says:

    Did you set the field index to unicode ?

  • Avatar EddyR says:

    Hi,

    Hereby an observation that may be of help for people who don’t get this to work, dunno…

    I tried this solution in a file open on a FileMaker Pro Server (Macintosh 10.8.5, FileMaker Pro Server 12). Didn’t work, lost more than a couple of hours. Then I noticed that the entries weren’t Unicode sorted at all (caps had no impact) although Unicode resorting had been selected. I didn’t want to work on the original file to investigate what was going on, so I took a fresh back up of the same file from the Server and opened it locally (Yosemite) with FileMaker Pro (no Server). Much to my surprise, the Unicode sorting in the back up file was set back to ΓÇÿDefault sortingΓÇÖ and when I turned Unicode sorting on again, everything worked like a dream.
    Loaded it up again to the server and although Unicode sorting remained on, the list was not resorted. Loaded it up to a Windows computer with FileMaker Pro Server 13 and Unicode sorting was set back to ΓÇÿDefault sortingΓÇÖ but after selecting Unicode sorting again, it worked!

    Now whether this is OSX 10.8.5 on the server versus Yosemite on my Mac (where the Unicode sorting worked in the local file), OSX 10.8.5 versus windows, FileMaker Pro Server 12 versus FileMaker Pro Server 13, I donΓÇÖt know but I was able to reproduce all the phenomena described above several times.

  • Avatar StephenW says:

    Thanks very much EddyR (and of course Marcelo for the original hack).

    I am having what seems to be a very similar problem to the one you describe. The solution did not resort the value list when opened on a client (Windows 7, using Filemaker Pro 13) and when I downloaded a copy of the file from the server, found the sort order reset to Default. Excited by the content of your post, I reset it to Unicode, and the sort order worked on the downloaded copy. Uploaded again to the server (with Filemaker Pro Server 12) – now the sorting appeared blank (not default) but I reset it to Unicode .. and sorting no longer works on the served file.

    Any more thoughts would be gratefully received – could be using Server 12 not 13?

  • Avatar Mike Scott says:

    Looks like a great idea but unfortunately doesn’t work with Filemaker 12 on Windows 7.

  • Worked perfectly for me. Nils function is good too. I’m using on FileMaker 13 server running on Yosemite server OS with Yosemite clients running FileMaker Pro 13

  • Avatar D Eitner says:

    I tried this in FM Pro 13 but could not discover where to set up the custom function (searching online, it appears this is a FM Pro Advanced feature). But I think I have come up with a simpler and more universal solution to the same problem.

    Everything I’ve done is similar to what the OP did, including the Value Lists sorted as Unicode, etc.) but to actually get the unicode BOM character in I used this simple one-liner in the Custom Field:

    Substitute ( 10^Order – 1; 9; Char(65279) ) & StatusName

    I got the idea from the excellent Adventures In Filemaking #2 by Phil Caulkins, but where his inserts blank spaces into a standalone field used only for sorting, mine inserts the unicode BOM characters and appends the value in the StatusName field so I can use this new calculated field as the source for my Value List.

  • Avatar Louise Nadon says:

    I just downloaded the demo file provided by Marcelo in a link above to try to understand this technique but instead of getting the expected sort order reported above:

    Quote
    Negotiating Price
    Backordered
    Confirmed
    Shipped
    Delivered
    Paid
    Returned
    Overdue

    I get:

    Back ordered
    Confirmed
    Delivered
    Negotiating Price
    Overdue
    Paid
    Quote
    Returned
    Shipped

    This corresponds to the alphabetical order and not the custom sorted order. I did check that Unicode is specified and made absolutely no changes to the demo file. Any ideas what’s happening? I’m sure it’s something silly but can’t figure it out. Thanks

  • Avatar Louise Nadon says:

    Although as mentioned in my July 15th post, the demo file didn’t sort correctly, when I tried the solution in my database it worked like a charm…on my MacBook running Filemaker 11.04 under Yosemite. However, others in my group work on PCs and instead of the invisible characters, one of them mentioned seeing the little boxes that several people have mentioned here. I checked this out on a Windows XP computer and sure enough I saw the little boxes. Does this work only on Macs or only on some Windows-based operating systems but not others. It’s a very elegant solution to a common Filemaker problem and I would really like to use it. Thanks in advance for any advice.

  • Avatar Scott M, Toronto says:

    Marcelo: this tip popped with a search just now; it’s right on target. Much thanks for sharing it.

  • Avatar Sam C. says:

    Great tip. Thank you Marcelo. I did, however, notice a possible issue with portal filtering…

    I have drop-down text field (call it ‘type’) that is populated using the technique in this article.

    I perform portal filtering on that field (e.g. If isEmpty(‘someglobalfield’) or PatternCount(‘someglobalfield’ ; ‘type’)

    I found that the filter returned 0 items until I made the ‘type’ field a stored calc in which the actual calculation was to ‘substitute’ a plain old null string for Char(65279).

    Has anyone else found this to be the case, or am I doing something incorrectly ?

    Thanks !!!

  • Avatar LPetri says:

    A couple of questions:
    1) If my “value” has a number in it, it does not sort correctly. For instance in the example above if one of the StatusName was say, “Confirmed 10-4″, it would be sorted at the end. I assume it things the order is 4104 rather than 4. Is there a way to get around this?
    2) Is there a good way to hide the little boxes from showing in preview mode for printing a PDF?

  • Avatar Scott Bradley says:

    Love this fix, thank you very much. If a record midway through the StatusName field is left blank the final selection result is a ?. Any ideas of how to remove the ? and leave the choice from the dropdown as a blank field?

  • Avatar rochard says:

    Brilliant as always, Marcelo. Thanks.

  • Avatar joel stoner says:

    Have been using this BOM technique with success, but just loaded FMS15, with with WebDirect, it appears that menus created this way are displaying the Byte Order Mark, but in the form of “feff” for each instance. The resulting menu looks like this:

    feff9
    fefffeff8
    fefffefffeff7
    fefffefffefffeff6
    fefffefffefffefffeff5

    Can someone else confirm they’re seeing this too? (still works fine in FMP15 client)

    Not very ‘eff-ing cool! I hope this is a WebDirect bug with a quick fix!!

  • Avatar Joel Stoner says:

    I’ve heard back from FMI that they’re able to replicate this issue, but (of course) no promise as to if/when they’ll fix it. Leaves be debating if I should un-do our sorted menus, search for yet another workaround to implement all over the solution, or cross fingers that there’ll be an early update with a fix… Thoughts anyone?

  • Avatar John Funk says:

    This is a great solution for Value lists, but when you use the data from the value list in an ExecuteSQL script FMSQL does not work. I came up with a solution to “strip” the BOM from the data right before the query:
    Set the value you want to use right before executing FMSQL.

    $FieldStripped=
    Char (
    Left(
    Code ( $Field);
    Length ( Code ( $Field ) ) – (5* PatternCount ( Code ( $Field); “65279”))
    )
    )

    Note: 65279 is the added byte used in this original sample, if you use something else just have the calculation get he length of the BOM with Length(Char($BOM))

    This saved me from aborting a 6+ hour upgrade for my client.

    Thanks

  • Avatar Damon Casey says:

    I’ve found a problem with this technique in FileMaker 14 and 15 when a file is hosted. The first character is removed after a value is selected from a value list. If the selected value is Zebra and there is one non-breaking space in front of it, the length of the field afterwards is 5, not 6. It does work correctly when the file is opened locally.

    FileMaker 14’s WebDirect does work correctly but I can’t test in 15 because of the issue Joel Stoner has reported above.

    I’ve reported this to FileMaker on the Community site, including an example file, here:
    https://community.filemaker.com/message/589014#589014

  • Hello – I am trying to get your formula to work, but am not having any luck. Will this allow value lists greater than 100 entries long? If yes, you have done me a huge favor.

  • Avatar netguru says:

    Yes, you can. You need to represent the sort order as a number to base 4 and add leading zeroes to pad the width to, say, eight “quartal” digits, and then replace the digits 0…3 as follows:

    0 -> Char(8203) # Unicode 200B, zero-width space
    1 -> Char(8204) # Unicode 200C, zero-width non-joiner
    2 -> Char(8205) # Unicode 200D, zero-width joiner
    3 -> Char 65279) # Unicode FFEF, Byte order mark (BOM)

    With eight characters as the start of your list, the maximum number of members of your list is then

    33333333 (base-4) = FFFF (base-16) = 65535 (decimal)

    This method also avoids the limitation that is posed by the possible presence of characters beyond the BOM in the Unicode set (for instance “full-width Latin” characters that may be present in Chinese or Japanese text).

  • I just noticed that it also works in FileMaker 11 on Windows Server 2018,
    or FileMaker 16 on Windows 10 with Char ( 847 ).

    However, you must (logically) select the Unicode index for the sort field on FM16.
    But funnily, not for FM11 (or is this due to the Windows versions?).

  • How about this basic one?

    Let ( [
    ~s = sorter +1 ; // “sorter” is my “sort”-field
    ~f = field // this is what I want to see in my value list
    ] ;

    substitute ( ( 10^( ~s ) ) -1 ; 9 ; Char ( 847 ) ) & ~f

    )

Leave a Reply