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.

Improve this question
1
  • 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
    Commented Apr 21, 2014 at 5:43

1 Answer 1

Reset to default
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.

Improve this answer
3
  • Interesting idea, but don't think it will work in my case. This is part of a managed package. I don't think you can package autonum fields on standard objects: success.salesforce.com/ideaView?id=08730000000BqC8AAK And even if you could, I don't think there's a way to cause existing Campaign records in installers' orgs to be retroactively autonumbered after installation of the package.
    – mscholtz
    Commented Apr 21, 2014 at 6:28
  • Above link is 5 years old. Can you please try and check if its still true? also there is way you can run code post installation. salesforce.com/us/developer/docs/apexcode/Content/…
    – AtulRajguru9
    Commented Apr 21, 2014 at 11:09
  • Check this solution where you add text field first, then updated the data and convert it into auto number. help.salesforce.com/HTViewSolution?id=000005315 (not sure if you can change the datatype in managed package )
    – AtulRajguru9
    Commented Apr 21, 2014 at 11:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .