+ Start a Discussion
TheCustomCloudTheCustomCloud 

Formula fields not sorting in related list

Using the following formula field:

 

HYPERLINK( '/' + Contact__c , Contact__r.LastName & ', ' & Contact__r.FirstName,'_parent')

 

When I sort the a related list by this formula field it does not correctly sort.  For example, Instead of Apple, Bob, Cat it sorts seemingly randomly like this.. Bob, Apple, Cat.

 

 

Anyone else encounter this?

sfdcfoxsfdcfox

The system is most likely sorting the fields correct. Since you're using HYPERLINK, that means that your formula is internally converted to something like this:

 

<a href="/00300000abcde">Alice</a>
<a href="/0030000012345">Bob</a>

Essentially, records will then be sorted by their ID value; we compare left to right when alphabatizing, and since the ID value comes to the left of the record's name within the string, the ID value will determine the sort order.

 

Standard lookup/master-detail fields do not suffer from this because they are programmed to sort by their display value and not the entire string. Unfortunately, formula fields do not enjoy this benefit. You won't be able to correctly sort based on a formula that uses HYPERLINK on salesforce.com ID values.

 

There's no real workaround to this problem directly. The best you could do would be something like the following:

 

Contact__r.LastName & ', ' & Contact__r.FirstName & ' (' & HYPERLINK( '/' & Contact__c, 'view' ) & ' )'

 

Anoop AsokAnoop Asok

Redfin/sfdcfox,

 

I just happened to face the same issue, and landed here while looking for a solution.

 

The solution mentioned by sfdcfox is a cool one, but I'm supposed to display the link with friendly text as "LastName,  FirstName" which forced me to search further.

 

Then I found out another workaround from one of the Ideas, and that's exactly what I was looking for.

 

HYPERLINK( '/' & Contact__r.LastName & ', ' & Contact__r.FirstName,'') &
HYPERLINK( '/' & Contact__c , Contact__r.LastName & ', ' & Contact__r.FirstName,'_parent')

This formula makes the field value a bit longer. But since the first hyperlink doesn't have any Friendly Text, it won't be displayed to the user on the UI. All the user will be seeing will be the second hyperlink.

 

As sfdcfox has mentioned in the last post, the HYPERLINK sorting mostly happens on the record id. The above formula will update the field value and it'll now begin with the Contact LastName and FirstName instead of the ContactId. I believe this is the best workaround available so far.

 

I know you guys had a discussion on this almost an year back, and probably this is no where in your active memory. Just updating this post hoping that someday someone might find it useful. :-)

 

Thanks,
Anoop