So here's the deal. I have a somewhat complex formula field that returns a Campaign ID. Then I have a SOQL query in apex that needs to ORDER BY this field. The problem is that ORDER BY is case insensitive, and formula fields by default return the 15 char ID. (http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_orderby.htm)
Result: if you have two campaign records whose 15-char IDs differ only in character cases, the sort does not work as expected. This actually cropped up in an installation, took me forever to troubleshoot it.
I thought the fix would be easy. Just wrap my formula in CASESAFEID() and we're good, right?
But not so fast. There's something screwy with that function. My formula before adding CASESAFEID was about 1300 chars according to SF. (Which is crazy, it's nowwhere near that long and doesn't reference any other formulas, just references related fields via Parent.Field notation. Basically I have no idea how SF gets their character counts on formulas.)
After wrapping my formula in one CASESAFEID() function it comes out to over 24,000 characters. Why? I have no earthly clue. (Is this a bug? Should I submit a case?)
So what's the workaround? I could write a trigger to save the 18 char ID I want to sort on to a text field, but I'd really like to avoid that if possible. Anyone see any other way out?
This is part of a managed package.