What's the default language under the "Storage" tab for the field used in the value list? Same question goes for the value list itself.
If they're both English then I'm out of ideas. If they're not, set them to English and see if it helps.
Heck, switch it to Unicode and see if it changes anything...
Hi, Perren…thanks for responding. That was a good idea, so I tried it out…
In addition my setup, which was disabled "sort by":
I tried all 3 other possible choices:
• Re-sort values based on: "English"
• Re-sort values based on: "Unicode"
• Re-sort values based on: "Default"
Each of the four options I tested in FileMaker 13 did not return values in the same order as FileMaker 12.
FileMaker 12 ignores pre-pended non-alpha-numeric characters to values, but FileMaker 13 respects them
like it does in sorting fields, which I guess was an incongruity that was corrected with version 13.
So, maybe there's some custom function that strips that away for the menu display, or…well, any other ideas?
- - Scott
Just for fun check the field default language (guessing it's English):
Only other thing of note is this KB article: http://help.filemaker.com/app/answers/detail/a_id/11577
Which vRev of 12 are you comparing against? If it's 12v3 or greater then this is
a) intended and undocumented behavior change from 12
b) a bug with English sorting (maybe) you've found newly introduced in 13.
Reason I say *maybe* is that "*" is lower than "a" or "A" in ascii code land ( Code ( "*" ) vs ( Code ( "A" ) or Code ( "a" ) ) ).
What's the ruleset for English sorting of low ascii (yet visible) characters? Anyone from FMI listening to chime in? Bueller?
The default for disabled "Re-sort…" checkbox is always English on my system — ostensibly due to my system settings. Even if I enable "Re-Sort…" and set it to something else, then disable it, and "OK" my way back to the layout, when I open up the dialog again, it is disabled with "English" grayed-out in the popup.
We were all using FMP or FMPA 12.0v5 with Server Advanced 12.0v3 until the end of last week. After I set file options to require FMP 13.0 or greater, users began noticing the change to how value lists in the popup menu's were different. As you described, the leading asterisk as described in the FM kBase article is affecting the sort order in FileMaker 13, whereas in FileMaker 12 they are ignored.
So, I got to thinking…what if I create 3 new records in a dummy table, and see how FileMaker sorts them in the table view:
I got the same result displayed above from both FileMaker versions 12 and 13, and this IS the way I want the value list to display! So why is FileMaker 13's default behavior sorting the values in the popup differently? I've also tested this with the actual field data in the base table occurence, and it sorts as I want, but I'll try blowing out the index to see what happens…
Message was edited by: mr_scott (added that test was also done on actual data file)
Good luck with that, guessing it's not gonna help.
This does beg the issue of text fields having 2 indices - value index (used by the relationship graph) and word index (used by finds and sorts, so says the dialog above).
Which one do value lists use?
I've got no idea off the top without digging around, but I would have guessed the word index myself. Only makes sense due to them giving us default language options.
You've got a bug my friend. I'd file a ticket on the support forums and see what sticks over there...can't think of a single hack to mask it in the meantime.
Ah…Here's the Index, and it is sorted like FileMaker 13 sorts my popup values (not the way I want!):
The index is accounting for the leading special character (asterisk, in this case) when sorting. I also tried the "Re-sort" options here to see how much changed (a few lines moved, but it did not correct the unwanted sort behavior).
Well, that answers that. Value lists use the value index, not the word index. I should have guessed as much with the common word of "value" and all...duh.
Question now is what does that dialog show in 12? That'll tell us if it was a bug or is now a bug.
Can you peek real quick?
Sorry… I forgot to mention that "View Index" sorts the same way in 12.
workaround: use a nuber field to specify sort order.
use a calculation field (stored text UNICODE index) below as second field in value list definition.
Let ( [
$n = sortorder ;
$len = 10
Evaluate ( Let ( $i = $len ; Substitute ( 10^$len - 1 ; 9 ; "Char ( 8203 + Mod ( Int ( $n / 3^Let ( $i = $i - 1 ; $i ) ) ; 3 ) ) & " ) & "SecondField" ) )
$len means from 0 to 3^10-1 is allowed in sort order.
That's an interesting approach…I just wonder how much overhead that may add over the WAN, but you did say "stored text UNICODE index".
I am hoping that FileMaker will restore the behavior that it used to have - one that is consistent with the default ASC/DESC sorting behavior on text fields, so that workarounds won't be necessary.
I thank you for taking the time to respond, and will reply with my test results.
- - Scott
Ran into a similar problem with one of our own solutions and we were able to resolve it by establishing a custom sort order. Would that work for you?
I believe that is exactly what user19752 proposed - to create a calculated field on which to sort. I'll give it a whirl later today, thanks.
- - Scott
PhilModJunk in the FileMaker customer support forums has created a file that reproduces the issue on Windows as well!
Details are here: