One common frustration for Salesforce admins and users is how hyperlink fields behave in reports. You create a custom formula field with a hyperlink to a record or external site, and when you sort that column in a report, you expect it to sort alphabetically by the visible text (the link label). Instead, Salesforce sorts by the URL, leading to confusing and seemingly incorrect ordering.
In this post, we’ll explain why this happens and how you can resolve this issue.
What’s Really Going On?
When you use the HYPERLINK() function in a Salesforce formula, it generates a clickable link, but Salesforce stores and sorts by the full URL, not the link label.
For example: HYPERLINK(“https://example.com/page?id=” & Id, Name)
Although users see the record Name, Salesforce sorts the column by the entire string, starting with https://….
How to Fix It
A workaround to fixing this issue is to generate your Hyperlink using the format below:
HYPERLINK(“/lightning/r/” & DisplayField__c & “/”+ Id & “/view”, DisplayField__c )
So, for example, if you were trying to create a link to an Account, you would do:
HYPERLINK(“/lightning/r/” & Account__c.Name & “/”+ Id & “/view”, Account__c.Name )
Also, if there is a chance that your Sort Field is likely to have a forward slash character in it (for example, if the account name was “Coca-Cola / Atlanta,” it is better to go a step further and make the hyperlink formula replace it with a blank:
HYPERLINK(“/lightning/r/” & SUBSTITUTE(DisplayField__c,’/’,’’) & “/”+ Id & “/view”, DisplayField__c )
This will ensure that a forward slash character does not impact the eventual URL generated.
How Does This work?
Since we cannot address Salesforce’s limitation of sorting occurring on the URL itself instead of the display name, we ensure that we inject the display value into the URL without affecting where it points.
We can do this because Salesforce’s URLs are very forgiving of the object name when it comes to the URLs since it knows from the first three characters of the ID what Object the record belongs to.
So, for instance, for a URL like this:
https://mysalesforceorg.lightning.force.com/lightning/r/Account/0016O00003KyXGwQAN/view
You could replace the object name (Account in this case) with anything you like:
https://mysalesforceorg.lightning.force.com/lightning/r/RobotsAreAwesome/0016O00003KyXGwQAN/view
Both URLs are valid and will take you to the same account.
However, since we inject the display into our URL, it sorts correctly as well.
While it would be great if Salesforce corrected this issue and sorted it on the URL display, which is what most users would expect, it’s nice that the remarkable flexibility of Salesforce URLs allows us to provide an easy workaround!
If you want more useful tips on how to use the HYPERLINK function and its capabilities, please refer to Salesforce’s help article here:
Take the guesswork out of CRM with XTIVIA! Our certified experts are here to ensure a seamless implementation tailored to your needs. Plus, don’t miss out on our CRM Admin On-Demand service for ongoing support you can count on.
Reach out to us today and make your CRM dreams a reality!