I am receiving an error when the this scheduled APEX task runs. Apex Class = UpsellBatchApex Create Upsell Services batch Apex job processed 2 batches with 1 critical batch failures. Error: First error: Too many DML rows: 10001

The issue seems to occur on line 151 of the Class.

151   List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);        

152   for (Integer i = 0; i < AcctUpdResults.size(); i++) {           

153   if (!AcctUpdResults[i].isSuccess()) {

I need to figure out how to find out the list size using list.size() method.

Then I need to split the list so the split doesn't cross it to more than 10,000 records. I need to run a loop upto the split list size then store the records in another list and then run the database.update for the split list. I need to  store the index of the loop upto where the list is run then run the next loop from that index till next defined split / list.size() and store the records in list again. Does anyone know how I can code the APEX to do this? Any help is greatly appreciated.

Full code is below:

1 global class UpsellBatchApex implements Database.Batchable<sObject>,Database.Stateful

2 {

3 global Integer failedRecordscount {get; set;}

4 global Integer successRecordscount {get;set;}

5 //This query variable is only to hold the query string that is passed over by batch scheduler/EmailServiceHandler class

6 global final String AccountQuery;

7 private final boolean IsTest;

8 global List<Servics_by_Market_Segment__c> MktSegList;

9 private static final Set<String> USER_PROFILE_TO_NOTIFY;

10 private static final Set<String> ACC_RECORDTYPES_DEVNAME_TO_INCLUDE;

11 String failureMsg;

12 integer failedRecCount = 0;

13 integer successRecCount = 0;

14 

15 static

16 {

17 USER_PROFILE_TO_NOTIFY = new Set<String>();

18 USER_PROFILE_TO_NOTIFY.add('System Administrator');

19 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE = new Set<String>();

20 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer');

21 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer_Read_Only');

22 }

23 //Constructor:

24 //global UpsellBatchApex(String Q,String Obj,boolean b)

25 global UpsellBatchApex()

26 {

27 failedRecordscount = 0;

28 successRecordscount = 0;

29 AccountQuery = 'Select Id, Market_Segment__c, RecordType.DeveloperName, Include_for_Upsell__c from Account where Include_for_Upsell__c = true and RecordType.DeveloperName in :ACC_RECORDTYPES_DEVNAME_TO_INCLUDE AND Market_Segment__c != null';

30 MktSegList = new List<Servics_by_Market_Segment__c>([Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c]);

31 

32 //IsTest = b;

33 }

34 //Here we could either use querylocator or Query Iterator.Query Iterator enforces governor limits of 50k

35 //retrieved records.Since there are no complex processing, used query locator.

36 global Database.querylocator start(Database.BatchableContext BC)

37 { 38 return Database.getQueryLocator(AccountQuery);

39 }

40 

41 global void execute(Database.BatchableContext BC, List <Account> Scope)

42 {

43 List<Upsell_Services__c> DelUpsellList = new List<Upsell_Services__c>();

44 List<Upsell_Services__c> UpsellList = new List<Upsell_Services__c>();

45 List<Account> updAcctList = new List<Account>();

46 Set<Account> updAccountSet = new Set<Account>();

47 //Declare a set to hold the account category number

48 successRecordscount = successRecordscount + Scope.size();

49 Map<Id, Account> updAcctMap = new Map<Id, Account>();

50 updAcctMap.putAll(scope);

51 

52 String Separator = '::';

53 String AccValue;

54 String MktSegment;

55 String Category;

56 String DecToString;

57 String NewAccValue;

58 

59 

60 Set<Id> AccId = new Set<Id>();

61 map<Id,String> AccMap = new map<Id,String>();

62 for (Account a : Scope) {

63 if (a.Market_Segment__c != '' && a.Market_Segment__c != null) {

64 //The separator xxx is added just in case if an account is not having

65 AccValue = a.Market_Segment__c + Separator + 'xxx';

66 AccMap.put(a.Id, AccValue);

67 a.Include_for_Upsell__c = false;

68 }

69 }

70 system.debug('Account Map after Account Loop is' + AccMap);

71 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c IN :AccMap.keySet() and Active__c = true]) {

72 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0) {

73 AccValue = AccMap.get(AccServ.Account__c);

74 AccValue = AccValue + Separator + AccServ.Category_Number__c;

75 AccMap.put(AccServ.Account__c, AccValue);

76 }

77 

78 

79 }

80 system.debug('Account Map after Services Loop is' + AccMap);

81 //Get the existing upsell list for the account and delete it

82 DelUpsellList = [Select Id from Upsell_Services__c where Account__c IN :AccMap.keySet()];

83 

84 //Create the new Upsell

85 for (Id i : AccMap.keySet()) {

86 NewAccValue = AccMap.get(i);

87 system.debug('String Value is' + NewAccValue);

88 MktSegment = NewAccValue.substring(0,NewAccValue.indexOf(Separator));

89 Category = NewAccValue.substring(NewAccValue.indexOf(Separator));

90 system.debug('Category value is' + Category);

91 for (Servics_by_Market_Segment__c MktServ : MktSegList) {

92 if (NewAccValue != '' && NewAccValue != null) {

93 if (MktServ.Market_Segment_Name__c.contains(MktSegment)) {

94 DecToString = MktServ.Category_Number__c.toPlainString();

95 if (!Category.contains(DecToString)) {

96 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = i,

97 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,

98 Category_Description__c = MktServ.Category_Description__c);

99 UpsellList.add(newUpsellRec);

100 }

101 }

102 }

103 }

104 }

105 

106 

107 /*

108 for(Account Acc : Scope)

109 {

110 DelUpsellList.addAll([Select Id from Upsell_Services__c where Account__c =:Acc.Id]);

111 Set<Decimal> CategorySet = new Set<Decimal>();

112 //Get the Account Services Category Number in a Set.

113 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c = :Acc.Id and Active__c = true]) {

114 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0)

115 CategorySet.add(AccServ.Category_Number__c);

116 }

117 

118 for (Servics_by_Market_Segment__c MktServ : [Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c

119 where Market_Segment_Name__c INCLUDES (:Acc.Market_Segment__c) AND Category_Number__c NOT IN :CategorySet ]) {

120 //Create new Upsell Record for this account

121 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = Acc.Id,

122 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,

123 Category_Description__c = MktServ.Category_Description__c);

124 UpsellList.add(newUpsellRec);

125 }

126 //Set the account back

127 Acc.Include_for_Upsell__c = false;

128 

129 } */

130 

131 if (DelUpsellList.size() > 0) {

132 delete DelUpsellList;

133 }

134 

135 List<Database.SaveResult> upsellResults = Database.insert(UpsellList, false);

136 system.debug('Upsell Results is:'+upsellResults);

137 for (Integer i = 0; i < upsellResults.size(); i++) {

138 if (!upsellResults[i].isSuccess()) {

139 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ UpsellList[i].Account__c + ' . The error reported is: ' +upsellResults[i].getErrors()[0].getMessage() + '\n';

140 

141 }

142 else {

143 //SuccessRecords

144 updAccountSet.add(updAcctMap.get(UpsellList[i].Account__c));

145 }

146 }

147 for(Account a : updAccountSet) {

148 a.Include_for_Upsell__c = false;

149 updAcctList.add(a);

150 }

151 List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);

152 for (Integer i = 0; i < AcctUpdResults.size(); i++) {

153 if (!AcctUpdResults[i].isSuccess()) {

154 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ updAcctList[i].Id + ' . The error reported is: ' +AcctUpdResults[i].getErrors()[0].getMessage() + '\n';

155 failedRecCount++;

156 

157 }

158 else {

159 //SuccessRecords

160 successRecCount++;

161 }

162 }

163 

164 /*

165 if (UpsellList.size() > 0) {

166 

167 insert UpsellList;

168 update scope;

169 }*/

170 }

171 

172 global void finish(Database.BatchableContext BC)

173 {

174 AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,

175 TotalJobItems, CreatedBy.Email from AsyncApexJob where Id = :BC.getJobId()];

176 Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

177 String[] toAddresses = new String[] {a.CreatedBy.Email};

178 

179 /*for(User users: [Select email from User where Profile.Name in :USER_PROFILE_TO_NOTIFY and IsActive = true]){

180 toAddresses.add(users.email);

181 } */

182 toAddresses.add('CRMCoordinators@transpac.com.au');

183 mail.setToAddresses(toAddresses);

184 mail.setSubject('UpsellBatchApex Job Processed');

185 String bodyText = 'BATCH\n\n Create Upsell Services batch Apex job processed ' + a.TotalJobItems + ' batches with '+ a.NumberOfErrors + ' critical batch failures.\n\nRECORDS\n\n';

186 bodyText = bodyText + 'Number of successful Upsell Services created: '+successRecCount + '. Number of failed records: '+failedRecCount;

187 if (failedRecCount > 0) {

188 bodyText = bodyText + '\n\n Failure Summary : \n' + failureMsg;

189 }

190 mail.setPlainTextBody (bodyText);

191 Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

192 }

193 }

I am receiving an error when the this scheduled APEX task runs. Apex Class = UpsellBatchApex Create Upsell Services batch Apex job processed 2 batches with 1 critical batch failures. Error: First error: Too many DML rows: 10001

The issue seems to occur on line 151 of the Class.

151   List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);        

152   for (Integer i = 0; i < AcctUpdResults.size(); i++) {           

153   if (!AcctUpdResults[i].isSuccess()) {

I need to figure out how to find out the list size using list.size() method.

Then I need to split the list so the split doesn't cross it to more than 10,000 records. I need to run a loop upto the split list size then store the records in another list and then run the database.update for the split list. I need to  store the index of the loop upto where the list is run then run the next loop from that index till next defined split / list.size() and store the records in list again. Does anyone know how I can code the APEX to do this? Any help is greatly appreciated.

Full code is below:

1 global class UpsellBatchApex implements Database.Batchable<sObject>,Database.Stateful

2 {

3 global Integer failedRecordscount {get; set;}

4 global Integer successRecordscount {get;set;}

5 //This query variable is only to hold the query string that is passed over by batch scheduler/EmailServiceHandler class

6 global final String AccountQuery;

7 private final boolean IsTest;

8 global List<Servics_by_Market_Segment__c> MktSegList;

9 private static final Set<String> USER_PROFILE_TO_NOTIFY;

10 private static final Set<String> ACC_RECORDTYPES_DEVNAME_TO_INCLUDE;

11 String failureMsg;

12 integer failedRecCount = 0;

13 integer successRecCount = 0;

14 

15 static

16 {

17 USER_PROFILE_TO_NOTIFY = new Set<String>();

18 USER_PROFILE_TO_NOTIFY.add('System Administrator');

19 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE = new Set<String>();

20 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer');

21 ACC_RECORDTYPES_DEVNAME_TO_INCLUDE.add('Contact_Centre_Customer_Read_Only');

22 }

23 //Constructor:

24 //global UpsellBatchApex(String Q,String Obj,boolean b)

25 global UpsellBatchApex()

26 {

27 failedRecordscount = 0;

28 successRecordscount = 0;

29 AccountQuery = 'Select Id, Market_Segment__c, RecordType.DeveloperName, Include_for_Upsell__c from Account where Include_for_Upsell__c = true and RecordType.DeveloperName in :ACC_RECORDTYPES_DEVNAME_TO_INCLUDE AND Market_Segment__c != null';

30 MktSegList = new List<Servics_by_Market_Segment__c>([Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c]);

31 

32 //IsTest = b;

33 }

34 //Here we could either use querylocator or Query Iterator.Query Iterator enforces governor limits of 50k

35 //retrieved records.Since there are no complex processing, used query locator.

36 global Database.querylocator start(Database.BatchableContext BC)

37 { 38 return Database.getQueryLocator(AccountQuery);

39 }

40 

41 global void execute(Database.BatchableContext BC, List <Account> Scope)

42 {

43 List<Upsell_Services__c> DelUpsellList = new List<Upsell_Services__c>();

44 List<Upsell_Services__c> UpsellList = new List<Upsell_Services__c>();

45 List<Account> updAcctList = new List<Account>();

46 Set<Account> updAccountSet = new Set<Account>();

47 //Declare a set to hold the account category number

48 successRecordscount = successRecordscount + Scope.size();

49 Map<Id, Account> updAcctMap = new Map<Id, Account>();

50 updAcctMap.putAll(scope);

51 

52 String Separator = '::';

53 String AccValue;

54 String MktSegment;

55 String Category;

56 String DecToString;

57 String NewAccValue;

58 

59 

60 Set<Id> AccId = new Set<Id>();

61 map<Id,String> AccMap = new map<Id,String>();

62 for (Account a : Scope) {

63 if (a.Market_Segment__c != '' && a.Market_Segment__c != null) {

64 //The separator xxx is added just in case if an account is not having

65 AccValue = a.Market_Segment__c + Separator + 'xxx';

66 AccMap.put(a.Id, AccValue);

67 a.Include_for_Upsell__c = false;

68 }

69 }

70 system.debug('Account Map after Account Loop is' + AccMap);

71 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c IN :AccMap.keySet() and Active__c = true]) {

72 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0) {

73 AccValue = AccMap.get(AccServ.Account__c);

74 AccValue = AccValue + Separator + AccServ.Category_Number__c;

75 AccMap.put(AccServ.Account__c, AccValue);

76 }

77 

78 

79 }

80 system.debug('Account Map after Services Loop is' + AccMap);

81 //Get the existing upsell list for the account and delete it

82 DelUpsellList = [Select Id from Upsell_Services__c where Account__c IN :AccMap.keySet()];

83 

84 //Create the new Upsell

85 for (Id i : AccMap.keySet()) {

86 NewAccValue = AccMap.get(i);

87 system.debug('String Value is' + NewAccValue);

88 MktSegment = NewAccValue.substring(0,NewAccValue.indexOf(Separator));

89 Category = NewAccValue.substring(NewAccValue.indexOf(Separator));

90 system.debug('Category value is' + Category);

91 for (Servics_by_Market_Segment__c MktServ : MktSegList) {

92 if (NewAccValue != '' && NewAccValue != null) {

93 if (MktServ.Market_Segment_Name__c.contains(MktSegment)) {

94 DecToString = MktServ.Category_Number__c.toPlainString();

95 if (!Category.contains(DecToString)) {

96 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = i,

97 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,

98 Category_Description__c = MktServ.Category_Description__c);

99 UpsellList.add(newUpsellRec);

100 }

101 }

102 }

103 }

104 }

105 

106 

107 /*

108 for(Account Acc : Scope)

109 {

110 DelUpsellList.addAll([Select Id from Upsell_Services__c where Account__c =:Acc.Id]);

111 Set<Decimal> CategorySet = new Set<Decimal>();

112 //Get the Account Services Category Number in a Set.

113 for (CSC_Services__c AccServ : [Select Id, Category_Number__c, Account__c from CSC_Services__c where Account__c = :Acc.Id and Active__c = true]) {

114 if (AccServ.Category_Number__c != null && AccServ.Category_Number__c > 0)

115 CategorySet.add(AccServ.Category_Number__c);

116 }

117 

118 for (Servics_by_Market_Segment__c MktServ : [Select Id, Category_Description__c, Category_Number__c, Market_Segment_Name__c, Waste_Stream__c from Servics_by_Market_Segment__c

119 where Market_Segment_Name__c INCLUDES (:Acc.Market_Segment__c) AND Category_Number__c NOT IN :CategorySet ]) {

120 //Create new Upsell Record for this account

121 Upsell_Services__c newUpsellRec = new Upsell_Services__c ( Name = MktServ.Category_Description__c, Account__c = Acc.Id,

122 Category_Number__c = MktServ.Category_Number__c, Waste_Stream__c = MktServ.Waste_Stream__c,

123 Category_Description__c = MktServ.Category_Description__c);

124 UpsellList.add(newUpsellRec);

125 }

126 //Set the account back

127 Acc.Include_for_Upsell__c = false;

128 

129 } */

130 

131 if (DelUpsellList.size() > 0) {

132 delete DelUpsellList;

133 }

134 

135 List<Database.SaveResult> upsellResults = Database.insert(UpsellList, false);

136 system.debug('Upsell Results is:'+upsellResults);

137 for (Integer i = 0; i < upsellResults.size(); i++) {

138 if (!upsellResults[i].isSuccess()) {

139 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ UpsellList[i].Account__c + ' . The error reported is: ' +upsellResults[i].getErrors()[0].getMessage() + '\n';

140 

141 }

142 else {

143 //SuccessRecords

144 updAccountSet.add(updAcctMap.get(UpsellList[i].Account__c));

145 }

146 }

147 for(Account a : updAccountSet) {

148 a.Include_for_Upsell__c = false;

149 updAcctList.add(a);

150 }

151 List<Database.SaveResult> AcctUpdResults = Database.update(updAcctList,false);

152 for (Integer i = 0; i < AcctUpdResults.size(); i++) {

153 if (!AcctUpdResults[i].isSuccess()) {

154 failureMsg = failureMsg + 'Error: Needs your attention - Could not create Upsell Record for Account Id: '+ updAcctList[i].Id + ' . The error reported is: ' +AcctUpdResults[i].getErrors()[0].getMessage() + '\n';

155 failedRecCount++;

156 

157 }

158 else {

159 //SuccessRecords

160 successRecCount++;

161 }

162 }

163 

164 /*

165 if (UpsellList.size() > 0) {

166 

167 insert UpsellList;

168 update scope;

169 }*/

170 }

171 

172 global void finish(Database.BatchableContext BC)

173 {

174 AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,

175 TotalJobItems, CreatedBy.Email from AsyncApexJob where Id = :BC.getJobId()];

176 Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

177 String[] toAddresses = new String[] {a.CreatedBy.Email};

178 

179 /*for(User users: [Select email from User where Profile.Name in :USER_PROFILE_TO_NOTIFY and IsActive = true]){

180 toAddresses.add(users.email);

181 } */

182 toAddresses.add('CRMCoordinators@transpac.com.au');

183 mail.setToAddresses(toAddresses);

184 mail.setSubject('UpsellBatchApex Job Processed');

185 String bodyText = 'BATCH\n\n Create Upsell Services batch Apex job processed ' + a.TotalJobItems + ' batches with '+ a.NumberOfErrors + ' critical batch failures.\n\nRECORDS\n\n';

186 bodyText = bodyText + 'Number of successful Upsell Services created: '+successRecCount + '. Number of failed records: '+failedRecCount;

187 if (failedRecCount > 0) {

188 bodyText = bodyText + '\n\n Failure Summary : \n' + failureMsg;

189 }

190 mail.setPlainTextBody (bodyText);

191 Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

192 }

193 }