9

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

enter image description here

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|heim@xe302.elastify.eu|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:

enter image description here


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.

0

This is SObject default sorting. As covered in that article, you can write a wrapper and implement Comparable to apply your own sorting (just using basic String comparison for the names will do what you want here).

UPDATE:

According to Uwe's testing of using a formula field that wrapped the Name, and using the formula field for sorting in the UI, this did not resolve this issue for him.

5
  • The suggestion to use a formula field does NOT work. See screenshot in my question at the bottom in the section Update 1. Since I'm not using and not want to use Visualforce, the first solution is also not applicable. I would need something to work on the standard UI. The workaround with the formula field was exactly what I was looking for - if only it would work. – Uwe Heim Dec 14 '20 at 17:41
  • Dang. Sorry to hear that. I'll remove that from my answer. (Thought I had worked around it that way once, but must have misremembered.) – Phil W Dec 14 '20 at 18:03
  • @UweHeim I'm wondering if it is a locale issue...? I'm sure I've had the lists "correctly" ordered before (but have also seen weird ordering too, like you show). – Phil W Dec 14 '20 at 18:09
  • my locale is DE (de_DE) – Uwe Heim Dec 14 '20 at 18:10
  • @UweHeim have you tried a slightly more complex formula, like: "" & TEXT(Name) (to try to force conversion into a standard string)? Also, what if you temporarily set your locale to en_US? Does that change the behaviour? – Phil W Dec 14 '20 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.