2

I have 2 custom objects we will call ObjectOne__c and ObjectTwo__c, these 2 objects DO NOT have a direct relationship to each other. However, I have a business need that requires fields on ObjectTwo__c to be updated to match the fields on ObjectOne__c when ObjectOne__c is updated. Here is a sample of the Trigger that handles this process. My issues is I need to move the SOQL out of the loop to properly handle mass updates, but I can’t figure out how to do that and still get the trigger to properly update all the records in the trigger.

Here is a sample of the code in my trigger:

trigger COSyncTrigger on ObjectOne__c (after update) {
    //Create list to store the ObjectTwo__c records that will be Updated
    List < ObjectTwo__c> upObTwo = new List < ObjectTwo__c >();

    //Look at records being Updated
    if(trigger.isUpdate){

        for(ObjectOne__c ObjOne : trigger.new){
            //Create a list of existing ObjectTwo__c records that match the ObjectOne__c record being updated 
            List < ObjectTwo__c > checkupObjTwo = [Select Id from ObjectTwo__c Where (Account__c = :ObjOne.Account__c And Owner = : ObjOne.Owner And Client__c = : ObjOne. Client__c)
                                                     ];
            //If the Above List is NOT Empty Update the ObjectTwo__c records
            if(!checkupObjTwo.isEmpty()) {
                ObjectTwo__c upobjtwo = [Select Id from ObjectTwo__c Where (Account__c = : ObjOne.Account__c And Owner = : ObjOne.Owner And Client__c = : ObjOne. Client__c)]; 

                upobjtwo.Client_Role__c = ObjOne. Client_Role__c;
                upobjtwo.Client_Status__c = ObjOne. Client_Status__c;

                //Add the ATM record to the list to be updated later
                upObTwo.add(upobjtwo);
            }
        }           

        //If the List of records to update is NOT Empty update the ObjectTwo__c records
        if(upObTwo.size() > 0){
            system.debug('Count of ObjectTwo__c records to update = '+ upObTwo.size());

            update upObTwo;
        }
    }
}

I have done some research and understand I need to create a list outside my loop but I can't seem to get it to update ObjectTwo__c records to Match the ObjectOne__c records in the trigger. Please explain how I can accomplish this and provide an example if possible. Thank you in advance.

  • 1
    You want something similar to this design. – sfdcfox May 15 '18 at 15:40
  • 1
    I've made some adjustments to your code formatting. Basically, I made indentation consistent and removed some blank lines that I think don't make the code easier to read. That last bit is mostly personal preference, so feel free to edit them back in if you wish. – Derek F May 15 '18 at 15:42
2

You will want to create Sets to capture the values you are looking for. This design is what I call the Aggregate Query Update pattern, since you first gather the values together, query the database once, then perform an update.

This trick uses ObjectTwo__c as a "key" in the Map to find records that meet all the criteria in linear time.

// Aggregate data to query
Set<Id> accountIds = new Set<Id>(), 
        ownerIds = new Set<Id>(),
        clientIds = new Set<Id>();
for(ObjectOne__c record: Trigger.new) {
    accountIds.add(record.Account__c);
    ownerIds.add(record.OwnerId);
    clientIds.add(record.Client__c);
}
Map<ObjectTwo__c, ObjectTwo__c> objTwos = new Map<ObjectTwo__c, ObjectTwo__c>(),
     updates = new Map<ObjectTwo__c, ObjectTwo__c>();
// Query for existing data
for(ObjectTwo__c record: [SELECT Account__c, OwnerId, Client__c FROM ObjectTwo__c
       WHERE Account__c = :accountIds AND OwnerId = :ownerIds 
             AND Client__c = :clientIds]) {
    objTwos.put(
        new ObjectTwo__c(Account__c=record.Account__c, 
            OwnerId=record.OwnerId, 
            Client__c=record.Client__c),
        record);
}
// Update the records
for(ObjectOne__c record: Trigger.new) {
    ObjectTwo__c key = new ObjectTwo__c(
        Client__c=record.Client__c, OwnerId=record.OwnerId, Account__c=record.Account__c),
         objTwoRecord = objTwos.get(key);
    if(objTwoRecord != null) {
        updates.put(key, objTwoRecord);
        objTwoRecord.Client_Role__c = record.Client_Role__c;
        objTwoRecord.Client_Status__c = record.Client_Status__c;
    }
}
update updates.values();
  • Any reason why key for Map is sObject and not string? Does it have better performance, any advantages or just your code style? – Pranay Jaiswal May 15 '18 at 16:10
  • 2
    @PranayJaiswal (a) performance (~5% faster), (b) clarity; with a string, I have to remember if it was Account+Owner+Client or Account+Client+Owner, etc, but with the SObject, I can specify the fields in any order without affecting the key, which makes it less likely that I'll screw up. – sfdcfox May 15 '18 at 16:15
  • 1
    @JCochran An important note here is that when you have a query with multiple, variable filters, moving that query outside of a loop means that the query can (and probably will) return more records than you want. If you only want Object_Two__c where the 3 key fragments are either "a, b, c" or "x, y, z", moving the query outside of the loop will give you records with "a, b, c", "a, b, z", ..., "x, y, c", "x, y, z" (8 total possible combinations, if they exist). Thus, the query alone is insufficient, and you need to do some filtering with Apex (which is exactly what sfdcfox's answer does). – Derek F May 15 '18 at 16:26
  • @DerekF Thank you for the additional details. It gave me some more insight regarding the solution provided by sfdcfox – JCochran May 16 '18 at 3:20

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.