Apex Code Development You need to sign in to do that
Don't have an account?
Haroon_jva Stumped on:Too many SOQL queries: 101
Hi All
I am a newbie writing apex.I understand why I am getting this but I can not find a work around. I am writing a trigger that creates a custom object every time a new opportunity is created. The possible workaround I may have, is to pull all the data of Rep_assignments__c in a list and then use logic to get the values I need. I have about 27,000 record in there and do not find it an effective way to program. Any suggestions are appreciated,
Thanks
Here is my code:
I am calling this from a trigger.
public static void setpipeline (Opportunity [] opps)
{
Date maxvalue = NULL ;
//build list of records
list<Rep_assignments__c> Records = new list<Rep_assignments__c>() ;
list <date> dates = new List<date>();
List <print_pipeline__c> pipelinetoinsert = new List<print_pipeline__c>();
//need to put list of opportunities
list<Opportunity> Oppty = new List<Opportunity> ();
//build list of records of rep assignments
for (Opportunity z:opps)
{
if (z.RecordTypeId ==record_type && z.integration__c <> 1)
records = [select r.id, r.Rep1_id__c, r.Rep1_pct__c, r.Rep2_id__c, r.Rep2_pct__c, r.Rep3_id__c, r.Rep3_pct__c, r.Rep4_id__c, r.Rep4_pct__c, r.effective_date__c
from Rep_assignments__c r where r.brand__c = :z.brand__c and r.pub_id__c =:z.pub_id__c and r.effective_date__c <=:z.CloseDate ];
}
//if more than one record found, then we need to get the max date and the array ref number to get the correct rep assignment
if (records.size() !=0)
{
integer ref_num = 0;
date maxdate;
if (records.size() > 1)
{
for (integer i = 0; i < records.size(); i++)
{
dates.add((records.get(i).effective_date__c));
}
//get max date
maxdate = dates[0];
for (integer i = 0; i < dates.size(); i ++){
if (dates[i] > maxdate)
maxdate = dates[i];
}
}
//get ref number
for (integer i=0; i <Records.size(); i++){
if (records[i].Effective_Date__c == maxdate)
ref_num= i;
}
//create print pipe
for (Opportunity o:opps){
if (records.get(ref_num).rep1_id__c != null)
{
print_pipeline__c pipeline = new print_pipeline__c (Opportunity__c=o.id ,
Rep_assignment__c=records.get(ref_num).id, Sales_Rep__c=records.get(ref_num).Rep1_id__c,
Rep_split__c=records.get(ref_num).Rep1_pct__c );
pipelinetoinsert.add(pipeline);
}
if (records.get(ref_num).rep2_id__c != null){
print_pipeline__c pipeline = new print_pipeline__c (Opportunity__c=o.id , Rep_assignment__c=records.get(ref_num).id, Sales_Rep__c=records.get(ref_num).Rep2_id__c,
Rep_split__c=records.get(ref_num).Rep2_pct__c );
pipelinetoinsert.add(pipeline);
}
if (records.get(ref_num).rep3_id__c != null)
{
print_pipeline__c pipeline = new print_pipeline__c (Opportunity__c=o.id , Rep_assignment__c=records.get(ref_num).id, Sales_Rep__c=records.get(ref_num).Rep3_id__c,
Rep_split__c=records.get(ref_num).Rep3_pct__c );
pipelinetoinsert.add(pipeline);
}
if (records.get(ref_num).rep4_id__c != null)
{
print_pipeline__c pipeline = new print_pipeline__c (Opportunity__c=o.id , Rep_assignment__c=records.get(ref_num).id, Sales_Rep__c=records.get(ref_num).Rep4_id__c,
Rep_split__c=records.get(ref_num).Rep4_pct__c );
pipelinetoinsert.add(pipeline);
}
}
if (!pipelinetoinsert.isEmpty())
database.insert(pipelinetoinsert);
}
}
Thanks guys I was able to solve this by using this code below. I needed to use a map and query the child object in one statement.
I was trying to query rep_assignments for each opportunity:
which is related as
opportunity->brands->rep_assignments
-------------------------------------------
// method to insert new records
public static void setpipeline (Opportunity [] opps)
{
Date maxvalue = NULL ;
//build list of records
list<Rep_assignments__c> Records = new list<Rep_assignments__c>() ;
list <date> dates = new List<date>();
List <print_pipeline__c> pipelinetoinsert = new List<print_pipeline__c>();
//need to put list of opportunities
list <Rep_assignments__c> records_temp = new list<Rep_assignments__c>();
//put brand id and corresponding opportunity in map
map <id, opportunity> brand_opp_map = new map<id, opportunity>();
for (opportunity o : [select brand__c, pub_id__c, CloseDate from opportunity where id in :opps] )
brand_opp_map.put(o.brand__c, new opportunity (id=o.id, pub_id_temp__c=o.pub_id__c,
brand__c=o.brand__c ,CloseDate=o.CloseDate ));
//get list of brands with a rep assignment
list <brand__c> mybrands = [Select b.Id, (Select Id, brand__c, pub_id__c, Rep1_id__c, Rep1_msg_id__c,
Rep1_pct__c, Rep2_id__c, Rep2_msg_id__c, Rep2_pct__c, Rep3_id__c, Rep3_pct__c, Rep4_id__c, Rep4_pct__c, effective_date__c From Rep_assignment__r) From Brand__c b where id in :brand_opp_map.keyset()];
//get a list of rep assignments linked to a brand
for (brand__c br : mybrands ){
for (Rep_assignments__c r : br.Rep_assignment__r){
records_temp.add(r);
}
}
//clean list of records to get the correct assignment
//loop through opportunity, compare each opportunity to record_temp, if match then popluate the record list
for (Opportunity o : brand_opp_map.values())
{
for (integer z = 0; z < records_temp.size(); z ++ )
{
if (o.brand__c == records_temp[z].brand__c && o.pub_id_temp__c == records_temp[z].pub_id__c && records_temp[z].effective_date__c <=o.closedate )
records.add(records_temp[z]);
}
}
All Answers
The short answer is - you should never have a SELECT statement inside of a loop.
The general way to get around this is to build lists that define the records you want to query, use the SOQL "IN" term to pull all relevent objects, then use maps to relate records to the corresponding filters.
Depending on the number of records, you may need to defer some or all of the work to a batch process.
The long answer is to learn more about Apex design patterns and how to code to limits :-)
Dan
if (z.RecordTypeId ==record_type && z.integration__c <> 1)
records = [select r.id, r.Rep1_id__c, r.Rep1_pct__c, r.Rep2_id__c, r.Rep2_pct__c, r.Rep3_id__c, r.Rep3_pct__c, r.Rep4_id__c, r.Rep4_pct__c, r.effective_date__c
from Rep_assignments__c r where r.brand__c = :z.brand__c and r.pub_id__c =:z.pub_id__c and r.effective_date__c <=:z.CloseDate ];
Take this soql out of the for loop.....Use a list of Ids for the Rep_Assignments__c thn write the for loop
Google 'salesforce governor limits' and 'salesforce apex best practices' for further information. This is something you need to have a grasp on if planning to use apex - due to its importance, the nature of this issue is well documented.
Thanks guys I was able to solve this by using this code below. I needed to use a map and query the child object in one statement.
I was trying to query rep_assignments for each opportunity:
which is related as
opportunity->brands->rep_assignments
-------------------------------------------
// method to insert new records
public static void setpipeline (Opportunity [] opps)
{
Date maxvalue = NULL ;
//build list of records
list<Rep_assignments__c> Records = new list<Rep_assignments__c>() ;
list <date> dates = new List<date>();
List <print_pipeline__c> pipelinetoinsert = new List<print_pipeline__c>();
//need to put list of opportunities
list <Rep_assignments__c> records_temp = new list<Rep_assignments__c>();
//put brand id and corresponding opportunity in map
map <id, opportunity> brand_opp_map = new map<id, opportunity>();
for (opportunity o : [select brand__c, pub_id__c, CloseDate from opportunity where id in :opps] )
brand_opp_map.put(o.brand__c, new opportunity (id=o.id, pub_id_temp__c=o.pub_id__c,
brand__c=o.brand__c ,CloseDate=o.CloseDate ));
//get list of brands with a rep assignment
list <brand__c> mybrands = [Select b.Id, (Select Id, brand__c, pub_id__c, Rep1_id__c, Rep1_msg_id__c,
Rep1_pct__c, Rep2_id__c, Rep2_msg_id__c, Rep2_pct__c, Rep3_id__c, Rep3_pct__c, Rep4_id__c, Rep4_pct__c, effective_date__c From Rep_assignment__r) From Brand__c b where id in :brand_opp_map.keyset()];
//get a list of rep assignments linked to a brand
for (brand__c br : mybrands ){
for (Rep_assignments__c r : br.Rep_assignment__r){
records_temp.add(r);
}
}
//clean list of records to get the correct assignment
//loop through opportunity, compare each opportunity to record_temp, if match then popluate the record list
for (Opportunity o : brand_opp_map.values())
{
for (integer z = 0; z < records_temp.size(); z ++ )
{
if (o.brand__c == records_temp[z].brand__c && o.pub_id_temp__c == records_temp[z].pub_id__c && records_temp[z].effective_date__c <=o.closedate )
records.add(records_temp[z]);
}
}