+ Start a Discussion

SOQL Grouping by String field - Need it to be case sensitive

Hey everyone,

I have batch code that's designed to find the last logged task out of every Account in an entire Account's hierarchy. To do this I did the following:
  1. I created a custom Text field on the Account object called Ultimate Parent Account ID Stamp that simply contains the ID of the ultimate parent Account in the hierarchy. (The ultimate parent Account itself contains its own ID in the Ultimate Parent Account ID Stamp field.)
  2. In the batch code I created an Aggregrate SOQL queury that's grouped by the Ultimate Parent Account ID Stamp field and finds the latest Last Contacted date from all the Accounts in the grouping

So a typical Account hierarchy would look like this:

User-added image

And here's the aforementioned Aggregate queury: 
List<AggregateResult> lastHierarchy = [SELECT
                                          MAX(Last_Contacted__c) lastC
                                          Ultimate_Parent_Account_ID_Stamp__c In :initialAccounts
                                       GROUP BY

The problem I'm running into is that the Ultimate Parent Account ID Stamp field is a text field and not some type of ID field. IDs can have the exact same characters, but with different capitalizations. So APEX treats IDs as case-sensitive and Text as case-insensitive. So if there's a bunch of Accounts with an Ultimate Parent Account ID Stamp of 001A000000uMLPs and another bunch of Accounts with an Ultimate Parent Account ID Stamp of 001A000000uMLPS, they'll all be grouped together in the queury even though the "S" is capitalized in one ID and not in the other.

The reason I used a Text field for Ultimate Parent Account ID Stamp is so that the ultimate parent could contain its own ID. If I used an Account lookup field instead, then the ultimate parent couldn't lookup to itself. So I used a Text field. But then I noticed the above issue.

So my question is: Is there a way to make the Aggregate grouping text field case-sensitive somehow? If not, do you know a better way to handle what I'm doing?