Aren't you confusing field names and field values?
The way fields are sorted in the manage database has NOTHING to do with sorting records. It's like saying I want my Excel spreadsheet rows sorted by a column in Excel the say exact way the Excel spreadheets are sorted in way in a folder on the computer. Apples and oranges. Has nothing to do with performance.
You can sort your FileMaker records by any of the fields you have in the given table. There is no need for a calculation.
Please post a picture of what your layout looks like so we can undrestand better what your issue is.
1 of 1 people found this helpful
I tried your unicode idea & achieved the result you want. I think that is your answer & perhaps there is something else getting in the way. Have you tried this sort method in a non-portal field? That is what I tried 1st & it worked - I'd be curious what result you would get.
Performance really isn't an issue. A stored and indexed calculation will sort just as fast as a stored/indexed non-calculation. The only slow down with be when the value in the field changes and two indexes are updated, rather than one, but that's unlikely to be noticeable to human beings.
You're right, something else must be getting in the way. I don't know what... Your example does what I want. Thanks.
The difference between your example and my file is that I'm using uppercase rather than lowercase. In your example file, if values in the related table are e.g. A, B, _A, _B, then the underscore values go to the end.
Ok - that is interesting - I entered capital letter versions of the same six original values & when it sorted I got this:
Curious... I do not know what to do at this point, but hopefully this has helped clarify what it is you're after (getting a field to sort on a layout in the same way fields themselves are sorted when managing a database) to someone more knowledgable &/or creative.
It is sorting based on the Unicode character table. See www.unicode-table.com/en/#control-character for reference.
to test place a $ in front of the A, like so $A.
Adding "sort by" field as Lower(theField) may be the simplest.
But field name is ignoring case, so there never be both 'A' and 'a', but your data may be.
Do you need case sensitive sort ?
One record at a time, the cost of adding a calculation field isn't noticible. But since I'm creating/modifying large numbers of records on the fly, there is a performance hit of about 6% when I add a calculation field (for the purpose of fixing sort).
I don't need a case-sensitive sort, but Unicode seems to be the only way to keep underscores together. (That's better than ignoring them, but I'd like them to sort to the top.)
In e.g. FMP Manage Fields, OSX Finder Windows, and Excel spreadsheets, underscore sorts above uppercase and lowercase alpha (which sort case-insensitively). I've come to think of that as normal/standard sorting. It's bizarre that FileMaker doesn't support sorting data in that way.
1.Create value list from the field to sort with 'Include all values' and 'Re-sort values based on English'
2.Sort records with 'Custom order based on valuelist' using it.
Wow, that actually works! (It even works without resorting values in the value list.) Thanks so much for your help!