+ Start a Discussion
Dano BlevinsDano Blevins 

Comparing multiple values on two objects in loop

I have a list of fields on Account and an equal list of fields on Checklist_Library__c. Each field is a Boolean and there are 22 fields in total. 

I have a third object called Compliance__c which is related to both the Account and the Checklist_Library__c.  I need to look at the values and if any PAIR (from Account and Checklist_Library__c) == TRUE then Compliance__c status = "Not Applicable"

So if account.Value_1__c && Checklist_Library.Value_1__c == True, then Compliance.Status = "Not Applicable"

We are trying to do this on insert of the Compliance records and we generate approx 500 of them each time so I need to look at each compliance item and check to see if the status should be set to NA.

Each of the Compliance items are all related to 1 Account, but each Compliance item has a different Checklist_Library__c item it is related to.

So I need to loop through the Compliances being created, compare the flagged values on the Account, and see if the Compliances related Checklist_Library has any of the same named values flagged. I am trying really hard not to nest select statments inside a loop but that is what I keep running into. I list the Account values, then for each compliance item I query the related checklist libary for values in the same order and compare each value in the list and if ANY are equal, then I change status to true. Is there a better way to go about this?

Best Answer chosen by Dano Blevins
Piyush Gautam 6Piyush Gautam 6
Hi Dano Blevins,

It is better to use Map instead of calling SELECT query inside FOR LOOP.
I have prepared a dummy code for you. Please check. You need to pass a list of compliance that needs to be inserted.
If it helps please mark as solved

Thanks
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public static void apexMethod(List<Compliance__c> comList){
    
     List<Compliance__c> newcomList= new List<compliance__c>()
    
     Map<Id, Account> accMap= new Map<Id, Account>();
    
     for(Account acc: [SELECT id, field1, field2, ... FROM Account]){
        accMap.put(acc.Id, acc);
     }
    
     Map<Id, Checklist_Library__c> libMap= new Map<Id, Checklist_Library__c>();
    
     for(Checklist_Library__c lib: [SELECT id, field1, field2, ... FROM Checklist_Library__c]){
        libMap.put(lib.Id, lib);
     }    
    
        
        for(Compliance__c com: comList){
             
             account accObj= accMap.get(com.accountId);
             Checklist_Library__c libObj= libMap.get(com.Checklist_Library__c);
             
             if(accObj.field1== True && libObj.field1== True){
                com.status= 'Not Applicable';
             }
             elseif(accObj.field2== True && libObj.field2== True){
                 com.status= 'Not Applicable';
             }
             //...
             // Till 22 fields check
             else{
                 com.status= 'Applicable';
             }
             
             newcomList.add(com);
             
        }
        
        if(newcomList.size()> 0){
        
            insert newcomList;
        }
        
    }

All Answers

Piyush Gautam 6Piyush Gautam 6
Hi Dano Blevins,

It is better to use Map instead of calling SELECT query inside FOR LOOP.
I have prepared a dummy code for you. Please check. You need to pass a list of compliance that needs to be inserted.
If it helps please mark as solved

Thanks
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public static void apexMethod(List<Compliance__c> comList){
    
     List<Compliance__c> newcomList= new List<compliance__c>()
    
     Map<Id, Account> accMap= new Map<Id, Account>();
    
     for(Account acc: [SELECT id, field1, field2, ... FROM Account]){
        accMap.put(acc.Id, acc);
     }
    
     Map<Id, Checklist_Library__c> libMap= new Map<Id, Checklist_Library__c>();
    
     for(Checklist_Library__c lib: [SELECT id, field1, field2, ... FROM Checklist_Library__c]){
        libMap.put(lib.Id, lib);
     }    
    
        
        for(Compliance__c com: comList){
             
             account accObj= accMap.get(com.accountId);
             Checklist_Library__c libObj= libMap.get(com.Checklist_Library__c);
             
             if(accObj.field1== True && libObj.field1== True){
                com.status= 'Not Applicable';
             }
             elseif(accObj.field2== True && libObj.field2== True){
                 com.status= 'Not Applicable';
             }
             //...
             // Till 22 fields check
             else{
                 com.status= 'Applicable';
             }
             
             newcomList.add(com);
             
        }
        
        if(newcomList.size()> 0){
        
            insert newcomList;
        }
        
    }
This was selected as the best answer
Dano BlevinsDano Blevins
Thank you! This is exactly what I am looking for!!