Apex Code Development You need to sign in to do that
Don't have an account?
Rachid El Kaddouri error: too many query rows 50001
Hello,
I'm having this problem that I can't solve. I tried everything but I can't find a solution. I hope someone can help me out here.
Problem description:
I'm getting this error: Too many query rows: 50001. This is because the functionality will insert up to 50000 records.
The functionality works fine but Salesforce gives me this error because Salesforce thinks its wrong.
How can i prevent this error? Some sources told me that batch apex should work but I didn't succeed in implementing this. Didn't really understood how that works.
Hope someone can help me.
Below you can find the full code:
I hope someone can help me out because I'm stuck at this point.
Kind regards
I'm having this problem that I can't solve. I tried everything but I can't find a solution. I hope someone can help me out here.
Problem description:
I'm getting this error: Too many query rows: 50001. This is because the functionality will insert up to 50000 records.
The functionality works fine but Salesforce gives me this error because Salesforce thinks its wrong.
How can i prevent this error? Some sources told me that batch apex should work but I didn't succeed in implementing this. Didn't really understood how that works.
Hope someone can help me.
Below you can find the full code:
public class Create_Project_SO {
string ProjectID = ApexPages.currentPage().getParameters().get('id');
list<PBSI__PBSI_Sales_Order_Line__c> NewSOLines = new List<PBSI__PBSI_Sales_Order_Line__c>();
list<PBSI__PBSI_Sales_Order__c> NewSOs = new List<PBSI__PBSI_Sales_Order__c>();
integer Amount_DifferentItems = 0;
list<integer> Amount_ProjectItem = new list<integer>();
list<string> Id_ProjectItem = new list<string>();
public void Fill_Item_Ids(){
Id_ProjectItem = new list<string>();
list<TOA_fs_Project__c> ProjectLine = [SELECT id, Project_Item_ID_1__c, Project_Item_ID_2__c, Project_Item_ID_3__c, Project_Item_ID_4__c,
Project_Item_ID_5__c, Project_Item_ID_6__c, Project_Item_ID_7__c, Project_Item_ID_8__c, Project_Item_ID_9__c, Project_Item_ID_10__c
FROM TOA_fs_Project__c WHERE Id =:ProjectID];
for( TOA_fs_Project__c a: ProjectLine){
Id_ProjectItem.add(a.Project_Item_ID_1__c);
Id_ProjectItem.add(a.Project_Item_ID_2__c);
Id_ProjectItem.add(a.Project_Item_ID_3__c);
Id_ProjectItem.add(a.Project_Item_ID_4__c);
Id_ProjectItem.add(a.Project_Item_ID_5__c);
Id_ProjectItem.add(a.Project_Item_ID_6__c);
Id_ProjectItem.add(a.Project_Item_ID_7__c);
Id_ProjectItem.add(a.Project_Item_ID_8__c);
Id_ProjectItem.add(a.Project_Item_ID_9__c);
Id_ProjectItem.add(a.Project_Item_ID_10__c);
}
}
public void Check_Amount_Items(){
Amount_DifferentItems = 0;
if(Id_ProjectItem[0]!= null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[1] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[2] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[3] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[4] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[5] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[6] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[7] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[8] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
if(Id_ProjectItem[9] != null){
Amount_DifferentItems = Amount_DifferentItems + 1;
}
}
public String ProjectLink{
get{
String ProjectLink = URL.getSalesforceBaseUrl().toExternalForm() +'/'+ ProjectID;
return ProjectLink;
}
}
public list<AggregateResult> GroupBy_Project_Locations{
get{
list<AggregateResult> OneOffPlanningLines = [SELECT SUM(Aantal_Item_1__c) Totaal_Item1, SUM(Aantal_Item_2__c) Totaal_Item2, SUM(Aantal_Item_3__c) Totaal_Item3,
SUM(Aantal_Item_4__c) Totaal_Item4, SUM(Aantal_Item_5__c) Totaal_Item5, SUM(Aantal_Item_6__c) Totaal_Item6, SUM(Aantal_Item_7__c) Totaal_Item7, SUM(Aantal_Item_8__c) Totaal_Item8,
SUM(Aantal_Item_9__c) Totaal_Item9, SUM(Aantal_Item_10__c) Totaal_Item10, Account_Location_ID__c FROM VG_One_Off_Planning__c WHERE Project__c =: ProjectID AND Project_Location__c != null AND Totale_Aantallen_Items__c != 0 GROUP BY Account_Location_ID__c];
return OneOffPlanningLines;
}
}
public list<AggregateResult> Total_Items{
get{
list<AggregateResult> OneOffPlanningLines = [SELECT SUM(Aantal_Item_1__c) Totaal_Item1, SUM(Aantal_Item_2__c) Totaal_Item2, SUM(Aantal_Item_3__c) Totaal_Item3,
SUM(Aantal_Item_4__c) Totaal_Item4, SUM(Aantal_Item_5__c) Totaal_Item5, SUM(Aantal_Item_6__c) Totaal_Item6, SUM(Aantal_Item_7__c) Totaal_Item7, SUM(Aantal_Item_8__c) Totaal_Item8,
SUM(Aantal_Item_9__c) Totaal_Item9, SUM(Aantal_Item_10__c) Totaal_Item10 FROM VG_One_Off_Planning__c WHERE Project__c =: ProjectID GROUP BY Project__c];
return OneOffPlanningLines;
}
}
Public string Get_WO(){
TOA_fs_Project__c Project = [SELECT VG_Work_Order__c FROM TOA_fs_Project__c WHERE id =: ProjectID];
string WO_ID = string.valueof(Project.VG_Work_Order__c);
Return WO_ID;
}
Public string Get_Main_Acc(){
TOA_fs_Project__c Project = [SELECT VG_Work_Order__c FROM TOA_fs_Project__c WHERE id =: ProjectID];
string WO_ID = string.valueof(Project.VG_Work_Order__c);
VG_Work_Order__c VGWO = [SELECT Sales_Order__c FROM VG_Work_Order__c WHERE id =: WO_ID];
string SO_ID = string.valueof(VGWO.Sales_Order__c);
VG_Sales_Order__c VGSO = [SELECT Account__c FROM VG_Sales_Order__c WHERE id =: SO_ID];
string Acc_ID = string.valueof(VGSO.Account__c);
return Acc_ID;
}
Public string Get_Account(string ProjectLocationID){
TOA_fs_Project_Location__c ProjectLocation = [SELECT Account_Location__c FROM TOA_fs_Project_Location__c WHERE id =: ProjectLocationID];
string AL_ID = string.valueof(ProjectLocation.Account_Location__c);
Account_Location__c AccountLocation = [SELECT Main_Account__c FROM Account_Location__c WHERE id =: AL_ID];
string Account_ID = string.valueof(AccountLocation.Main_Account__c);
return Account_ID;
}
Public string Get_AL(string ProjectLocationID){
TOA_fs_Project_Location__c ProjectLocation = [SELECT Account_Location__c FROM TOA_fs_Project_Location__c WHERE id =: ProjectLocationID];
string AL_ID = string.valueof(ProjectLocation.Account_Location__c);
return AL_ID;
}
public void CreateSOLocations(){
Fill_Item_Ids();
Check_Amount_Items();
String VGWO_ID = Get_WO();
String Customer_Account = Get_Main_Acc();
for(AggregateResult a: GroupBy_Project_Locations){
PBSI__PBSI_Sales_Order__c NewRecord = new PBSI__PBSI_Sales_Order__c();
id myid = string.valueof(a.get('Account_Location_ID__c'));
/// system.debug('Blabla ' + myid);
NewRecord.Account_Location__c = myid;
NewRecord.PBSI__Customer__c = Customer_Account;
NewRecord.VG_Work_Order__c = VGWO_ID;
NewRecord.Project__c = ProjectID;
NewSOs.add(NewRecord);
}
Insert NewSOs;
integer i = 0;
integer batchlimit = 0;
for(AggregateResult a: GroupBy_Project_Locations){
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item1')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item2')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item3')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item4')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item5')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item6')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item7')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item8')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item9')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item10')));
for(Integer c = 0; c < Amount_DifferentItems ;c++ ){
PBSI__PBSI_Sales_Order_Line__c NewSOLine = new PBSI__PBSI_Sales_Order_Line__c();
NewSOLine.PBSI__Sales_Order__c = NewSOs[i].id;
NewSOLine.PBSI__Item__c = Id_ProjectItem[c];
NewSOLine.PBSI__Quantity_Needed__c = Amount_ProjectItem[c];
if(NewSOLine.PBSI__Quantity_Needed__c != NULL){
NewSOLines.add(NewSOLine);
}
}
i++;
batchlimit++;
Amount_ProjectItem.clear();
/*if(batchlimit == 80){
batchlimit = 0;
Insert NewSOLines;
NewSOLines.clear();
}*/
}
Insert NewSOLines;
}
Public list<CreateSo_ItemObject> Shipment_Item_Overview{
get{
Fill_Item_Ids();
Check_Amount_Items();
for(AggregateResult a: Total_Items){
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item1')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item2')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item3')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item4')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item5')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item6')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item7')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item8')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item9')));
Amount_ProjectItem.add(integer.valueof(a.get('Totaal_Item10')));
}
list<CreateSo_ItemObject> ProjectItems = new List<CreateSo_ItemObject>();
for(integer i = 0; i < Amount_DifferentItems ;i++ ){
if(Id_ProjectItem[i] != NULL){
PBSI__PBSI_Item__c CurrentItem = [SELECT PBSI__Minimum_On_Hand__c, PBSI__Available_to_Promise__c, Name, PBSI__description__c FROM PBSI__PBSI_Item__c WHERE id =: Id_ProjectItem[i]];
CreateSo_ItemObject item = new CreateSo_ItemObject();
item.Name = CurrentItem.name;
item.Description = CurrentItem.PBSI__description__c;
item.Quantity = integer.valueof(CurrentItem.PBSI__Available_to_Promise__c);
item.QuantityOrdered = Amount_ProjectItem[i];
item.QuantityLeft = integer.valueof(CurrentItem.PBSI__Available_to_Promise__c) - Amount_ProjectItem[i];
if(item.QuantityLeft >= 0){
if(CurrentItem.PBSI__Minimum_On_Hand__c != null){
if(item.QuantityLeft < CurrentItem.PBSI__Minimum_On_Hand__c){
item.LineColor = 'Orange';
}else{
item.LineColor = 'Green';
}
}else{
item.LineColor = 'Green';
}
}else{
item.LineColor = 'Red';
}
ProjectItems.add(item);
}
}
Amount_ProjectItem.clear();
Return ProjectItems;
}
}
}
I hope someone can help me out because I'm stuck at this point.
Kind regards
https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm
If you are hitting this limit because you are processing a large number of records, you may need to look at batch apex. Alternatively, if this is a Visualforce page you could look at the readonly annotation, which allows you to query up to 1 million records as long as you aren't changing any of them.
Thank you for the fast reply. The readonly annotation sounds good. Do you, by any chance, have some more information about the readonly annotation? And how to use this.
Hope to hear from you.
Cheers!