0

I'm trying to query for all unique accounts that has a first name, last name and person email by using this query:

List<AggregateResult> checkUniquePersonAccounts = [
SELECT count(Id), count(FirstName), count(LastName), PersonEmail
FROM Account
WHERE IsPersonAccount = true
GROUP BY PersonEmail
HAVING count(Id) = 1
AND count(FirstName)= 1 
AND count(LastName) = 1
];

Obviously this fails if there are more than 2000 records (as that's the limit on records turned by the aggregate function)

What's my workaround, seeing as we've got well over 15k person accounts in the org.

Thanks in advance

  • What's your end objective with this data? – David Reed Dec 10 '19 at 17:40
  • Incoming cases should be linked to a person account only if the case information that comes in (name and email) exists on one, and one account only – erikvm Dec 10 '19 at 17:43
  • Then you can obviously add to your WHERE clause and only look at records which can match those values, no? – Adrian Larson Dec 10 '19 at 17:45
  • Well, I might get more than one result then? ...SELECT PersonEmail FROM Account WHERE PersonEmail IN :emails What if one of the emails in the cases that comes through returns two matches – erikvm Dec 10 '19 at 17:47
1

Based on your comment about the end goal here:

Incoming cases should be linked to a person account only if the case information that comes in (name and email) exists on one, and one account only

I think there's another way to approach this.

Suppose you have a list of Cases, List<Case> newCases. Each Case has an Email and a first and last name. You want to find those Person Accounts corresponding to the Email and Name, but only if they're unique.

First, process the Cases into three Sets: Set<String> firstNames, Set<String> lastNames, andSet emails`. Then, query for all potentially responsive Person Accounts:

List<Account> possibleAccounts = [SELECT Id, FirstName, LastName, PersonEmail
    FROM Account
    WHERE IsPersonAccount = true
          AND (FirstName IN :firstNames OR lastName IN :lastNames OR PersonEmail IN :emails)
];

Then, process this result list too, into a Map<String, List<Account>> keyedAccounts keyed on a composite key: the concatenation of the First Name, Last Name, and Email, separated by some delimiter like \n. For example, Mike Jones <mike@jones.com> would be stored in the List<Account> keyed under Mike\nJones\nmike@jones.com.

Lastly, walk through your Cases one more time, and for each one construct that composite key. Then, you have your answer as to whether it has one and only one corresponding Account: keyedAccounts.containsKey(compositeKey) && keyedAccounts.get(compositeKey).size() == 1.

This is not invulnerable to pathological cases (what if you have 50,000 guys named Carl, for instance?) but it's a pattern you can tailor somewhat to your org's specific data volume and structure and is likely to be more resilient than your current aggregate approach.

|improve this answer|||||
  • Hmm, thanks for the suggestion - not a big fan of concatenating like that - what happens if there are multiple accounts with the same first name + last name + email? I'd still get duplicates then.. – erikvm Dec 10 '19 at 18:29
  • @erikvm I thought that was the point: to determine where you have duplicates and select only those with exactly one matching Account. – David Reed Dec 10 '19 at 18:30
  • But ` AND (FirstName IN :firstNames OR lastName IN :lastNames OR PersonEmail IN :emails)` Doesn't that check if there is a firstname, a last name, OR a person email? – erikvm Dec 10 '19 at 18:52
  • Yes, it does. The last steps in the process I outline above cover how to use that data to figure out which Cases have exactly one match. – David Reed Dec 10 '19 at 18:53
  • Hm okay, maybe I misunderstood it a bit - let me try it out and I'll get back - thanks a lot for now – erikvm Dec 10 '19 at 18:54
0

SOQL Pagination may be your answer...

https://developer.salesforce.com/blogs/tech-pubs/2012/06/the-joys-of-soql-pagination.html

List<AggregateResult> checkUniquePersonAccounts = [
   SELECT count(Id), count(FirstName), count(LastName), PersonEmail
   FROM Account
   WHERE IsPersonAccount = true
   GROUP BY PersonEmail
   HAVING count(Id) = 1
      AND count(FirstName)= 1 
      AND count(LastName) = 1
   ORDER BY PersonEmail
   LIMIT 1000 OFFSET 1000
];
|improve this answer|||||
  • Well this isn't really something thats being rendered in a table or ui, so not sure how pagination helps here – erikvm Dec 10 '19 at 17:48
  • May need to use a batch process then to break up the query results into batches, that would consolidate the findings. developer.salesforce.com/docs/atlas.en-us.apexcode.meta/… – Paul N Dec 10 '19 at 17:54
  • 2
    Something worthy of note: OFFSET can only go up to 2000 – Derek F Dec 10 '19 at 18:02

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.