1

When creating a case and the following values are provided:

  • Web First Name,
  • Web Last Name
  • Web Email,

I wish to see if these three fields already exists on a person account (FirstName, LastName, PersonEmail) - if not - create a new person account.

I was thinking of using a map and concatenating the incoming cases to one long string and comparing it with existing accounts, as such:

Map<Id, String> existingAccounts = new Map<Id, String>();
Map<Id, String> newCases = new Map<Id, String>();

for (Case cs : casesReceived) {
    newCases.put(cs.Id, cs.SuppliedEmail + cs.Web_First_Name__c + cs.Web_Last_Name__c);
}

for (Account persAcc : [SELECT Id, FirstName, LastName, PersonEmail FROM Account WHERE IsPersonAccount = true]) {
    existingAccounts.put(persAcc.Id, persAcc.PersonEmail + persAcc.FirstName + persAcc.LastName);
}

But not entirely sure how to proceed - any ideas?

  • This sounds a bad idea - you will be loading lots of detail you don't care about from the (potentially very large) PersonAccounts. Surely you can just construct an SOQL to query the possible PersonAccount matches where at least one of the first/last/email values match values collected from the cases, then find exact matches from this data. – Phil W Dec 2 '19 at 15:59
  • Would you mind showing what that query would look like? – erikvm Dec 2 '19 at 16:24
2

To add to @DerekF's answer, if you are going to use a Key that isn't a string concatenation (this is quite an inefficient approach by the way) whilst having a Key that is immutable so you can't mess up your map, try adding an Apex class like:

public class MyKey {
    private List<Object> key;

    public MyKey(String first, String last, String email) {
        this.key = new List<Object>{first, last, email};
    }

    public Boolean equals(Object other) {
        if (other instanceof MyKey) {
            return this.key.equals(((MyKey) other).key);
        }

        return false;
    }

    public override Integer hashCode() {
        return key.hashCode();
    }

    public String getFirst() {
        return (String) key[0];
    }

    public String getLast() {
        return (String) key[1];
    }

    public String getEmail() {
        return (String) key[2];
    }
}

Whenever you add something to your map, do it with a MyKey instance. When you search in your map, do it with a MyKey instance.

This approach is safer because you cannot accidentally mutate a key value. It is also faster (in CPU usage) than the approach of concatenating strings, at least in our experience. For example:

Set<String> firstNames = new Set<String>();
Set<String> lastNames = new Set<String>();
Set<String> emails = new Set<String>();

for (Case case : cases) {
    firstNames.add(case.firstnameField);
    lastNames.add(case.lastnameField);
    emails.add(case.emailField);
}

Account accounts = [
        SELECT Id, FirstName, LastName, PersonEmail
            FROM Account
            WHERE FirstName IN :firstNames
                AND LastName IN :lastNames
                AND PersonEmail IN :emails
];

Map<MyKey, Id> accountIdsByKey = new Map<MyKey, Id>();

for (Account account : accounts) {
    accountIdsByKey.put(new MyKey(account.FirstName, account.LastName, account.PersonEmail), account.Id);
}

For each of your cases you can then simply look see if they relate to an existing account by:

Map<MyKey, Account> newAccountsByKey = new Map<MyKey, Account>();

for (Case case : cases) {
    MyKey caseKey = new MyKey(case.firstnameField, case.lastnameField, case.emailField);
    Id foundAccountId = accountIdsByKey.get(caseKey);

    if (foundAccountId != null) {
        // The case maps to an account and the foundAccountId tells you
        // which one. You might not need to do anything here
       ...
    } else {
       // No account for the case, so you need to handle that accordingly, e.g.
       Account newAccount = newAccountsByKey.get(caseKey);

       if (newAccount == null) {
           newAccountsByKey.put(caseKey,
               new Account(FirstName = case.firstnameField...));
       }
    }

At the end of this newAccountsByKey.values() are the account instances to be created, just doing:

insert newAccountsByKey.values();

If you don't care what the account IDs are, you could use Set<MyKey> existingAccountKeys instead of Map<MyKey, Id> accountIdsByKey...

  • Hey Phil, thanks for the explanation - one concern: If I create a case and a an account doesn't exist - it'll create an account and a case, but there won't be any link between them – erikvm Dec 3 '19 at 9:19
  • If you need a link - a lookup field - that's easy to accomplish. After inserting the accounts using the newAccountsByKey.values() they get IDs. These IDs are put in the Account instances in newAccountsByKey. So all you have to do is go through the cases and for each query the newAccountsByKey using a key for the case - you can then take the ID from the Account instance from the map and put it in the required Account lookup field on case. Remember to then update those Case instances and you're done. You may want to link a Case to a foundAccountId too - depends on your needs. – Phil W Dec 3 '19 at 9:49
  • Shouldn't that take place in the if (foundAccountId != null) part of the code? – erikvm Dec 3 '19 at 11:45
  • It depends on whether you need to connect cases to existing and/or new accounts - it is up to your requirement to drive where and when you do this. If only to new accounts then it would only be in the "no account for the case" flow, effectively. However, the processing must be done after you do the bulkified insert of newAccountsByKey.values() in a separate iteration over the cases, generating a separate list of updates to cases to be bulk updated into the database. I'm sure you can work that out for yourself, right? :) – Phil W Dec 3 '19 at 12:02
  • I do wish to connect the newly created cases to the newly created account, yes. Is the suggested approach to query the IDs in newAccountsByKey.values() and look for cases that matches the newAccountsByKey keySet? Just a bit confused with this new approach with using a custom key and all – erikvm Dec 3 '19 at 12:10
3

I think there are two issues to address here.

Issue 1, query selectivity

You'll want to make your query on Account more selective. Adding filters for FirstName, LastName, and PersonEmail into your query should do the trick. Just create one set for each, and populate while you iterate over the Cases.

This won't get you 100% of the way to your goal, but it does narrow down the number of records you need to try to search against. The thing with using multiple filters and binding collections to them is that the filter you're building is effectively the cartesian product of your collections.

firstNames = {'Derek', 'Erik'} and lastNames = {'F', 'Smith'} means you'll get records for "Derek F", "Erik F", "Derek Smith", and "Erik Smith" (if they exist).

Issue 2, what am I searching for?

With the information you've given so far, you don't care so much about the Id of an Account that matches your first/last/email combo. Rather, you care whether or not there is a match at all.

So, instead of a Map<Id, String> from Account to the concatenation of your fields, I'd suggest a Set<String> to simply store the concatenation itself. This will make your search lightning fast (given this case, was there any account that matched firstname + lastname + email?)

Putting it together

The pseudo-code might look like this

declare sets to hold firstname, lastname, and email (one each)

for(my cases){
    add the firstname to the firstname set
    add the lastname to the lastname set
    add the email to the email set
}

declare a set to hold the concatenation of firstname, lastname, and email from queried accounts

for([SELECT fields FROM Account WHERE firstname IN :firstnameset AND lastname IN :lastnameset AND email IN emailset]){
    add the concatenation into the set we just declared
}

for(my cases){
    String needle = concatenatation of your case fields;
    if(existingAccountSet.contains(needle)){
        // no need to make an account
    }else{
        // make an account
    }

}

Bonus

For relatively short and simple things like this, concatenating values into a single string should do the job just fine.

Another way to do this would be to use an SObject as a composite key. For example...

Set<Account> needleAccounts = new Set<Account>();
for(my cases){
    needleAccounts.add(new Account(
        FirstName = case.firstnameField, 
        LastName = case.lastnameField,
        PersonEmail = case.emailField
    ));
}

Normally, using an SObject as the datatype of a set, or the key of a map is discouraged (because if you change anything on the object instance in a set or the key of a map, you'll lose access to that data in the collection). If you never alter things after placing them into a set/map though (or are careful about using .clone()), then it can be fine.

The advantage here is twofold:

  • It doesn't matter what order you use for the "keys" of your composite object (unlike a concatenated string)
  • If it turns out that you need to insert a new Account, you already have a head start on creating that record

You could also eliminate the third loop by using the removeAll() method of the Set class.

declare 3 sets for your fname, lname, email

Set<Account> needleAccounts = new Set<Account>();
for(my cases){
    needleAccounts.add(new Account(
        FirstName = case.firstnameField, 
        LastName = case.lastnameField,
        PersonEmail = case.emailField
    ));

    populate your 3 other sets (as in the other example)
}

Set<Account> haystackAccounts = new Set<Account>();

for([SELECT fields FROM Account WHERE firstname IN :firstnameset AND lastname IN :lastnameset AND email IN emailset]){
    haystackAccounts.add(new Account(
        FirstName = account.firstnameField, 
        LastName = account.lastnameField,
        PersonEmail = account.emailField
    ));
}

needleAccounts.removeAll(haystackAccounts);

// Now, needleAccounts will only contain Accounts that need to be created
insert needleAccounts;
  • I dislike using SObject as a key since they are mutable, and if you mutate a key in a map you break the map. To avoid this I would use an immutable object that wraps the key and passes equals and hashCode through to the key. Even though the key is still effectively mutable, it cannot be mutated because it is encapsulated within an immutable object. – Phil W Dec 2 '19 at 16:43
  • NB: In terms of performance using an array of object within an immutable object that represents the key is ideal - this is MUCH faster than creating keys using string concatenation. – Phil W Dec 2 '19 at 16:46
  • @PhilW Yes, which is why I took the time to mention that. In this particular use case (creating new object instances and not storing them in any other variable), the danger of that is no more than that of using a String, Id, etc... – Derek F Dec 2 '19 at 16:47
  • Strings are immutable, as are IDs. – Phil W Dec 2 '19 at 16:51
  • But as mentioned, string concatenation for keys is much slower than using Array<Object> – Phil W Dec 2 '19 at 16:52

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.