You need to sign in to do that
Don't have an account?
sakshi Gandhi 8
Too many Query rows More than 50001
Hello, I am inserting 60k records and trigger is showing Too many query rows exception. I am trying to achieve roll up summary through this trigger .Below is code
trigger FRB_updateExceptionField on Process_Adherence_Monitoring__c (after insert,after update,after delete) {
Set<id> accIdSet = new Set<id>();
string FRBRecordTypeID = utility.getFRBProfileNameLabel();
if(Trigger.isInsert){
For(Process_Adherence_Monitoring__c pm1 : Trigger.new){
accIdSet.add(pm1.Organization_Name__c);
}
}
if(Trigger.IsUpdate || Trigger.Isdelete){
For(Process_Adherence_Monitoring__c pm1 : Trigger.old){
accIdSet.add(pm1.Organization_Name__c);
}
}
List<Account> accUpdateListunderfrst = new List<Account>();
List<Account> accUpdateListUnderScnd = new List<Account>();
List<Account> accUpdateListUnderThird = new List<Account>();
List<Account> accUpdateListUnderForth = new List<Account>();
Map<id,Account> accMap = new Map<id,Account>([select id, Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id IN :accIdSet]);
List<Account>listToUpdateFrstBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet];
if(listToUpdateFrstBcktExceptions .size()>0){
for (Account acc: listToUpdateFrstBcktExceptions ){
accMap.get(acc.Id).Number_of_Exception__c = acc.Process_Adherence_Monitoring__r.size();
accUpdateListunderfrst.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatesecondBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatesecondBcktExceptions .size()>0){
for (Account acc:listToUpdatesecondBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_4_6_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderScnd.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatethrdBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatethrdBcktExceptions.size()>0){
for (Account acc:listToUpdatethrdBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_7_10_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderThird.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatefrthBcktExceptions =[select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where Id IN :accIdSet];
if(listToUpdatefrthBcktExceptions.size()>0){
for (Account acc: listToUpdatefrthBcktExceptions) {
accMap.get(acc.Id).Number_of_exceptions_under_11_19_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderForth.add(accMap.get(acc.Id));
}
}
if(accUpdateListunderfrst.size()>0){
update accUpdateListunderfrst;
}
if(accUpdateListUnderScnd.size()>0){
update accUpdateListUnderScnd;
}
if(accUpdateListUnderThird.size()>0){
update accUpdateListUnderThird;
}
if(accUpdateListUnderForth.size()>0){
update accUpdateListUnderForth;
}
}
Please help
trigger FRB_updateExceptionField on Process_Adherence_Monitoring__c (after insert,after update,after delete) {
Set<id> accIdSet = new Set<id>();
string FRBRecordTypeID = utility.getFRBProfileNameLabel();
if(Trigger.isInsert){
For(Process_Adherence_Monitoring__c pm1 : Trigger.new){
accIdSet.add(pm1.Organization_Name__c);
}
}
if(Trigger.IsUpdate || Trigger.Isdelete){
For(Process_Adherence_Monitoring__c pm1 : Trigger.old){
accIdSet.add(pm1.Organization_Name__c);
}
}
List<Account> accUpdateListunderfrst = new List<Account>();
List<Account> accUpdateListUnderScnd = new List<Account>();
List<Account> accUpdateListUnderThird = new List<Account>();
List<Account> accUpdateListUnderForth = new List<Account>();
Map<id,Account> accMap = new Map<id,Account>([select id, Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id IN :accIdSet]);
List<Account>listToUpdateFrstBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet];
if(listToUpdateFrstBcktExceptions .size()>0){
for (Account acc: listToUpdateFrstBcktExceptions ){
accMap.get(acc.Id).Number_of_Exception__c = acc.Process_Adherence_Monitoring__r.size();
accUpdateListunderfrst.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatesecondBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatesecondBcktExceptions .size()>0){
for (Account acc:listToUpdatesecondBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_4_6_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderScnd.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatethrdBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatethrdBcktExceptions.size()>0){
for (Account acc:listToUpdatethrdBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_7_10_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderThird.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatefrthBcktExceptions =[select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where Id IN :accIdSet];
if(listToUpdatefrthBcktExceptions.size()>0){
for (Account acc: listToUpdatefrthBcktExceptions) {
accMap.get(acc.Id).Number_of_exceptions_under_11_19_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderForth.add(accMap.get(acc.Id));
}
}
if(accUpdateListunderfrst.size()>0){
update accUpdateListunderfrst;
}
if(accUpdateListUnderScnd.size()>0){
update accUpdateListUnderScnd;
}
if(accUpdateListUnderThird.size()>0){
update accUpdateListUnderThird;
}
if(accUpdateListUnderForth.size()>0){
update accUpdateListUnderForth;
}
}
Please help
Hi Sakshi,
50k is a salesforce Limit, Total number of records retrieved by SOQL queries can not exceed 50k as per
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm
You will need to use Batch Apex, to insert records more than 50K, see link below to use batch apex
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch.htm
Thanks,
Balayesu
Call the Batch class from your trigger like this,
Write your Batch class like this,
Mark it as the best answer if it helps,
Thanks,
Balayesu
"You've exceeded the limit of 100 jobs in the flex queu"
You've exceeded the limit of 100 jobs in the flex queu
call the batch class in your trigger only once by combining all the four lists as one and pass this one as a parameter to your batch class.
Thanks,
Balayesu
Now it is allowing me to insert 60k records successfully but again giving "First error: Too many query rows: 50001" even in batch class below is the code:
Trigger Code:
trigger FRB_updateExceptionField on Process_Adherence_Monitoring__c (after insert,after update,after delete) {
Set<id> accIdSet = new Set<id>();
string FRBRecordTypeID = utility.getFRBProfileNameLabel();
if(Trigger.isInsert){
For(Process_Adherence_Monitoring__c pm1 : Trigger.new){
accIdSet.add(pm1.Organization_Name__c);
}
if(accIdSet.size()>0){
Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding'];
if(flexQueueSize < 100){
Database.executeBatch(new FRB_BatchToUpdateExceptionFields ());
system.debug('*********'+accIdset);
}
}
}
if(Trigger.IsUpdate || Trigger.Isdelete){
For(Process_Adherence_Monitoring__c pm1 : Trigger.old){
accIdSet.add(pm1.Organization_Name__c);
}
if(accIdSet.size()>0){
Integer flexQueueSize = [SELECT COUNT() FROM AsyncApexJob WHERE Status = 'Holding'];
if(flexQueueSize < 100){
Database.executeBatch(new FRB_BatchToUpdateExceptionFields ());
}
}
}
}
Batch Class code:
global class FRB_BatchToUpdateExceptionFields implements Database.Batchable<SObject>{
global database.querylocator start(Database.BatchableContext bc)
{
string FRBRecordTypeID = utility.getFRBProfileNameLabel();
string query= 'Select id,Status__c,LastModifiedDate,Account_Resolution_Date__c,Organization_Name__c,C1_TOTAL_BAL_THRESHOLD__c,C1_Resolution_Date__c,C2_RCVRABLE_COST_COST_SPENT__c,C2_Resolution_Date__c,C3_ATTY_OTHER_FEES_CNTRCT_AMT__c,C3_Resolution_Date__c,C4_ATTY_FEES_STAT_AMT__c,C4_Resolution_Date__c,C5_INTEREST_RATE__c,C5_Resolution_Date__c,C6_INTEREST_THRESHOLD__c,C6_Resolution_Date__c,C7_CALCULATED_BALANCE_FIRM_BAL__c,C7_Resolution_Date__c,C7A_PRIN_AMT_PLACED_PRIN__c,C7A_Resolution_Date__c,C7B_REC30_PMTS_ADJS_REC42_PMTS_ADJS__c,C7B_Resolution_Date__c,C7C_REC30_RCVR_COST_REC42_RCVR_COST__c,C7C_Resolution_Date__c,C7D_CURRENT_BALANCE_0__c,C7D_Resolution_Date__c from Process_Adherence_Monitoring__c where RecordtypeId =: FRBRecordTypeID AND Status__c != \'Closed\'';
return Database.getQueryLocator(query);
}
global void execute(Database.BatchableContext bc, Process_Adherence_Monitoring__c[] FRBList)
{
string FRBRecordTypeID = utility.getFRBProfileNameLabel();
system.debug('=====FRBList'+FRBList);
List<Account> accUpdateListunderfrst = new List<Account>();
List<Account> accUpdateListUnderScnd = new List<Account>();
List<Account> accUpdateListUnderThird = new List<Account>();
List<Account> accUpdateListUnderForth = new List<Account>();
set<id>accidSet = new set<id>();
if(FRBList.size()>0){
for(Process_Adherence_Monitoring__c p:FRBLIST){
accidSet.add(p.Organization_Name__c);
}
}
Map<id,Account> accMap = new Map<id,Account>([select id, Number_of_Exception__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_11_19_bucket__c from Account where id IN :accIdSet]);
if(accidSet.size()>0){
List<Account>listToUpdateFrstBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) from Account where Id IN :accIdSet];
if(listToUpdateFrstBcktExceptions .size()>0){
for (Account acc: listToUpdateFrstBcktExceptions ){
accMap.get(acc.Id).Number_of_Exception__c = acc.Process_Adherence_Monitoring__r.size();
accUpdateListunderfrst.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatesecondBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatesecondBcktExceptions .size()>0){
for (Account acc:listToUpdatesecondBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_4_6_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderScnd.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatethrdBcktExceptions = [select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where Id IN :accIdSet];
if(listToUpdatethrdBcktExceptions.size()>0){
for (Account acc:listToUpdatethrdBcktExceptions ) {
accMap.get(acc.Id).Number_of_exceptions_under_7_10_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderThird.add(accMap.get(acc.Id));
}
}
List<Account>listToUpdatefrthBcktExceptions =[select Id, Name, Number_of_Exception__c ,(select id,Account_Standing__c from Process_Adherence_Monitoring__r where Status__C != 'Closed' AND recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where Id IN :accIdSet];
if(listToUpdatefrthBcktExceptions.size()>0){
for (Account acc: listToUpdatefrthBcktExceptions) {
accMap.get(acc.Id).Number_of_exceptions_under_11_19_bucket__c= acc.Process_Adherence_Monitoring__r.size();
accUpdateListUnderForth.add(accMap.get(acc.Id));
}
}
}
if(accUpdateListunderfrst.size()>0){
update accUpdateListunderfrst;
}
if(accUpdateListUnderScnd.size()>0){
update accUpdateListUnderScnd;
}
if(accUpdateListUnderThird.size()>0){
update accUpdateListUnderThird;
}
if(accUpdateListUnderForth.size()>0){
update accUpdateListUnderForth;
}
}
global void finish(Database.BatchableContext bc)
{
}
}
try the if condition in your trigger like this,
I have changes my code as per your suggestion and again load records and it is saying
that Job has already scheduled for execution:
The Apex job named "FRB_BatchToUpdateExceptionFields" is already scheduled for execution.
As apex jobs are more than 100 so it is trying to execute it again .Not sure how to achieve this