This site uses tracking cookies. By using this site, you agree to our Privacy Policy. If you don't opt in, some parts of the site might not function.
Home / Blog / FileMaker / Extending FileMaker Pro’s value list sort capabilities using the Char() function
11Sep 2012

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

About the Author

Marcelo Piñeyro Marcelo Piñeyro

Marcelo is a 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.

Comments (55)

Daniel Wood - September 11, 2012

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

Nils Waldherr - September 12, 2012

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.


Marcelo Piñeyro - September 12, 2012

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.

Joel Shapiro - September 12, 2012

This is very cool, Marcelo! Thanks for sharing.

john renfrew - September 14, 2012


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

Kevin Frank - September 14, 2012

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

Matt Petrowsky - September 14, 2012

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!

Rob Russell - September 14, 2012

I love simple solutions. Very nice.


Ray Santangelo - September 17, 2012

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

raybaudi - September 23, 2012
Mislav - September 24, 2012

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 )


Joel Shapiro - September 27, 2012

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?

raybaudi - October 3, 2012

“@raybaudi: are you using XP?”

Yes, Joel. 🙁

Howard Schlossberg - October 4, 2012

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.

Andrés López Pascual - November 7, 2012

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!

Andy Hibbs - November 28, 2012

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.

Michael Kupietz - February 2, 2013

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 .

Jason Tallman - February 7, 2013

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.

Nigel Browning - February 9, 2013

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.

Chris Cain - March 17, 2013

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.

Teresa Kenyon - April 10, 2013

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.

Jonathan Fletcher - June 10, 2013

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!

Oreste - August 2, 2013

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



Make sense?

pixi - August 27, 2013

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

Bruce Robertson - August 30, 2013

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

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

Actually char() handles more than one character.

himagine - August 31, 2013

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 ) & ” ) & “\”\”” ) )

Joe - September 18, 2013

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!

wader2 - October 7, 2013
TonyC - November 16, 2013

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.

Jeff Drake - September 10, 2014

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.

Steve - September 17, 2014


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?


wanderer - September 23, 2014

If your number is lower than 100 (the list shows max as 11), Sort Order expression is

marbel911 - October 29, 2014

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

wanderer - November 19, 2014

Did you set the field index to unicode ?

EddyR - January 23, 2015


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.

StephenW - May 10, 2015

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?

Mike Scott - May 19, 2015

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

Jeffrey R Gorman - May 29, 2015

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

D Eitner - May 31, 2015

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.

Louise Nadon - September 2, 2015

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:

Negotiating Price

I get:

Back ordered
Negotiating Price

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

Louise Nadon - September 2, 2015

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.

Scott M, Toronto - September 2, 2015

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

Sam C. - September 2, 2015

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 !!!

LPetri - November 2, 2015

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?

Scott Bradley - November 8, 2015

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?

rochard - December 4, 2015

Brilliant as always, Marcelo. Thanks.

joel stoner - May 19, 2016

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:


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!!

Joel Stoner - June 3, 2016

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?

    JonJ - October 24, 2017

    Similar problem in WebD on FMcloud. However, using the Char ( 8203 ) seems to work.

John Funk - July 22, 2016

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.

Char (
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.


Damon Casey - July 27, 2016

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:

Peter Gerlings - October 19, 2016

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.

netguru - January 10, 2018

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).

Fabrice Ricker - October 30, 2018

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?).

Fabrice Ricker - November 6, 2018

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