4

Let's say I want to check records for duplicates before allowing insertion, updates or undeletes. Records are considered duplicates if they satisfy some equality comparison which involves multiple fields. I believe formula fields cannot be made unique, although I could have a workflow rule to copy a formula field to yet another field and then have a unique constraint on that.

Matching rules only work for a single lookup relationship fields, and as it happens I might want multiple ones.

Finally, I can use a trigger which would check against existing records and against other records in a trigger scope. However, if the trigger is chunked, it will be executed multiple times, wiping the structures used to keep track of duplicates. At the same time, records from a previous trigger chunk won't be available to select from the database. To get around that, I might use a static variable to hold my structures. This feels clunky.

Is there some other elegant way to prevent duplication through a trigger?

  • 1
    so if in batch 1-200 you have record with value foo and in batch 201-400 you have another record with value foo, you want both records to fail to insert? This won't be possible, best you can do is insert the first foo and then fail the second foo in batch 2 – cropredy Jan 28 '17 at 1:30
  • Question: do you care about all fields matching, or just a particular subset? For example, if you're checking Name, Email, and Phone, would Name+Phone be a duplicate, even though Email is different? Or would all three values have to match? – sfdcfox Jan 28 '17 at 1:36
  • @sfdcfox I'm looking for a subset of all fields and all fields of a subset should match to make something a duplicate. – ipavlic Jan 28 '17 at 3:31
  • @cropredy It's my understanding that chunks are not committed until they have all been processed. So unless bulk API is used, the entire transaction would fail. So while rejecting all duplicates is not possible, it is possible to either 1. insert the first unique record, reject all later duplicates 2. reject the entire transaction. Am I wrong? – ipavlic Jan 28 '17 at 3:43
  • 1
    sorry, I have data loader on my mind these days; each batch of 200 = 1 trigger context is a separate transaction – cropredy Jan 28 '17 at 3:50
6

Personally, one easy thing you can do is to generate digests, and put them into a text field. By generating a large digest, you'll find exact matches with a very low probability of a false positive, and even be able to use unique indexes to enforce deduplication with virtually zero effort.

There's several different possible combinations you could go for, but personally, I'd go with the easy route: a unique index on a custom text field. Here's an easy way to leverage a unique index.

trigger calculateMacForRecord on Lead (before insert, before update) {
    for(Lead record: Trigger.new) {
        record.Duplicate_Check__c = 
            EncodingUtil.base64Encode(
                Crypto.generateDigest(
                    'sha-512',
                    Blob.valueOf(
                        (
                            (''+record.Name).rightPad(255)+
                            (''+record.Phone).rightPad(255)+
                            (''+record.Email).rightPad(255)
                        ).toUpperCase()
                    )
                )
            );
    }
}

This requires virtually no CPU time, requires no queries, doesn't need to worry about multiple in-flight transactions. Of course, the drawback is that it's only an exact (although in this example, case-sensitive) match of all fields in order to cause a duplicate error; depending on data types, you might need to intentionally strip out punctuation, etc to get a good match. You could use a few unique indexes, though, if there's not too many possible variations.

More complicated versions are also possible; you could fill up a text area field with multiple such signatures, and then perform an SOSL query to find possible matches. Unfortunately, this doesn't work in real-time, so you might need to go with asynchronous process if you decided to do that. You couldn't prevent duplicates, but you could easily detect them. In fact, you could even set up Matching Rules based on the digest being an exact match, which would be just as good as a unique index.

Other things you could do: make a field set to make this dynamically configurable (but each record would have to be updated via trigger), query this digest field using FOR UPDATE to find duplicates, or use formula fields to determine the value to digest instead (formula fields are available in trigger context).

In summary, there's a variety of ways you could simply calculate digests in order to make finding duplicates incredibly efficient, even if there's a large number of fields you need to compare against, because the odds of a single sha512 collision is astronomical unless there is an exact match.

  • 1
    Basically, the text field is populated in a trigger which causes a uniqueness exception for duplicates. Similar to first option in my question but without the overhead of an additional field. That's a great option although it does introduce an extra field. Do you see any issues with my third suggestion? – ipavlic Jan 28 '17 at 3:40
  • 2
    @ipavlic It "works", at least in the traditional sense. The problem is engineering a solution that doesn't miss records, doesn't blow past the 20,000 SOQL character limit, doesn't select more than 50,000 rows by accident, doesn't drastically slow down the speed of inserts and updates, and so on. It can be done, but I've always preferred simplicity, and it doesn't get much simpler than this. – sfdcfox Jan 28 '17 at 4:07
  • Good argument and great answer once again! Thank you. – ipavlic Jan 28 '17 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.