Comparing fields between two sObjects

Question

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?

Answer 1

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...

Answer 2

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;