The Salesforce mantra is ‘clicks not code’ so we always look for a config solution to a problem before resorting to code. While we can set the unique indicator on a custom field to prevent duplicate values for one field across records, how do we prevent records inserted with duplicate values across all or some of the fields? Do we need to write a trigger to handle this? Or is there a config only solution to the problem?

Its actually very simple and can be accomplished in 2 steps:

1. Define a unique Text field on the object
2. Create a workflow rule and field update to update the text field with a concatenation of values

In this example, we are using a custom object ‘Transaction’. The Transaction object has lookups to Account and Product and a Transaction Date and Amount. We want to make sure that no duplicate transactions for the same Account, Product, Transaction Date and Amount can be saved.

To setup our config duplicate check, we follow step 1 and add a custom field ‘Unique Record Key’ to the Transaction object. Note that it is marked as Unique. The type and length is defined as Text 100. This should be more than enough as it is holding 2 id fields, a date and an amount.

Next we need to add a workflow rule. The rule criteria is ‘True’ and the evaluation criteria is set to created and every time edited, to make sure that it will always fire for new and for any edits to existing records.

The field update concatenates the values of the Account Id, Product Id, Transaction Date and Transaction Amount and updates the Unique Record Key with the result. Notice that the non text fields need to use the TEXT function to convert them to text to be able to use the concatenate function.

Here we see the Unique Record Key added temporarily to the page layout, so we can see that the field update is working. You can see the two id values, followed by a date and an amount. Don’t forget to remove the field from the page layout!

Finally here is the result we are looking for! We have prevented a duplicate record with Salesforce config only. Attempting to add a record with exactly the same values for Account, Product, Transaction Date and Amount will result in an error. The error message tells us there is a duplicate value on another record, and even gives us the record name and a link to the record to check it.