The ordering mechanisms in Salesforce are really odd.
Take any SObject having any Text field (e.g. Name on Account) and create some Records. Do that on an Org with no other Records of the type (e.g. a brand new Developer Org). You can do that manually or go to a place where you can "Execute Anonymous" and run this APEX
Account[] accs = new Account[]{};
accs.add( new Account(Name='1') );
accs.add( new Account(Name='2') );
accs.add( new Account(Name='a') );
accs.add( new Account(Name='b') );
accs.add( new Account(Name='c') );
accs.add( new Account(Name='d') );
accs.add( new Account(Name='!b') );
accs.add( new Account(Name='_b') );
accs.add( new Account(Name='__b') );
accs.add( new Account(Name='__c') );
accs.add( new Account(Name='$$b') );
accs.add( new Account(Name='$$$$$$$$$$$$$$$$$$$$$$$b') );
accs.add( new Account(Name='$$1') );
insert(accs);
It's only important that you have any Records on any objects having these values in a any field. Now go to a list view and sort by this field. You will get something like
These aspects are is really very very odd
- Numbers are sorted AFTER letters. Almost every other system is sorting Number FIRST.
- Non-alphanumerical chars like _ or ! or $ or anything else you try will be "kind of" IGNORED in the sorting! The first alphanumerical char will determine the sorting. I would have expected that the _ or $ moves the record either top-most, or bottom-most, BUT NOT INBETWEEN.
Exactly the same ordering you will get by APEX
Account[] accs = [ select Name from Account order by Name ASC ];
string out = '';
for(Account a : accs) {
out += a.Name +'\n';
}
system.debug(WARN,out);
40.0 APEX_CODE,WARN;APEX_PROFILING,WARN;CALLOUT,WARN;DB,WARN;SYSTEM,WARN
15:50:05.361 (361990842)|USER_INFO|[EXTERNAL]|0050Y000001DPSm|[email protected]|Mitteleuropäische Zeit|GMT+01:00
15:50:05.361 (362035000)|EXECUTION_STARTED
15:50:05.361 (362043935)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex
15:50:05.361 (386763598)|USER_DEBUG|[593]|WARN|
a
!b
$$$$$$$$$$$$$$$$$$$$$$$$$b
$$b
__b
_b
b
__c
c
$$1
1
2
15:50:05.361 (386962210)|CODE_UNIT_FINISHED|execute_anonymous_apex
15:50:05.361 (386993160)|EXECUTION_FINISHED
EXPECTED BEHAVIOR:
Something like this, I would see in other systems (like Excel, Windows, whatever ....)
!b
$$1
$$b
$$$$$$$$$$$$$$$$$$$$$$$$$b
_b
__b
__c
1
2
a
b
c
QUESTION:
Is there any way to get rid of this strange sorting and get something more like people would expect it to be? I'm sure this is by design and considered as "feature" by someone, but IMOH this is just messing things up to get some kind of meaningful ordering of records.
Also workarounds (even dirty workarounds) would be welcome!
UPDATE 1
I've tried the suggestion by PhilW to create a formula field and sort the list by that. It does NOT work. The ordering is exactly the same. See here side by side Name and the formula:
UPDATE 2
Current Workaround (I HATE IT but it works):
I've created a new field text(255) and a trigger containing that
if((trigger.isInsert || trigger.isUpdate) && trigger.isBefore) {
for(Account item : (Account[])trigger.new ) {
item.helperField__c = '';
for(String c : item.Name.split('')) {
String injection = 'Z';
{if(false) {
} else if(('#_?:.,!%$§&/()={[]}*~').contains(c)) {
injection = 'a';
} else if(('1234567890').contains(c)) {
injection = 'A';
}
}
item.helperField__c += injection + c;
}
}
}
Then I sort by this field.
