Introduction
The FileMaker char() function 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. Introduced in FileMaker Pro 10, 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. Because they are just characters, they can be formatted using the formatting tools available in FileMaker Pro. These include conditional formatting and the text formatting calculation functions.
An especially 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
Consider the following scenario:
- We have a dynamic value list composed of two fields (say an internal ID and its corresponding user-facing value).
- We want to store the values in the first field but only 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. (Yes, this is rather limiting.)
Fortunately, we can bypass this limitation by using char(65279). Here’s the process broken down in steps:
Imagine we have 2 tables, Order and OrderStatus. 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. Then, 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 )
)
)Create a Dynamic Value List
Now we just need to create a dynamic value list that displays the values sorted based on our custom order. (See 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 here:
Sorting More Value Lists in FileMaker Pro
There are other uses for BOMs, and reviewing some more of them may justify a follow-up post. So stay tuned! We hope we’ve simplified the process of extending FileMaker Pro’s value list sort capabilities.
Have questions? You can contact my team directly for more insights and help with your FileMaker application. Or, check out our other FileMaker posts to learn more about customizing your solution.
Genius!! You sir, have done what FileMaker have failed to give us in however-many-years value lists have been in existence, kudos!!!!!!!
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
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.
This is very cool, Marcelo! Thanks for sharing.
Brilliant
And Nils clever bit works up to 10^404-1
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!
I love simple solutions. Very nice.
r
Marcelo, you evil genius … you just made at least one person’s day over here (and many more, I’m sure). Thanks!
Maybe it’s me, but I see this on windows.
http://fmforums.com/forum/index.php?app=core&module=attach§ion=attach&attach_rel_module=msg&attach_id=18586
( many squares )
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 )
)
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?
“@raybaudi: are you using XP?”
Yes, Joel. 🙁
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.
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!
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/ .
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.
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.
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.
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.
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!
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?
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”.
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.
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 ) & ” ) & “\”\”” ) )
)
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!
How about to set font linking?
http://msdn.microsoft.com/en-us/goglobal/bb688134%28v=msdn.10%29.aspx
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.
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.
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
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))
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
Did you set the field index to unicode ?
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.
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?
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
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.
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
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.
Marcelo: this tip popped with a search just now; it’s right on target. Much thanks for sharing it.
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 !!!
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?
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?
Brilliant as always, Marcelo. Thanks.
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!!
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?
Similar problem in WebD on FMcloud. However, using the Char ( 8203 ) seems to work.
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
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.
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).
nice!
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
)
Pingback: 2-Column Magic Value Lists – FileMakerHacks
Hi!
I have a portal in which I am resorting the records and would like to see that order reflected in a Drop Down Menu, but that menu shows the records in an alphabetical order. Would your solution help me here?
Thanks!
I used to use a scripted routine to add the unicode padding, but since the introduction of WHILE, the current approach has been to use this auto-enter calc using a manually entered sequence field in a value list data table as the basis.
While (
[
output = “” ;
padding = “” ;
pad =Char (65279) ;
iterations = 0 ;
Order = Sequence
]
;
iterations < order
;
[
iterations = iterations + 1 ;
padding = pad & padding
]
;
padding & Text & " (" & Value & ")"
)
good job but there is a problem when the field sort is > 140