I have an Account hierarchy. I have a formula on my Account that returns the ID of the Account at the top of the hierarchy. Due to formula size restraints, I can't use CASESAFEID in the formula, so it only returns the 15 character ID. Return type of the formula is Text (String).

My use case is given one Account, return all the Accounts in its hierarchy. The problem is, I have a 15 character ID from the formula and SOQL is not case sensitive.

So if I do something like this:

Account a = [SELECT Id, Ultimate_Parent_Id__c FROM Account LIMIT 1];
String parentId = a.Ultimate_Parent_Id__c;
String query = 'SELECT Id, ' + Util.GetDynamicQueryString() + ' FROM Account WHERE Ultimate_Parent_Id__c =: parentId';
List<Account> results = Database.query(query);

This won't work because theoretically there could be multiple Accounts with that 15-character ID when it is processed case-insensitive.

Since SOQL can't compare the String values in a case sensitive manner, the only way I can think to do this is to write a trigger to stamp the 18 character ID of the ultimate parent on create or reparent.

Has anyone figured out a way around this?

  • 1
    I'd have to see your formula, but I suspect there'd be a way to optimize it to fit the casesafeid formula in. – sfdcfox Mar 27 '20 at 0:46
  • Thanks for responding. Formula is as follows. It's ugly as sin, but it works for the most part and our hierarchies are not more than 5 deep. IF(isblank(Parent.Id),Id, IF(isblank(Parent.Parent.Id),Parent.Id, IF(isblank(Parent.Parent.Parent.Id),Parent.Parent.Id, IF(isblank(Parent.Parent.Parent.Parent.Id),Parent.Parent.Parent.Id, IF(isblank(Parent.Parent.Parent.Parent.Parent.Id),Parent.Parent.Parent.Parent.Id, IF(isblank(Parent.Parent.Parent.Parent.Parent.Parent.Id),Parent.Parent.Parent.Parent.Parent.Id, null )))))) – Bob Hatcher Mar 27 '20 at 0:48
  • So, I was wrong, I tried creating a casesafeid version using the best optimization I could think of: casesafeid(BLANKVALUE( Parent.Parent.Parent.Parent.ParentId, BLANKVALUE( Parent.Parent.Parent.ParentId, BLANKVALUE( Parent.Parent.ParentId, BLANKVALUE( Parent.ParentId, ParentId))))) ... it was still too long about about 3k characters. You'll probably want to make a Process Builder to avoid using a trigger to populate the data. – sfdcfox Mar 27 '20 at 0:50
  • I was afraid of that. Thank you! – Bob Hatcher Mar 27 '20 at 0:52
  • I think I'm missing something - so long as you aren't destroying the case of the characters in a 15-character Id, it's perfectly valid for use in any Salesforce API. SOQL on case-preserved 15 character Ids works fine. Are your Ids clamped to upper or lower case? – David Reed Mar 27 '20 at 0:53

Browse other questions tagged or ask your own question.