Apex Code Development You need to sign in to do that
Don't have an account?
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:
So a typical Account hierarchy would look like this:
And here's the aforementioned Aggregate queury:
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?
Thanks!
-Greg
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:
- 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.)
- 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:
And here's the aforementioned Aggregate queury:
List<AggregateResult> lastHierarchy = [SELECT
Ultimate_Parent_Account_ID_Stamp__c,
MAX(Last_Contacted__c) lastC
FROM
Account
WHERE
Ultimate_Parent_Account_ID_Stamp__c In :initialAccounts
GROUP BY
Ultimate_Parent_Account_ID_Stamp__c];
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?
Thanks!
-Greg