Preventing Duplicate Records from Saving

Source code:

Problem

You want to prevent users from saving duplicate records based on the value of one or more fields.

Solution

If you can determine whether a record is a duplicate based on the value of a single custom field, select the Unique and Required checkboxes on that field's definition:

The Unique and Required checkboxes are only available on custom fields. If you want to check for uniqueness based on the value of a single standard field and your edition can't use Apex, you can also use the following workaround:

  1. Create a custom field with the same type and label as the standard field. Select the Unique and Required checkboxes on the custom field's definition page.
  2. Replace the standard field with your new custom field on all page layouts.
  3. Use field-level security to make the standard field read-only for all user profiles. This prevents any user from mistakenly modifying the standard field through the API, unless the user has the “Modify All Data” profile permission.
  4. Define a workflow rule that automatically updates the value of the standard field with the value of the custom field whenever the custom field changes. This ensures that any application functionality that relies on the value of the standard field continues to work properly. (For example, the Send An Email button on the Activity History related list relies on the standard Email field for a lead or contact.)
[note] Because this is a less-elegant solution than using Apex, creating a trigger on lead is the preferred solution for Unlimited Edition and Developer Edition. [/note] If you need to require uniqueness based on the value of two or more fields, or a single standard field, write an Apex*before insert* and *before update* trigger. For example, the following trigger prevents leads from being saved if they have a matching Email field: [code apex]trigger leadDuplicatePreventer on Lead (before insert, before update) { Map<String, Lead> leadMap = new Map<String, Lead>(); for (Lead lead : System.Trigger.new) { // Make sure we don't treat an email address that  // isn't changing during an update as a duplicate.  if ((lead.Email != null) && (System.Trigger.isInsert || (lead.Email != System.Trigger.oldMap.get(lead.Id).Email))) { // Make sure another new lead isn't also a duplicate  if (leadMap.containsKey(lead.Email)) { lead.Email.addError('Another new lead has the ' + 'same email address.'); } else { leadMap.put(lead.Email, lead); } } } // Using a single database query, find all the leads in  // the database that have the same email address as any  // of the leads being inserted or updated.  for (Lead lead : [SELECT Email FROM Lead WHERE Email IN :leadMap.KeySet()]) { Lead newLead = leadMap.get(lead.Email); newLead.Email.addError('A lead with this email ' + 'address already exists.'); } }[/code]

The following class can be used to test the trigger for both single- and bulk-record inserts and updates.

[code apex] public class leadDupePreventerTests{ static testMethod void testLeadDupPreventer() { // First make sure there are no leads already in the system // that have the email addresses used for testing Set<String> testEmailAddress = new Set<String>(); testEmailAddress.add('test1@duptest.com'); testEmailAddress.add('test2@duptest.com'); testEmailAddress.add('test3@duptest.com'); testEmailAddress.add('test4@duptest.com'); testEmailAddress.add('test5@duptest.com'); System.assert([SELECT count() FROM Lead WHERE Email IN :testEmailAddress] == 0); // Seed the database with some leads, and make sure they can // be bulk inserted successfully. Lead lead1 = new Lead(LastName='Test1', Company='Test1 Inc.', Email='test1@duptest.com'); Lead lead2 = new Lead(LastName='Test2', Company='Test2 Inc.', Email='test4@duptest.com'); Lead lead3 = new Lead(LastName='Test3', Company='Test3 Inc.', Email='test5@duptest.com'); Lead[] leads = new Lead[] {lead1, lead2, lead3}; insert leads; // Now make sure that some of these leads can be changed and // then bulk updated successfully. Note that lead1 is not // being changed, but is still being passed to the update // call. This should be OK. lead2.Email = 'test2@duptest.com'; lead3.Email = 'test3@duptest.com'; update leads; // Make sure that single row lead duplication prevention works // on insert. Lead dup1 = new Lead(LastName='Test1Dup', Company='Test1Dup Inc.', Email='test1@duptest.com'); try { insert dup1; System.assert(false); } catch (DmlException e) { System.assert(e.getNumDml() == 1); System.assert(e.getDmlIndex(0) == 0); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'A lead with this email address already exists.') > -1); } // Make sure that single row lead duplication prevention works // on update. dup1 = new Lead(Id = lead1.Id, LastName='Test1Dup', Company='Test1Dup Inc.', Email='test2@duptest.com'); try { update dup1; System.assert(false); } catch (DmlException e) { System.assert(e.getNumDml() == 1); System.assert(e.getDmlIndex(0) == 0); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'A lead with this email address already exists.') > -1); } // Make sure that bulk lead duplication prevention works on // insert. Note that the first item being inserted is fine, // but the second and third items are duplicates. Note also // that since at least one record insert fails, the entire // transaction will be rolled back. dup1 = new Lead(LastName='Test1Dup', Company='Test1Dup Inc.', Email='test4@duptest.com'); Lead dup2 = new Lead(LastName='Test2Dup', Company='Test2Dup Inc.', Email='test2@duptest.com'); Lead dup3 = new Lead(LastName='Test3Dup', Company='Test3Dup Inc.', Email='test3@duptest.com'); Lead[] dups = new Lead[] {dup1, dup2, dup3}; try { insert dups; System.assert(false); } catch (DmlException e) { System.assert(e.getNumDml() == 2); System.assert(e.getDmlIndex(0) == 1); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'A lead with this email address already exists.') > -1); System.assert(e.getDmlIndex(1) == 2); System.assert(e.getDmlFields(1).size() == 1); System.assert(e.getDmlFields(1)[0] == 'Email'); System.assert(e.getDmlMessage(1).indexOf( 'A lead with this email address already exists.') > -1); } // Make sure that bulk lead duplication prevention works on // update. Note that the first item being updated is fine, // because the email address is new, and the second item is // also fine, but in this case it's because the email // address doesn't change. The third case is flagged as an // error because it is a duplicate of the email address of the // first lead's value in the database, even though that value // is changing in this same update call. It would be an // interesting exercise to rewrite the trigger to allow this // case. Note also that since at least one record update // fails, the entire transaction will be rolled back. dup1 = new Lead(Id=lead1.Id, Email='test4@duptest.com'); dup2 = new Lead(Id=lead2.Id, Email='test2@duptest.com'); dup3 = new Lead(Id=lead3.Id, Email='test1@duptest.com'); dups = new Lead[] {dup1, dup2, dup3}; try { update dups; System.assert(false); } catch (DmlException e) { System.debug(e.getNumDml()); System.debug(e.getDmlMessage(0)); System.assert(e.getNumDml() == 1); System.assert(e.getDmlIndex(0) == 2); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'A lead with this email address already exists.') > -1); } // Make sure that duplicates in the submission are caught when // inserting leads. Note that this test also catches an // attempt to insert a lead where there is an existing // duplicate. dup1 = new Lead(LastName='Test1Dup', Company='Test1Dup Inc.', Email='test4@duptest.com'); dup2 = new Lead(LastName='Test2Dup', Company='Test2Dup Inc.', Email='test4@duptest.com'); dup3 = new Lead(LastName='Test3Dup', Company='Test3Dup Inc.', Email='test3@duptest.com'); dups = new Lead[] {dup1, dup2, dup3}; try { insert dups; System.assert(false); } catch (DmlException e) { System.assert(e.getNumDml() == 2); System.assert(e.getDmlIndex(0) == 1); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'Another new lead has the same email address.') > -1); System.assert(e.getDmlIndex(1) == 2); System.assert(e.getDmlFields(1).size() == 1); System.assert(e.getDmlFields(1)[0] == 'Email'); System.assert(e.getDmlMessage(1).indexOf( 'A lead with this email address already exists.') > -1); } // Make sure that duplicates in the submission are caught when // updating leads. Note that this test also catches an attempt // to update a lead where there is an existing duplicate. dup1 = new Lead(Id=lead1.Id, Email='test4@duptest.com'); dup2 = new Lead(Id=lead2.Id, Email='test4@duptest.com'); dup3 = new Lead(Id=lead3.Id, Email='test2@duptest.com'); dups = new Lead[] {dup1, dup2, dup3}; try { update dups; System.assert(false); } catch (DmlException e) { System.assert(e.getNumDml() == 2); System.assert(e.getDmlIndex(0) == 1); System.assert(e.getDmlFields(0).size() == 1); System.assert(e.getDmlFields(0)[0] == 'Email'); System.assert(e.getDmlMessage(0).indexOf( 'Another new lead has the same email address.') > -1); System.assert(e.getDmlIndex(1) == 2); System.assert(e.getDmlFields(1).size() == 1); System.assert(e.getDmlFields(1)[0] == 'Email'); System.assert(e.getDmlMessage(1).indexOf( 'A lead with this email address already exists.') > -1); } } } [/code]

Discussion

The first and most important lesson to learn from this recipe is that you should generally take advantage of point-and-click Force.com functionality if it can solve your problem, rather than writing code. By using the point-and-click tools that are provided, you leverage the power of the platform. Why reinvent the wheel if you can take advantage of a point-and-click feature that performs the same functionality? As a result, we indicate in this recipe that you should first determine whether you can simply use the Unique and Required checkboxes on a single custom field definition to prevent duplicates.

If you do need to check for duplicates based on the value of a single standard field, or more than one field, Apex is the best way to accomplish this. Because Apex runs on the Force.com servers, it's far more efficient than a deduplication algorithm that runs in a Web control. Additionally, Apex can execute every time a record is inserted or updated in the database, regardless of whether the database operation occurs as a result of a user clicking Save in the user interface, or as a result of a bulk *upsert* call to the API. Web controls can only be triggered when a record is saved through the user interface.

The included trigger is production-ready because it meets the following criteria: