Enforcing Unique Records

Sometimes I need to prevent duplicates on a table other than Accounts or Contacts (preventing Account and Contact dupes is, of course, a huge topic in itself).

The most-frequent need I've encountered is quotas: I want to allow one quota record per user, per quarter, and prevent duplicates.

Assuming I have a custom object called Quota with a picklist on it called "Quarter", here's how I typically do this:
  1. Create a field on Quota called Unique User+Quarter ID, with the following attributes:
    •  Text field
    •  Do not allow duplicate values
    •  Read-only (all Profiles)
    •  Not shown on any Page Layouts
  2. Create a Workflow Rule on the Quota object called Populate Unique User+Quarter ID:
    •  Evaluate rule every time a record is created or edited
    •  Criteria: Created By not equal to [null]
  3. Create a Workflow Field Update also called Populate Unique User+Quarter ID, fired by the Workflow Rule:
    •  Update field "Unique User+Quarter ID"
    •  Use a formula to set the new value: OwnerId & Quarter__c
By concatenating (that is, smooshing) the Owner ID and Quarter values together into a single field that requires unique values, Salesforce won't let me create dupes.

This general approach works in many situations where I need to guarantee unique records. Give it a try!  
Comments