You need to sign in to do that
Don't have an account?
GeorgeP
How to Bulkify Code (Too many SOQL queries error) ?
Hi, can anyone help as to how i can bulkify this code:
Context: A list of accounts is loaded (imported) into a custom VF page, the list is in a .CSV file. Afeter the list is uploaded to the VF page & viewable by the user they can insert the list into the SF database.
As part of the load step (or import step) the controller behind the VF page validates the accounts coming in to ensure that they actaully exist on the SF database (ie they have to be exisiting accounts) otherwise an error message is shown telling the user which account doen't exist and the insert button is disabled. However, the code gets a 'Too many sql queries error' when running with a largish file.
There are other things going on in the controllwe file so other SQL contribute to the limit being reached, but is there a way that a list can be validated in bulk as currently the list is validated acount by account? An import file of over 30 accounts is getting the too many SQL query error.
Here is the SOQL:
1st checks if the account exisit & then checks if the account is associated to an opportunity?
public static integer validateContractAccount(string contAcct, string SFOppId, string oppId_c, integer errorNum, integer linenum) {
List<Account> acctList = [SELECT ID, ParentId FROM Account WHERE Account.Sap_ca_Id__c =: contAcct LIMIT 1];
if (acctList.size() < 1 ) {
errorNum = errorNum + 1;
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR, errorNum +
') Contract Account ' + contAcct + ' for site ' + linenum + ' was not found in Salesforce. Data can not be inserted.');
ApexPages.addMessage(errorMessage);
}
else {
List<Contract_Account_Relationship__c> relation = [SELECT ID, Opportunity__c FROM Contract_Account_Relationship__c
WHERE Account__c =: acctList.get(0).ID and Opportunity__c =: SFOppId];
if (relation.size() < 1 ) {
errorNum = errorNum + 1;
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR, errorNum +
') Contract Account ' + contAcct + ' for site ' + linenum + ' is not associated to opportunity ' + oppId_c + '. Data can not be inserted.');
ApexPages.addMessage(errorMessage);
}
}
return errorNum;
}
The actual insert code (not shown) is bulkified but can the validation code be bulkified?
cheers
Context: A list of accounts is loaded (imported) into a custom VF page, the list is in a .CSV file. Afeter the list is uploaded to the VF page & viewable by the user they can insert the list into the SF database.
As part of the load step (or import step) the controller behind the VF page validates the accounts coming in to ensure that they actaully exist on the SF database (ie they have to be exisiting accounts) otherwise an error message is shown telling the user which account doen't exist and the insert button is disabled. However, the code gets a 'Too many sql queries error' when running with a largish file.
There are other things going on in the controllwe file so other SQL contribute to the limit being reached, but is there a way that a list can be validated in bulk as currently the list is validated acount by account? An import file of over 30 accounts is getting the too many SQL query error.
Here is the SOQL:
1st checks if the account exisit & then checks if the account is associated to an opportunity?
public static integer validateContractAccount(string contAcct, string SFOppId, string oppId_c, integer errorNum, integer linenum) {
List<Account> acctList = [SELECT ID, ParentId FROM Account WHERE Account.Sap_ca_Id__c =: contAcct LIMIT 1];
if (acctList.size() < 1 ) {
errorNum = errorNum + 1;
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR, errorNum +
') Contract Account ' + contAcct + ' for site ' + linenum + ' was not found in Salesforce. Data can not be inserted.');
ApexPages.addMessage(errorMessage);
}
else {
List<Contract_Account_Relationship__c> relation = [SELECT ID, Opportunity__c FROM Contract_Account_Relationship__c
WHERE Account__c =: acctList.get(0).ID and Opportunity__c =: SFOppId];
if (relation.size() < 1 ) {
errorNum = errorNum + 1;
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR, errorNum +
') Contract Account ' + contAcct + ' for site ' + linenum + ' is not associated to opportunity ' + oppId_c + '. Data can not be inserted.');
ApexPages.addMessage(errorMessage);
}
}
return errorNum;
}
The actual insert code (not shown) is bulkified but can the validation code be bulkified?
cheers
All Answers
If you are not doing any dml in the vf page then in the apex page use attribute Read Only = true and it will be helpful to remove your error.
Thanks
Gyani
Mirketa Software Pvt. Ltd.
http://www.mirketa.com
Can you expand on your last answer a bit more please. What limit are you talking about?
cheers
George
there is a limit that you can show only 50,000 record in the vf page but if in your org there is more than 50K records then in the controller in the soql that is use to fetch the record you have to the limit in the soql liki limit = 50000 and by using this you get only 50k record in the list that is use in the vf page and another option to remove the error you can use read only true attribute afte using this attribute record limit increase.
Thanks
Gyani
The program or Vf / Controller combination concerned is not showing any database records on the screen. The screen only shows the data in the records in the .csv file that is uploaded to SF via the VF page. The data is validated & if no errors the user can then insert the data to the database via pressing a button labelled 'insert data'.
In the validation or import step is where the code I posted gets run, ie it validates the incoming uploaded data. The data or .csv rows have approx 70 fields in them so a fair bit of validation gets done though not all of it requiring SOQL, a lot is format & number checks, picklists checks etc.
As such I'm not sure that your suggestion will help as fetching is only limited to the few rows that are uploaded.
cheers
George
Bit the bullet & re-wrote the code to query all accounts at once & still geive detailed error messages as per follows. This now just gets called after all the data has been read in as opposed to record by record, with me adding the incoming accounts to the acctValues list as they are read in.
============
public void validateContractAccount() {
boolean isInSet;
Set<String> acctset = new Set<String>();
List<Account> acctList = [SELECT ID, ParentId, Sap_ca_Id__c FROM Account WHERE Account.Sap_ca_Id__c in: acctValues];
if (acctList.size() != acctValues.size()) {
for (integer i=0; i<acctList.size(); i++){
acctSet.add(string.valueOf(acctList.get(i).Sap_Ca_Id__c));
}
isInSet = false;
for (integer i=0; i<acctValues.size(); i++){
isInSet = acctSet.contains(acctValues.get(i));
if (!isInSet) {
errorNum = errorNum + 1;
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR, errorNum +
') Contract Account ' + acctValues.get(i) + ' for site ' + (i + 1) + ' was not found in Salesforce. Data can not be inserted');
ApexPages.addMessage(errorMessage);
}
}
}
============