2

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.

  • One thought I had would be to create a new formula that concatenates the 15 char ID with other data from the campaign, like the name & the creation timestamp. Then sort on that. That would make it very very unlikely that any two records would have the same value in the field, although I suppose not literally impossible. – mscholtz Apr 21 '14 at 5:43
1

This is why i hate the idea of using ID for sorting. I would strongly suggest you to create auto-number field on campaign and use that for ordering.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.