3

I need to figure out the list of duplicate records based on two fields. That means suppose we have two duplicate records a and b, a.field_a__c = b.field_a__c and a.field_b__c = b.field_b__c and a.id != b.id. Is there anyway I can query this?

4

See my answer for information on how to catch multiple fields as duplicates (the answer mentioned uses three fields, but this can be expanded to any number of fields as you need). While this answer is not technically a duplicate, the answers you seek are already here on SFSE.

For a recap, basically, one solution is to use a wrapper class to mark duplicates. First, aggregate all possible values together, then query for them and place them into the wrapper class, then process the results. I've included the original code from the other example for you to peruse.

trigger BlockDupeLeadConvert on Lead (after update) {
    class UniqueKey {
        UniqueKey(string Firstname, String lastName, String email) {
            this.firstname = firstname;
            this.lastname = lastname;
            this.email = email;
        }
        string FirstName, LastName, Email;
        integer hashCode() {
            return toString().hashCode();
        }
        boolean equals(Object o) {
            return toString() == ((UniqueKey)o).toString();
        }
        public override string toString() {
            return String.format(
                'FirstName = \'\'{0}\'\' AND LastName = \'\'{1}\'\' AND Email = \'\'{2}\'\'',
                new String[] {
                    FirstName==null?'':String.escapeSingleQuotes(FirstName.tolowercase()),
                    LastName==null?'':String.escapeSingleQuotes(LastName.tolowercase()),
                    Email==null?'':String.escapeSingleQuotes(Email.tolowercase())
                }
            );
        }
    }

    Map<UniqueKey, Lead> leads = new Map<UniqueKey, Lead>();
    for(Lead record:Trigger.new) {
        if(record.isconverted) {
            UniqueKey key = new UniqueKey(record.firstname, record.lastname, record.email);
            if(leads.containskey(key)) {
                record.addError('Duplicate lead conversion.');
            } else {
                leads.put(key, record);
            }
        }
    }
    String[] keys = new String[0];
    for(UniqueKey key:leads.keyset()) {
        keys.add(key.tostring());
    }
    String query = string.format(
        'SELECT Id,FirstName,LastName,Email FROM Contact WHERE {0}',
        new String[] {
            String.join(keys, ' OR ')
        }
    );
    if(!leads.isempty()) {
        for(Contact record:Database.query(query)) {
            UniqueKey key = new UniqueKey(record.firstname, record.lastname, record.email);
            if(leads.containskey(key)) {
                leads.get(key).addError('Duplicate lead conversion.');
            }
        }
    }
}

The reason why this is necessary is because unlike normal SQL, you can't simply write select ... left join on a.field_a__c = b.field_a__c and a.field_b__c = b.field_b__c and a.id != b.id. It doesn't work that way. The way around it is rather unfortunate, as you're likely to query many more records than you intend to in most cases. If you have very few values to choose from, you may even be forced to use extreme measures, like a formula field or even a batch class to flag the records as duplicate after the fact.

|improve this answer|||||
  • Thank you for the detailed answer. But this seems to query the whole table which in my case means 170k+ records. I am trying to narrow down the query to make it a little bit more reasonable. Making it a batch job doesn't seem to be an option. – Lance Shi Jul 16 '14 at 6:00
  • @LanceShi No, it's only looking for records that might possibly be a duplicate. This specific example builds a query like select id, firstname, lastname, email from contact where (firstname='John' and lastname='Doe' and email='johndoe@gmail.com') or (firstname='Jane' and lastname='Smith' and email='janesmith@gmail.com'). Only query what you need to find, and you should be okay. – sfdcfox Jul 16 '14 at 6:04
  • thank you for pointing that out. But the map is built on Trigger.new. In my case, it is not a trigger, it is a query which will query the whole table - Anyway, I have figured out how to narrow it down now. And your example is of great help. – Lance Shi Jul 16 '14 at 6:44
  • As the string length increases as the number of records increase , will this have any impact on the SOQL query character limit. SOQL Characted limit is: 20000 per soql statement. Please let me know if I am getting something wrong about the character limit – kso Jun 15 '16 at 6:20
  • as I assume if a single record requires ~50 character then a list having nearly 500 records can easily cross the limit.. – kso Jun 15 '16 at 6:26

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.