Hidden Hyperlink and the Secret Sorting

I was designing a multi-year Donations matrix report based on an April – March fiscal year, with close date by fiscal month as the column headers, and fiscal years as the rows. One difficulty here is that Salesforce doesn’t want to put close date in both the column and the row headers, so I first needed a way to have the month appear as the column header, so I used a formula suggested by Steve Molis to create a field on the Donation record that returned the month name. The problem then became that Salesforce wanted to sort the months alphabetically. April was first, which was great, but then came August.

I was able to move into ugly but functional territory by having the function preface the month name with a number to manage the sorting: 01 April, 02 May, 03 June, etc. Again, ugly, but functional. A developer solution, unfriendly to end users.

I would have probably left it that way, but then I encountered a variation on the same problem when I needed to sort monthly Campaigns in a matrix report based on their start month. In a matrix report, you’re limited to sorting by grouping fields, and I was already full-up, since I was using parent campaign as a first group, with child campaigns below that. There are plenty of messy solutions available, creating formulas to preface the campaign name with a month number, as above, or fuller date info as needed.

As I was searching the web for a solution, I came across a common complaint about hyperlink sorting — that they sort by the underlying html rather than by the displayed text. And along with it, a solution, using a second hyperlink with no text before the primary hyperlink, to force the links to sort by the text.

From this workaround comes a general solution for creating formula fields with a hidden sort embedded in a HYPERLINK function. For the campaigns, this looks like:

HYPERLINK("/?sort="& LPAD(TEXT(MONTH(StartDate)) ,2,'0'),"") & HYPERLINK("/" & Id, Name)

Which sorts by a two-digit month name return using that handy LPAD funtion, and still returns a clickable campaign name that takes you right to the campaign record. This one is just sorting by calendar month, not fiscal month – that would take a more complex formula.

For the month, using the remnants of my ugly but functional formula:

HYPERLINK ("/?sort=" & CASE(MONTH(CloseDate), 1, "10", 2, "11", 3, "12", 4, "01", 5, "02", 6, "03", 7, "04", 8, "05", 9, "06 ", 10, "07", 11, "08", 12, "09", "None"),"")  & CASE(MONTH(CloseDate), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "None")

In this case, prefacing the unlinked text month name with the hyperlink is resulting in a tooltip text showing the raw html when you mouse over the month name, but this stray gobbledy gook is much easier to ignore than having the month name prefaced by a two-digit fiscal month sort number:

UPDATE: See also Another Use Case for Hidden Hyperlinks