SUMMARY:
Salesforce administrators and users can resolve frustrating column sorting errors in reports, which occur because the platform sorts custom formula fields using the HYPERLINK() function by the full URL rather than the visible link text, by implementing a specialized formula workaround that injects the display field name into the URL path.
- Salesforce defaults to sorting hyperlink formula fields by the entire URL string (e.g., https://…) instead of the human-readable link label, resulting in confusing report ordering.
- The issue must be fixed using a workaround because Salesforce currently has a limitation where sorting occurs on the URL itself.
- The core solution involves restructuring the
HYPERLINK()formula to embed the desired display field (e.g.,Account__c.Name) into the middle of the URL path, effectively forcing the URL to carry the sortable display value. - The flexibility of Salesforce URLs allows this workaround because the platform is forgiving of the object name within the URL path, recognizing the record object based on the record ID’s first three characters.
This reliable technique ensures that column sorting reflects the logical display name, providing an easy administrative fix that overcomes a common platform limitation.
Table of contents
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:
Tips for Working with Hyperlink Formula Fields
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!