[SalesForce] Find if records exist with custom field comparing and as little SOQL as possible

I'm writing a trigger that needs to create a record based on a newly inserted one. The trigger I have now works, but only for small amounts of records. The problem is where I need to check if there is already an Account with the same FirstName, LastName and PersonEmail as the inserted one. Currently I just query all accounts for any matching all three fields, but obviously this results in a lot of SOQL queries when the trigger is called with lots of new objects. Is there a way to remove the need for so many SOQL queries?

Best Answer

Rather than performing the query for each record that comes into your trigger - which is what it sounds like you're doing. What you need to do is perform 1 much broader query that gets back all of the likely candidates for duplicate records. Then do the duplicate processing in Apex using a composite key.

For example:

// Build up your query clauses
Set<String> firstNames = new Set<String>();
Set<String> lastNames = new Set<String>();
Set<String> emails = new Set<String>();

for(Contact c : Trigger.new)
{
    firstNames.add(c.FirstName);
    lastNames.add(c.LastName);
    emails.add(c.Email);
}

// Get all potential duplicates
List<Contact> potentialDuplicates = 
[
    SELECT Id ... 
    FROM Contact 
    WHERE 
        FirstName IN :firstNames 
        AND LastName IN :lastNames 
        AND Email IN :emails
];

// Create a map based on a composite key
Map<String, Contact> duplicateMap = new Map<String, Contact>();

for(Contact c : potentialDuplcaites)
{
    String key = c.FirstName + c.LastName + c.Email;
    duplicateMap.put(key, c);
}

// Look for duplicates
for(Contact c : Trigger.new)
{
    String key = c.FirstName + c.LastName + c.Email;
    Contact duplicate = duplicateMap.get(key);

    if(duplicate != null)
    {
        // Record is a duplicate, so whatever you need to do
    }
}
Related Topic