Indexing and SORTS

[LaRetta]

This ( seemingly ) inconsistent behavior has struck me as odd and I would sure like to understand the principle behind it.   I may have even been told ( or known ) the answer at one time but might have forgotten.  Using FMPA 14.0v6 on Mac El Capitan with brand new file with simple text field.  

Shot 1 is simple sort, ascending.
Shot 2 is value list of the field
Shot 3 is the field index COMMAND-I or CONTROL-I

So my question ... why does the field sort differently than the value list or the index?  I thought sorting used the index.  Thank you for considering my question!  

 

Edited December 9, 2016 by LaRetta

[Steve Martino]

Is one sorted by unicode?

[LaRetta]

Hi Steve, thank you for helping me out here.  :-)

No, the field was newly created in a new file as a text field and nothing has been toughed in its field definition.  I feel like I should know this answer but it is escaping me.  It is as if FMI used different rules for the sort than it does for the regular index!  Being you are on Windows, what do you get if YOU create a new file, new text field and enter the data as I've entered it and sort it?  And then create a value list and check it?  Then CTRL-I and what do you get?

Obviously (?), the sort is ignoring the underscore but why does it use different principle than value lists, which are supposed to sort alphabetical and different than the index, which is ... well, the index?

BTW, this behavior seems to hold back through version 11 than I have tested.  Why haven't I noticed this before?  I feel like I woke up one day and found out that gravity wasn't true at all!

Edited December 8, 2016 by LaRetta

[comment]

56 minutes ago, LaRetta said:

nothing has been toughed in its field definition.

First, it's worth noting that some things are included in the field's definition as default. In this case, the field's default language (found under Field Options > Storage > Indexing) has been set to English (or whatever the default language is on your system). You will see a different result if you change it to Unicode.

As to why it's happening, I can only offer a tentative explanation: it seems that when sorting English text (as well as text in many other languages), Filemaker will ignore punctuation that is not part of an actual word. Turn on the 'Show individual words' option when viewing the index to see which characters are dropped.

 

 

Edited December 8, 2016 by comment

[LaRetta]

Ah, the characters dropped appear to be same ones FM considers word break characters (I have not checked them all, just a handful of them).  For example, '$' is dropped in the sort as well.

In field definitions, sort by field name works as I would expect, putting underscored words first so I suppose FMI must handle text data differently, as you say, according to default language and based upon 'individual word index' as displayed when I 'Show individual words' when viewing the index.

I sure wish we had a function Get ( Index { show individual words } ).  It would come in handy!

Thank you for easing my mind a bit.  I dislike discovering 'oddities' which have been in front of my eyes for over 10 years but missed (or forgotten) by me! :-)