1

Let's say I have a data load that inserts 100,000 unique records of a custom object - call it MyObject__c. Each record has a lookup to a Contact record that is populated. I want to iterate over the entire list of 100,000 inserted records and find all of the records of MyObject that look up to the same Contact, and then set a checkbox on all but one record that indicates they are 'duplicates'.

Example: there are 5 record of MyObject (out of 100,000) that all look up to the Contact MyContact (SFID 123456789). After processing the entire list of 100,000 records, I'd have four of the five records have a 'duplicate reference' checkbox marked, with the fifth one having it unchecked. Basically a uniqueness check on the lookup.

What would be the best way to accomplish something like this?

My current thought process is to build an Apex batch job; The batch runs the query to get all MyObject records that need to be processed (100,000) and splits them into a batch. I'm able to get their individual ContactId value they look up to, and I can use that to compare against other MyObject records and see if the Contact SFID lookups match and bulk-process the DML update to the records. Easy.

...But I'm getting hung on on how to get the data for all 100,000 MyObject records to compare against for each batch. Batch splits up the processing, but within each 200-record batch, I still need to get all 100,000 MyObject records that I am processing, read the ContactId they point to and find duplicates.

That is to say, if I am processing record 187 of Batch 1, I still need to compare it against all 100,000 other MyObject records to find the duplicates. I might be overthinking, but I can't find a way to avoid governance limits in this case. a SOQL statement [SELECT contactId FROM MyObject WHERE DataLoadId = myId] would return more than 50,000 rows (since I have 100,000 records that would match). SOSL has similar limitations on returned rows, as do aggregate queries.

How the heck would I do a compare against a large set of data? I feel like the answer is much simpler than I am making it out to be in my head...

2
  • So @DerekF has a very good point - instead of processing in a batch you could process in a trigger (I would change the logic slightly rather than setting values to true and then false but to each their own); however I want to clarify - how is it that you import 100K records? Is that via dataloader, Bulk API, UI? E.g. if you need it to work whenever records are created and eventual consistency is important I would go with trigger (provided you have business rules set for when the same contact exists in multiple batches in both one or many imports)
    – zaitsman
    Jan 10, 2023 at 2:04
  • If, however, you're building e.g. UI solution to allow your users to load 100K records then you can deal with this on the client side first.
    – zaitsman
    Jan 10, 2023 at 2:05

2 Answers 2

1

The way I'd go about this is to use a parent-child subquery. You don't need to compare against all of your custom object records, you just need to see the custom object records that are related to the Contacts in the current chunk that you're processing.

Assuming that the lookup to Contact is populated before you're loading the data (though Salesforce's DataLoader, or another similar tool), you don't even need a batchable (it could be accomplished in a trigger).

The general idea ends up being pretty similar to other situations where you're looking for related data. That is to say that you:

  • iterate over the current "chunk" of data that you're processing
  • gather the Ids of the related records you're interested in
  • perform a query

Assuming the work is done in a trigger (though this should be very easy to translate to the execute() method of a batchable)...

List<Id> contactIds = new List<Id>();
// Assuming this is a before insert trigger, where the record doesn't yet have
//   its own Id
// We need to be able to get back to this record later, so we use the Contact Id
//   as a proxy of sorts.
Map<Id, MyObject__c> firstMyObjectByContactId = new Map<Id, MyObject__c>();

for(MyObject__c myobj :trigger.new){
    // Assume that everything is a duplicate until we can show otherwise
    myobj.isDuplicate__c = true;

    // This handles encountering the same contact within a trigger chunk
    if(!firstMyObjectByContactId.containsKey(myobj.Contact__c)){
        firstMyObjectByContactId.put(myobj.Contact__c, myobj);
    }

    contactIds.add(myobj.Contact__c);
}

// We can filter and order in subqueries
// The purpose of LIMIT 1 is to ensure that we can (safely) use the subquery results
//   directly (i.e. without a nested loop)
// The assumption here is that we'll just mark the oldest child record as the
//   "original" and everything else as a duplicate
for(Contact cont :[SELECT Id, (SELECT Id FROM MyObjects__r ORDER BY CreatedDate ASC LIMIT 1) FROM Contact WHERE Id IN :contactIds]){
    // If there are no child records under the Contact yet (and this subquery
    //   would be empty until the "after insert" context), then it is not a duplicate
    //   and we can flip the duplicate flag
    if(cont.MyObjects__r.isEmpty()){
        firstMyObjectByContactId.get(cont.Id).isDuplicate__c = false;
    }
}

This does add 1 extra query per 200 records that you process. Querying the Contacts and limiting the subquery to returning a single row helps make this approach tread pretty lightly on the query rows limit as well (and the CPU limit, since you aren't trying to process 100k records all at once).

0

Solution:1 If you don't have any usage of duplicate records then you can utilise database to identify duplicate. Just make 'contact' lookup as external unique field. while uploading, salesforce DB will figure out the duplicates and will allow insertion of only one record for a particular contact. So you will end up with 1-to-1 record for myObject__c and contact.

Solution-2 If you have usage of duplicate records then you can introduce one more object let's say myObject2__c, it should have just one field as 'contact id' make it text/external/unique.

Now write a before insert trigger on your 1st object myObject__c. This trigger should in turn try to insert record in myObject2__c. if insertion fails then update the 'isDuplicate' checkbox to true else nothing. Trigger would look something like below.

trigger on myObject__c (before insert){
    
    try{
        MyObject2__c mobj2 = new MyObject2__c( ContactId__c = trigger.new(0).contactId__c;
    }catch(DMLExeption ex){
        trigger.new(0).isDuplicate__c = true;
    }
}

This way by utilizing database power of identifying duplicates, you will have data exactly as you wanted to achieve.

I assume you would be able to bulkify the trigger code. given sample is for concept explanation only.

Please promote if you like the answer.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .