function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Aidel BruckAidel Bruck 

working with soql queries and dataloader

I have a custom object X I need to updated through the dataloader. When updated I need the related account to be updated for each custom object. 
Obviously, my trigger uses a Soql query on each instance of the custom object to find the related account. 
When I run the dataloader update I reach the limit and I am stuck. 
​Any ideas?
Best Answer chosen by Aidel Bruck
Aidel BruckAidel Bruck
This article was what I needed. Putting this out there for future reference
http://www.sfdc99.com/2014/01/20/soql-queries-inside-loops/

All Answers

GauravGargGauravGarg
Hi Aidel,

This is not the ideal situation to update Related objects while inserting/updating mass # of Custom object, Please follow below approach:
  1. Bypass Account update for mass DML operation. 
  2. Create a batch job, that will identify all the custom object records that has been updated recently. 
  3. Pull related Account records and make necessary changes. 
  4. Update account object.
Hope this helps!!!

Thanks,
Gaurav
Skype: gaurav62990
Aidel BruckAidel Bruck
Are you suggesting I run the dataloader again? *Aidel Bruck* Rofeh Cholim Cancer Society *Office*: 077-300-7482 ext: 200 *Mobile*: 011-972-50-418-9123 *US:* 1-929-255-1759
GauravGargGauravGarg
Aidel,

Yes, anyway we need to do that uptil records get updated properly.

But, first complete below steps:
  • bypass Account update code for Mass Update.
  • Write batch job to update related Account records, that will be run after Data loader. 


Thanks,
Gaurav
Skype: gaurav62990
Aidel BruckAidel Bruck
How do I bypass code? And how do I create a batch job. I have never done any of these? this is what my code looks like right now trigger PaymentPaid on Payment__c (after insert, after update) { List UpdatedAccounts= new list(); If(trigger.isinsert) for(payment__c pay :trigger.new ) If(pay.status__C=='paid') { system.debug('pay new'+pay.Check_Amount_G__c); updatedAccounts=paymentpaidhelper.CreateUpdatedAccounts(pay, updatedaccounts); } If(trigger.isupdate) for(payment__c pay :trigger.new ) for (payment__c payold: trigger.old) if(pay.id== payold.id) If(pay.status__C== 'paid' && payold.status__c != 'paid') { system.debug('pay update'+ pay.Check_Amount_G__c); updatedAccounts=paymentpaidhelper.createupdatedaccounts(pay, updatedaccounts); } if(!updatedAccounts.isempty()) update updatedaccounts; } public class PaymentPaidHelper { public static List CreateUpdatedAccounts(Payment__C Pay, list updatedAccounts ) { integer i= 0; account a= new account(); boolean remove= false; integer stop= 0; If(!updatedaccounts.isempty()) { for(account acc: updatedaccounts) { a= [select Patient_Id__c, Last_Payment_Policy_ID__c, Last_Payment_Check_Amount__c, Last_payment_check_date__c, Last_Payment_Check_Number__c, Last_payment_covering_month__c, Last_payment_Payee__c From Account Where Patient_ID__c =: pay.Patient_ID__c][0]; if(acc.patient_Id__C== a.patient_id__c) { a.Last_Payment_Policy_ID__c= acc.Last_Payment_Policy_ID__c+'; '+pay.approved_policy_name__C; a.Last_Payment_Check_Amount__c= acc.Last_Payment_Check_Amount__c+ '; '+pay.Check_Amount_G__c; a.Last_payment_check_date__c= pay.check_date__C; a.Last_Payment_Check_Number__c=acc.Last_Payment_Check_Number__c+'; '+ pay.Check__c; a.last_payment_covering_month__C= acc.last_payment_covering_month__C+'; '+pay.Covering_Period__c; a.Last_payment_Payee__c= acc.Last_payment_Payee__c+'; '+pay.payee__c; //system.debug('ids are equal: last payment policy: '+a.Last_Payment_Policy_ID__c+'/n last payment check amount '+ a.Last_Payment_Check_Amount__c+ //'/n last payment check date '+ a.Last_payment_check_date__c+'/n last payment check number '+ a.Last_Payment_Check_Number__c+ //'/n last payment covering month '+ a.Last_payment_covering_month__c+'/n last payment payee '+a.Last_payment_Payee__c); remove= true; stop= i; } else { a.Last_Payment_Policy_ID__c= pay.approved_policy_name__c; a.Last_Payment_Check_Amount__c= pay.Check_Amount_G__c+''; a.Last_payment_check_date__c= pay.check_date__C; a.Last_Payment_Check_Number__c= pay.Check__c; a.last_payment_covering_month__C= pay.Covering_Period__c; a.Last_payment_Payee__c= pay.payee__c; // system.debug('Ids are not equal: last payment policy: '+a.Last_Payment_Policy_ID__c+'/n last payment check amount '+ a.Last_Payment_Check_Amount__c+ //'/n last payment check date '+ a.Last_payment_check_date__c+'/n last payment check number '+ a.Last_Payment_Check_Number__c+ //'/n last payment covering month '+ a.Last_payment_covering_month__c+'/n last payment payee '+a.Last_payment_Payee__c); } i++; } if(remove) updatedaccounts.remove(stop); updatedAccounts.add(a); } else { a= [select Patient_Id__c, Last_Payment_Policy_ID__c, Last_Payment_Check_Amount__c, Last_payment_check_date__c, Last_Payment_Check_Number__c, Last_payment_covering_month__c, Last_payment_Payee__c From Account Where Patient_ID__c =: pay.Patient_ID__c][0]; a.Last_Payment_Policy_ID__c= pay.approved_policy_name__C; a.Last_Payment_Check_Amount__c= pay.Check_Amount_G__c+''; a.Last_payment_check_date__c= pay.check_date__C; a.Last_Payment_Check_Number__c= pay.Check__c; a.last_payment_covering_month__C= pay.Covering_Period__c; a.Last_payment_Payee__c= pay.payee__c; updatedaccounts.add(a); } return updatedaccounts; } } *Aidel Bruck* Rofeh Cholim Cancer Society *Office*: 077-300-7482 ext: 200 *Mobile*: 011-972-50-418-9123 *US:* 1-929-255-1759
Aidel BruckAidel Bruck
How do I bypass code? And how do I create a batch job. I have never done any of these? this is what my code looks like right now

trigger PaymentPaid on Payment__c (after insert, after update)
{
    List<account> UpdatedAccounts= new list<account>();
    If(trigger.isinsert) 
            for(payment__c pay :trigger.new )
                If(pay.status__C=='paid')
                {
                    system.debug('pay new'+pay.Check_Amount_G__c);
                    updatedAccounts=paymentpaidhelper.CreateUpdatedAccounts(pay, updatedaccounts);
                }
                    
                
                
    
    If(trigger.isupdate)
            for(payment__c pay :trigger.new )
                for (payment__c payold: trigger.old)
                    if(pay.id== payold.id)
                        If(pay.status__C== 'paid' && payold.status__c != 'paid')
                        {
                            system.debug('pay update'+ pay.Check_Amount_G__c);
                            updatedAccounts=paymentpaidhelper.createupdatedaccounts(pay, updatedaccounts);
                        }
                            
                

    if(!updatedAccounts.isempty())
        update updatedaccounts;
}





public class PaymentPaidHelper 
{
    
    
    public static List<Account> CreateUpdatedAccounts(Payment__C Pay, list<account> updatedAccounts )
    {
                    integer i= 0;
                    account a= new account();
                    boolean remove= false;
                    integer stop= 0;
                    
                    If(!updatedaccounts.isempty())
                    {
                        for(account acc: updatedaccounts)
                        {
                            a=  [select Patient_Id__c, Last_Payment_Policy_ID__c, Last_Payment_Check_Amount__c,    Last_payment_check_date__c,     Last_Payment_Check_Number__c, 
                             Last_payment_covering_month__c, Last_payment_Payee__c From Account Where Patient_ID__c =: pay.Patient_ID__c][0];
                            if(acc.patient_Id__C== a.patient_id__c)
                            {
                            a.Last_Payment_Policy_ID__c= acc.Last_Payment_Policy_ID__c+'; '+pay.approved_policy_name__C;
                            a.Last_Payment_Check_Amount__c= acc.Last_Payment_Check_Amount__c+ '; '+pay.Check_Amount_G__c;
                            a.Last_payment_check_date__c= pay.check_date__C;
                            a.Last_Payment_Check_Number__c=acc.Last_Payment_Check_Number__c+'; '+ pay.Check__c;
                            a.last_payment_covering_month__C= acc.last_payment_covering_month__C+'; '+pay.Covering_Period__c;
                            a.Last_payment_Payee__c= acc.Last_payment_Payee__c+'; '+pay.payee__c;
                            //system.debug('ids are equal: last payment policy: '+a.Last_Payment_Policy_ID__c+'/n last payment check amount '+ a.Last_Payment_Check_Amount__c+ 
                             //'/n last payment check date '+    a.Last_payment_check_date__c+'/n last payment check number '+    a.Last_Payment_Check_Number__c+
                             //'/n last payment covering month '+ a.Last_payment_covering_month__c+'/n last payment payee '+a.Last_payment_Payee__c);

                            remove= true;
                            stop= i;
                            }
                            else
                            {
                            a.Last_Payment_Policy_ID__c= pay.approved_policy_name__c;
                            a.Last_Payment_Check_Amount__c= pay.Check_Amount_G__c+'';
                            a.Last_payment_check_date__c= pay.check_date__C;
                            a.Last_Payment_Check_Number__c= pay.Check__c;
                            a.last_payment_covering_month__C= pay.Covering_Period__c;
                            a.Last_payment_Payee__c= pay.payee__c;
                            //    system.debug('Ids are not equal: last payment policy: '+a.Last_Payment_Policy_ID__c+'/n last payment check amount '+ a.Last_Payment_Check_Amount__c+ 
                             //'/n last payment check date '+    a.Last_payment_check_date__c+'/n last payment check number '+    a.Last_Payment_Check_Number__c+
                             //'/n last payment covering month '+ a.Last_payment_covering_month__c+'/n last payment payee '+a.Last_payment_Payee__c);
                              }
              
                            
                            i++;
                            
                        }
                        if(remove)
                            updatedaccounts.remove(stop);
                        updatedAccounts.add(a);
                        
                    }
                    
                    else
                    {
                            a=  [select Patient_Id__c, Last_Payment_Policy_ID__c, Last_Payment_Check_Amount__c,    Last_payment_check_date__c,     Last_Payment_Check_Number__c, 
                             Last_payment_covering_month__c, Last_payment_Payee__c From Account Where Patient_ID__c =: pay.Patient_ID__c][0];
                            a.Last_Payment_Policy_ID__c= pay.approved_policy_name__C;
                            a.Last_Payment_Check_Amount__c= pay.Check_Amount_G__c+'';
                            a.Last_payment_check_date__c= pay.check_date__C;
                            a.Last_Payment_Check_Number__c= pay.Check__c;
                            a.last_payment_covering_month__C= pay.Covering_Period__c;
                            a.Last_payment_Payee__c= pay.payee__c;
                        
                            updatedaccounts.add(a);
                    }
        
        return updatedaccounts;
    }

}
GauravGargGauravGarg

Hi Aidel,
 

Can you please contact me on skype, this need to be analyzed and add batch job for this scenario.

Thanks,

Gaurav
Skype: gaurav62990
Aidel BruckAidel Bruck
This article was what I needed. Putting this out there for future reference
http://www.sfdc99.com/2014/01/20/soql-queries-inside-loops/
This was selected as the best answer
GauravGargGauravGarg
Hi Aidel,

is the problem solved?
GauravGargGauravGarg
Please close the case by selecting a best answer.