function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
willjwillj 

Too many SOL queries even with no queries in loop - please help

Getting too many soql queries 101 even though no queries in loop.  how do I overcome this?  very frustrating.

 

code is long so divided into two posts.

 

trigger trac_Lead on Lead (before insert, before update, after insert, after update) {
		
	/*****************
	 * Before Insert *
	 *****************/
	 
	if(trigger.isInsert && trigger.isBefore) {
		// Perform SIC mappings
		for(Lead l : trigger.new) {
			if (l.sic_lookup__c != null) {
				l.Industry = l.sic_description__c;
			}
		}
		
		// Query campaigns and rates
		List<Campaign> campaignList = new List<Campaign>();
		for(Campaign i : DBHelper.getCampaignListQuery()){
			if(i.IsActive==true){
				campaignList.add(i);
			}
		}

		List<User> userList = DBHelper.getUserDetailQuery();

		for (Lead l : trigger.new) {
			
			for(User u : userList){
				if(u.Id == UserInfo.getUserId()){
					if(u.Account_ID__c != null) {
						for(Account a : DBHelper.getAccountQuery()){ //check if accountid on user detail is valid
							if(a.Id == u.Account_ID__c){
								l.Payment_Advisor__c = u.Account_ID__c;
							}
						}
					}
				}				
			}
						
			// Inject default rates to app from campaign
	        if (l.Campaign__c != null && l.Product_Group__c != null && l.Processor__c != null) {
	      
	      		Campaign matchingCampaign;
	      		for(Campaign c : campaignList){
	      				if(c.Id==l.Campaign__c) {
	      					matchingCampaign = c;
	      					break;
	      				}
	      		}
	      		
	      		Id selectedRate;
	      		if(l.Processor__c == 'FD') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.FD_Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.FD_Instore_Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.FD_Online_Rate__c;
	      		}
	      		else if(l.Processor__c == 'Chase') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.Online_Rate__c;	
	      		}
	      		else if(l.Processor__c == 'PsiGate') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.PsiGate_Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.PsiGate_Instore_Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.PsiGate_Online_Rate__c;	
	      		}
	      		
	      		for(Rate__c r : DBHelper.getRateListQuery()){
	      				if(r.Id==selectedRate) {
							l.Account_Setup_Fee__c = r.Account_Setup_Fee__c;
							l.Amex_JCB_Fee_del__c = r.Amex_JCB_Fee_del__c;
							l.Annual_Fee__c = r.Annual_Fee__c;
							l.Chargeback__c = r.Chargeback_del__c;
							l.Cross_Border_Fee__c = r.Cross_Border_Fee__c;
							l.Deconversion_Cancellation_Fee_per_loc__c = r.Deconversion_Cancellation_Fee_per_loc__c;
							l.Discover_Rate__c = r.Discover_Rate__c;
							l.Discover_Rate_Amount__c = r.Discover_Transaction_Fee__c;
							l.Interac_Fee__c = r.Interac_Fee__c;
							l.MC_VISA_Authorization__c = r.MC_VISA_Authorization__c;
							l.MasterCard_Cross_Border_Transaction_Fee__c = r.MasterCard_Cross_Border_Transaction_Fee__c;
							l.MasterCard_Rate__c = r.MasterCard_Rate__c;
							l.MasterCard_Rate_Amount__c = r.MasterCard_Transaction_Fee__c;
							l.Monthly_Account_Fee__c = r.Monthly_Account_Fee__c;
							l.Monthly_Minimums__c = r.Monthly_Minimums__c;
							l.Non_Qualifying_Surcharge_Fee__c = r.Non_Qualifying_Surcharge_Fee__c;
							l.Other_Monthly_Fees__c = r.Other_Monthly_Fees__c;
							l.VISA_Debit_Rate__c = r.VISA_Debit_Rate__c;
							l.VISA_Debit_Rate_Amount__c = r.VISA_Debit_Transaction_Fee__c;
							l.VISA_MasterCard_Card_Brand_Fee__c = r.VISA_MasterCard_Card_Brand_Fee__c;
							l.Visa_Rate__c = r.VISA_Rate__c;
							l.VISA_Rate_Amount__c = r.VISA_Transaction_Fee__c;
							l.Voice_Authorization__c = r.Voice_Authorization__c;
							l.Wireless_Terminal_Setup_Fee__c = r.Wireless_Terminal_Setup_Fee__c;
	      					break;
	      				}
	      		}
	        }
		}
	}

	/****************
	 * After Insert *
	 ****************/
	 
	if(trigger.isInsert && trigger.isAfter) {
		// add campaign mappings
		List<CampaignMember> newCMs = new List<CampaignMember>();
		List<Web_Profile__c> newWPs = new List<Web_Profile__c>();
		
		for (Lead l : trigger.new) {
			if(UserInfo.getUserId() != '005U0000000NmC0'){
				newWPs.add(new Web_Profile__c(Applicant__c = l.id, Email__c = l.Email));
			}
			newCMs.add(new CampaignMember(leadid = l.id, campaignid = l.campaign__c));
		}
		insert newCMs;

		try {
			insert newWPs;
		} catch (DmlException de) {
			for (Lead l : trigger.new) {
				l.addError('Duplicate Email with an existing lead.');
			}
		}
			
	}
	
	/*****************
	 * Before Update *
	 *****************/
	
	if(trigger.isUpdate && trigger.isBefore) {
	
		Set<Id> leadIds = new Set<Id>();
		for (Lead lp : trigger.new) {
			leadIds.add(lp.id);
		}
	
		List<Lead_Product__c> leadProductQuery = null;
		
		leadProductQuery = [SELECT lead__c, product__r.isActive, 
										Card_Types__c,
										Doing_Business_As_DBA__c,
										Quantity__c,
										Term__c,
										Product__c,
										product__r.purchase_price__c,
										product__r.rental_price__c,
										product__r.lease_price__c,
										Batch_Processing__c,
										Customer_Profile_Management__c,
										Recurring_Billing__c,
										Shopping_Cart_Option__c,
										Virtual_Terminal__c,
										Auto_Close_Hour__c,
										Auto_Close_Minute__c,
										Auto_Close__c,
										Cash_Back_Enabled__c,
										Invoice_Number__c,
										QSR_Flag__c,
										Server_Clerk_ID__c,
										Tip_Option__c,
										Connection_Type__c,
										DNS1__c,
										DNS2__c,
										Gateway_Address__c,
										IP_Address__c,
										Request_Type__c,
										Service_Option__c,
										Special_Instructions__c,
										Subnet_Mask__c,
										Add_to_Existing_NetConnect__c,
										Application_Type__c,
										Existing_NetConnect_ID__c,
										Software_Name_Version__c,
										TID_s__c,
										VAR_Comm_Method__c,
										VAR_Name__c,
										Rental_Cost__c,
										Rental_Price__c,
										Lease_Price__c,
										Purchase_Price__c,
										Buy_Rate__c
										FROM Lead_Product__c WHERE lead__c IN :leadIds];
	
		Id LEAD_RT_UNCLAIMED;
		Id LEAD_RT_MANUAL;
		for(RecordType rt : DBHelper.getRecordTypes()) {
			if(rt.SobjectType=='Lead' && rt.name=='Unclaimed Lead'){
				LEAD_RT_UNCLAIMED = rt.id;
			}
			if(rt.SobjectType=='Lead' && rt.name=='Manually Entered'){
				LEAD_RT_MANUAL = rt.id;
			}
		}	
	
		List<CampaignMember> newCMs = new List<CampaignMember>();
		Set<String> delCMKeys = new Set<String>();
		
		List<Lead_Product__c> lps = new List<Lead_Product__c>();
		for(Lead_Product__c i : leadProductQuery){
			if(leadIds.contains((Id)i.lead__c)){
				lps.add(i);
			}
		}
		
		// check that lead has products
		Set<Id> leadsWithProducts = new Set<Id>();
		for(Lead_Product__c i : lps) {
			if(!leadsWithProducts.contains((Id)i.lead__c)){
				leadsWithProducts.add((Id)i.lead__c);				
			}
		}
		
		// check for lead products that have inactive products	
		Set<Id> leadsWithInactiveProducts = new Set<Id>();
		for(Lead_Product__c i : lps) {
			if(!leadsWithInactiveProducts.contains((Id)i.lead__c) && i.product__r.isActive == false){
				leadsWithInactiveProducts.add((Id)i.lead__c);
			}
		}
		
		// Query campaigns and rates
		List<Campaign> campaignList = new List<Campaign>();
		for(Campaign i : DBHelper.getCampaignListQuery()){
			if(i.IsActive==true){
				campaignList.add(i);
			}
		}											 
		
		for (Lead l : trigger.new) {
			
			// assign record type based on whether owner is queue or a user
			if(((String)l.ownerid).startsWith('00G')) { // queue
				l.recordtypeid = LEAD_RT_UNCLAIMED;
			} else if (l.recordtypeid == LEAD_RT_UNCLAIMED){ // user, needs RT update
				l.recordtypeid = LEAD_RT_MANUAL;
			}
			
			// lead is about to be converted
			if (l.isConverted && !trigger.oldMap.get(l.id).isConverted) {
				if(!leadsWithProducts.contains(l.id)) {
					l.addError(system.label.noLeadProducts);
				} else if(leadsWithInactiveProducts.contains(l.id)) {
					l.addError(system.label.leadProductWithInactiveProduct);
				}
				
				l.status_date_review_and_sign__c = Datetime.now();
			}
			
			if (l.sic_lookup__c != null) {
				l.Industry = l.sic_description__c;
			}
			
			// Find leads where the campaign has been changed
			if(l.campaign__c != trigger.oldMap.get(l.id).campaign__c) {
				newCMs.add(new CampaignMember(leadid = l.id, campaignid = l.campaign__c));
				if (trigger.oldmap.get(l.id).campaign__c != null) {
					delCMKeys.add(((String)trigger.oldmap.get(l.id).campaign__c).substring(0,15) + ((String)l.id).substring(0,15));
				}
			}
			
			// Update rates if campaign, processor, or product group changed
			Lead oldLead = Trigger.oldMap.get(l.ID);
	        if (l.Campaign__c != oldLead.Campaign__c || l.Product_Group__c != oldLead.Product_Group__c || l.Processor__c != oldLead.Processor__c) {
	      
	      		Campaign matchingCampaign;
	      		for(Campaign c : campaignList){
	      				if(c.Id==l.Campaign__c) {
	      					matchingCampaign = c;
	      					break;
	      				}
	      		}
	      		
	      		Id selectedRate;
	      		if(l.Processor__c == 'FD') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.FD_Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.FD_Instore_Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.FD_Online_Rate__c;
	      		}
	      		else if(l.Processor__c == 'Chase') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.Online_Rate__c;	
	      		}
	      		else if(l.Processor__c == 'PsiGate') {
	      			if(l.Product_Group__c == 'Mobile') selectedRate = matchingCampaign.PsiGate_Mobile_Rate__c;
	      			else if(l.Product_Group__c == 'In-Store') selectedRate = matchingCampaign.PsiGate_Instore_Rate__c;
	      			else if(l.Product_Group__c == 'Online') selectedRate = matchingCampaign.PsiGate_Online_Rate__c;	
	      		}
	      		
	      		for(Rate__c r : DBHelper.getRateListQuery()){
	      				if(r.Id==selectedRate) {
							l.Account_Setup_Fee__c = r.Account_Setup_Fee__c;
							l.Amex_JCB_Fee_del__c = r.Amex_JCB_Fee_del__c;
							l.Annual_Fee__c = r.Annual_Fee__c;
							l.Chargeback__c = r.Chargeback_del__c;
							l.Cross_Border_Fee__c = r.Cross_Border_Fee__c;
							l.Deconversion_Cancellation_Fee_per_loc__c = r.Deconversion_Cancellation_Fee_per_loc__c;
							l.Discover_Rate__c = r.Discover_Rate__c;
							l.Discover_Rate_Amount__c = r.Discover_Transaction_Fee__c;
							l.Interac_Fee__c = r.Interac_Fee__c;
							l.MC_VISA_Authorization__c = r.MC_VISA_Authorization__c;
							l.MasterCard_Cross_Border_Transaction_Fee__c = r.MasterCard_Cross_Border_Transaction_Fee__c;
							l.MasterCard_Rate__c = r.MasterCard_Rate__c;
							l.MasterCard_Rate_Amount__c = r.MasterCard_Transaction_Fee__c;
							l.Monthly_Account_Fee__c = r.Monthly_Account_Fee__c;
							l.Monthly_Minimums__c = r.Monthly_Minimums__c;
							l.Non_Qualifying_Surcharge_Fee__c = r.Non_Qualifying_Surcharge_Fee__c;
							l.Other_Monthly_Fees__c = r.Other_Monthly_Fees__c;
							l.VISA_Debit_Rate__c = r.VISA_Debit_Rate__c;
							l.VISA_Debit_Rate_Amount__c = r.VISA_Debit_Transaction_Fee__c;
							l.VISA_MasterCard_Card_Brand_Fee__c = r.VISA_MasterCard_Card_Brand_Fee__c;
							l.Visa_Rate__c = r.VISA_Rate__c;
							l.VISA_Rate_Amount__c = r.VISA_Transaction_Fee__c;
							l.Voice_Authorization__c = r.Voice_Authorization__c;
							l.Wireless_Terminal_Setup_Fee__c = r.Wireless_Terminal_Setup_Fee__c;
	      					break;
	      				}
	      		}
	        }
		}
		
		// add new campaign members
		try {
			insert newCMs;
		} catch (DmlException de) {
			// existing member...
		}
		
		// delete old campaign members
		List<CampaignMember> oldCMs = [SELECT id FROM CampaignMember WHERE key__c IN :delCMKeys];
		if (oldCMs.size() > 0) {
			delete oldCMs;
		}
	}

 

Best Answer chosen by Admin (Salesforce Developers) 
Abhay AroraAbhay Arora

You have to understand testing procedure

 


for(Integer i=0;i<9;i++) {
            Rate__c rate = new Rate__c(

this will cause 10 DMLS you need to make a list of rate and insert it after loop
instead of inserting leadproducts seperatly make a list and do a single insert

Lead_Product__c lp = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp;
        
        Lead_Product__c lp2 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Lease', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp2;
        
        Lead_Product__c lp3 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Rent - Chase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp3;
        
        Lead_Product__c lp4 = new Lead_Product__c(lead__c = l.id, product__c = p2.id, quantity__c = 1, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp4;    

Same for leads

Lead l = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'In-Store', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l;

        Lead l2 = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'Online', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l2;

Same for SIC

        
        SIC__c sic1 = new SIC__c(name = '1', description__c = 'industry1');
        insert sic1;
        
        SIC__c sic2 = new SIC__c(name = '2', description__c = 'industry2');
        insert sic2;

All Answers

willjwillj

second half.

 

/****************
	 * After Update *
	 ****************/
	
	if(trigger.isUpdate && trigger.isAfter) {
		Map<Id,Id> leadIdsWithAcct = new Map<Id,Id>();
		Map<Id,Id> leadIdsWithOpp = new Map<Id,Id>();
		Map<Id,Id> accountToLeadMap = new Map<Id,Id>();
		Map<Id,Id> oppToLeadMap = new Map<Id,Id>();
		Map<Id,Id> contactToLeadMap = new Map<Id,Id>();
		Set<Id> campaignIds = new Set<Id>();
		Map<Id,Campaign> campaigns = new Map<Id,Campaign>();
								
		for (Lead l : trigger.new) {
			// determine eligible leads
			if (l.IsConverted && !trigger.oldMap.get(l.id).isConverted) {
				// converted with an account?
				if(l.ConvertedAccountId != null) {
					leadIdsWithAcct.put(l.id,l.convertedAccountId);
					accountToLeadMap.put(l.convertedAccountId,l.id);
				}
				// converted with an opp?
				if( l.convertedOpportunityId != null) {
					leadIdsWithOpp.put(l.id,l.convertedOpportunityId);
					oppToLeadMap.put(l.convertedOpportunityId,l.id);
					campaignIds.add(l.campaign__c);					
				}
				// converted with a contact?
				if(l.ConvertedContactId != null) {
					contactToLeadMap.put(l.convertedContactId,l.id);
				}
			} 
		}
		
		if(contactToLeadMap.size() > 0) {
			List<Contact> primaryContacts = new List<Contact>();
			for(Contact i :  [SELECT Primary_Owner__c FROM Contact WHERE Id IN :contactToLeadMap.keySet()]) {
				primaryContacts.add(i);
			}
			for(Contact con : primaryContacts) {
				con.primary_owner__c = true;
			}
			
			update primaryContacts;
		}
		
		// find all campaigns associated with converted leads
		if(campaignIds.size() > 0) {
			campaigns = new Map<Id,Campaign>();
			for(Campaign i : DBHelper.getCampaignListQuery()){
				if(campaignIds.contains(i.id)){
					campaigns.put(i.id,i);
				}
			}	
		}
		
		// create contacts from Ownership entries and attach to converted Account
		if (leadIdsWithAcct.size() > 0) {
			List<Contact> newContacts = new List<Contact>();
			
			List<Ownership__c> ownerships = [SELECT OWNER_FIRST_NAME__C,
													NAME,
													//OWNER_LAST_NAME__C,
													DOING_BUSINESS_AS_DBA__C,
													PERCENT_OWNERSHIP__C,
													HOME_ADDRESS__C,
													HOME_PHONE__C,
													SIN__C,
													FILED_FOR_BANKRUPTCY__C,
													LEAD__C,
													PRIMARY_OWNER__C,
													DATE_OF_BIRTH__C,
													HOME_CITY__C,
													HOME_STATE__C,
													HOME_POSTAL_ZIP__C,
													ADDITIONAL_BUSINESS_PHONE__C,
													ADDITIONAL_BUSINESS_FAX__C,
													TYPE_OF_ID1__C,
													TYPE_OF_ID2__C,
													TYPE_OF_ID_OTHER1__C,
													TYPE_OF_ID_OTHER2__C,
													ID_NUMBER1__C,
													ID_NUMBER2__C,
													PLACE_OF_ISSUE1__C,
													PLACE_OF_ISSUE2__C,
													ARE_YOU_A_PROPERTY_OWNER__C,
													SOCIAL_INSURANCE_NUMBER__C,
													TITLE__C,
													FILED_FOR_BANKRUPTCY_NOT__C
			
											FROM Ownership__c
											WHERE Lead__c IN :leadIdsWithAcct.keySet()];
											
			for(Ownership__c own : ownerships) {
				newContacts.add(new Contact(accountid = leadIdsWithAcct.get(own.lead__c),
											FIRSTNAME = own.OWNER_FIRST_NAME__C,
											LASTNAME = own.NAME,
											PERCENT_OF_OWNERSHIP__C = own.PERCENT_OWNERSHIP__C,
											MAILINGSTREET = own.HOME_ADDRESS__C,
											PHONE = own.HOME_PHONE__C,
											HAVE_YOU_EVER_FILED_FOR_BANKRUPTCY__C = own.FILED_FOR_BANKRUPTCY__C,
											DATE_OF_BIRTH__C = own.DATE_OF_BIRTH__C,
											MAILINGCITY = own.HOME_CITY__C,
											MAILINGSTATE = own.HOME_STATE__C,
											MAILINGPOSTALCODE = own.HOME_POSTAL_ZIP__C,
											HOMEPHONE = own.ADDITIONAL_BUSINESS_PHONE__C,
											FAX = own.ADDITIONAL_BUSINESS_FAX__C,
											TYPE_OF_ID__C = own.TYPE_OF_ID1__C,
											TYPE_OF_ID2__C = own.TYPE_OF_ID2__C,
											TYPE_OF_ID_OTHER__C = own.TYPE_OF_ID_OTHER1__C,
											TYPE_OF_ID_2_OTHER__C = own.TYPE_OF_ID_OTHER2__C,
											ID_NUMBER__C = own.ID_NUMBER1__C,
											ID_NUMBER_2__C = own.ID_NUMBER2__C,
											PLACE_OF_ISSUE__C = own.PLACE_OF_ISSUE1__C,
											PLACE_OF_ISSUE_2__C = own.PLACE_OF_ISSUE2__C,
											ARE_YOU_A_PROPERTY_OWNER__C = own.ARE_YOU_A_PROPERTY_OWNER__C,
											SOCIAL_INSURANCE_NUMBER__C = own.SOCIAL_INSURANCE_NUMBER__C,
											TITLE = own.TITLE__C));
				
			}
			Set<Id> contactIds = new Set<Id>();
			
			
			for (Database.SaveResult sr : Database.insert(newContacts)) {
				if(sr.isSuccess()) {
					contactIds.add(sr.getId());
				}
			}
			
			if(contactIds.size() > 0) {
				List<OpportunityContactRole> newContactRoles = new List<OpportunityContactRole>();
				
				
				
				List<Contact> contactTemp = new List<Contact>();
				for(Contact i : [SELECT accountid FROM Contact WHERE Id IN :contactIds]) {
					contactTemp.add(i);
				}								
				for(Contact c : contactTemp) {
					newContactRoles.add(
						new OpportunityContactRole(contactid = c.id,
												   opportunityid = leadIdsWithOpp.get(accountToLeadMap.get(c.accountid)),
												   role = 'Owner'));
				}
				insert newContactRoles;
			}
		}
		System.debug('11.Number of Queries used in this apex code so far: ' + Limits.getQueries());
		System.debug('22.Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
		System.debug('33.Number of script statements used so far : ' +  Limits.getDmlStatements());
		
		// update opps with campaign details, and create opportunity line items from Lead Products, and attach to converted Opportunity
		if(leadIdsWithOpp.size() > 0) {		
			List<Opportunity> opps = [SELECT campaign__c FROM Opportunity WHERE Id IN :leadIdsWithOpp.values()];
			
			List<SalesLog__c> sls = DBHelper.getSalesLogQuery();
			for (Opportunity opp : opps) {
				if (trigger.newMap.get(oppToLeadMap.get(opp.id)).campaign__c != null) { // should never be blank
					opp.campaign__c = trigger.newMap.get(oppToLeadMap.get(opp.id)).campaign__c;
					opp.partner_hidden__c = campaigns.get(opp.campaign__c).partner__c;
					opp.referrer_hidden__c = campaigns.get(opp.campaign__c).referrer__c;
					opp.Applicant_s_Name__c = trigger.newMap.get(oppToLeadMap.get(opp.id)).convertedContactId;
				}
				for(SalesLog__c sl : sls){
					if(oppToLeadMap.get(opp.id) == sl.Lead__c){
						sl.Opportunity__c = opp.id;
					}
				}
			}
						
			update opps;
			update sls;
			
			// set up a product to pricebook map. WARNING: This assumes a single pricebook, single currency org
			Map<Id,Id> productToPBE = new Map<Id,Id>();
			for (PricebookEntry pbe : [SELECT product2id FROM PricebookEntry WHERE isActive = true]) {
				productToPBE.put(pbe.product2id,pbe.id);
			}
			
			List<OpportunityLineItem> newOppLineItems = new List<OpportunityLineItem>();
			
			List<Lead_Product__c> leadProductQuery = null;
			
			leadProductQuery = [SELECT lead__c, product__r.isActive, 
											Card_Types__c,
											Doing_Business_As_DBA__c,
											Quantity__c,
											Term__c,
											Product__c,
											product__r.purchase_price__c,
											product__r.rental_price__c,
											product__r.lease_price__c,
											Batch_Processing__c,
											Customer_Profile_Management__c,
											Recurring_Billing__c,
											Shopping_Cart_Option__c,
											Virtual_Terminal__c,
											Auto_Close_Hour__c,
											Auto_Close_Minute__c,
											Auto_Close__c,
											Cash_Back_Enabled__c,
											Invoice_Number__c,
											QSR_Flag__c,
											Server_Clerk_ID__c,
											Tip_Option__c,
											Connection_Type__c,
											DNS1__c,
											DNS2__c,
											Gateway_Address__c,
											IP_Address__c,
											Request_Type__c,
											Service_Option__c,
											Special_Instructions__c,
											Subnet_Mask__c,
											Add_to_Existing_NetConnect__c,
											Application_Type__c,
											Existing_NetConnect_ID__c,
											Software_Name_Version__c,
											TID_s__c,
											VAR_Comm_Method__c,
											VAR_Name__c,
											Rental_Cost__c,
											Rental_Price__c,
											Lease_Price__c,
											Purchase_Price__c,
											Buy_Rate__c
											FROM Lead_Product__c WHERE lead__c IN :leadIdsWithOpp.keySet()];		
							
			List<Lead_Product__c> leadProducts = new List<Lead_Product__c>();
			for(Lead_Product__c i : leadProductQuery){
				if(leadIdsWithOpp.keySet().contains((Id)i.lead__c)){
					leadProducts.add(i);
				}
			}
		  
			Decimal price = 0;								  
			for(Lead_Product__c lp : leadProducts) {
				// calculate the correct price based on type of lead product
				if(lp.term__c != null) {
					if(lp.term__c == 'Purchase') price = lp.purchase_price__c;
					if(lp.term__c == 'Lease') price = lp.lease_price__c;
					if(lp.term__c == 'Rent - Chase' || lp.term__c == 'Rent - Payfirma') price = lp.rental_price__c;
				}
				newOppLineItems.add(new OpportunityLineItem(opportunityid = leadIdsWithOpp.get(lp.lead__c),
															pricebookentryid = productToPBE.get(lp.product__c),
															Card_Types__c = lp.Card_Types__c,
															//Doing_Business_As_DBA__c = lp.Doing_Business_As_DBA__c,
															Quantity = lp.Quantity__c,
															Term__c = lp.Term__c,
															Rental_Cost__c = lp.Rental_Cost__c,
															Rental_Price__c = lp.Rental_Price__c,
															Lease_Price__c = lp.Lease_Price__c,
															Purchase_Price__c = lp.Purchase_Price__c,
															Buy_Rate__c = lp.Buy_Rate__c,
															Batch_Processing__c = lp.Batch_Processing__c,
															Customer_Profile_Management__c = lp.Customer_Profile_Management__c,
															Recurring_Billing__c = lp.Recurring_Billing__c,
															Shopping_Cart_Option__c = lp.Shopping_Cart_Option__c,
															Virtual_Terminal__c = lp.Virtual_Terminal__c,
															Auto_Close_Hour__c = lp.Auto_Close_Hour__c,
															Auto_Close_Minute__c = lp.Auto_Close_Minute__c,
															Auto_Close__c = lp.Auto_Close__c,
															Cash_Back_Enabled__c = lp.Cash_Back_Enabled__c,
															Invoice_Number__c = lp.Invoice_Number__c,
															QSR_Flag__c = lp.QSR_Flag__c,
															Server_Clerk_ID__c = lp.Server_Clerk_ID__c,
															Tip_Option__c = lp.Tip_Option__c,
															Connection_Type__c = lp.Connection_Type__c,
															DNS1__c = lp.DNS1__c,
															DNS2__c = lp.DNS2__c,
															Gateway_Address__c = lp.Gateway_Address__c,
															IP_Address__c = lp.IP_Address__c,
															Request_Type__c = lp.Request_Type__c,
															Service_Option__c = lp.Service_Option__c,
															Special_Instructions__c = lp.Special_Instructions__c,
															Subnet_Mask__c = lp.Subnet_Mask__c,
															Add_to_Existing_NetConnect__c = lp.Add_to_Existing_NetConnect__c,
															Application_Type__c = lp.Application_Type__c,
															Existing_NetConnect_ID__c = lp.Existing_NetConnect_ID__c,
															Software_Name_Version__c = lp.Software_Name_Version__c,
															TID_s__c = lp.TID_s__c,
															VAR_Comm_Method__c = lp.VAR_Comm_Method__c,
															VAR_Name__c = lp.VAR_Name__c,
															unitprice = price));
			}
			insert newOppLineItems;
		}
		
		System.debug('1.Number of Queries used in this apex code so far: ' + Limits.getQueries());
		System.debug('2.Number of rows queried in this apex code so far: ' + Limits.getDmlRows());
		System.debug('3.Number of script statements used so far : ' +  Limits.getDmlStatements());
		
		// clone notes
		if(contactToLeadMap.size() > 0) {
			
			List<Note> newNotes = new List<Note>();
			List<Note> notes = [SELECT Body, IsPrivate, OwnerId, ParentId, Title
								FROM Note
								WHERE parentid IN :contactToLeadMap.keySet()];
			if(notes.size() > 0) {
				Note tempNote;
				for(Note n : notes) {
					tempNote = n.clone(false,true,false,false);
					tempNote.parentId = leadIdsWithOpp.get(contactToLeadMap.get(n.parentId));
					newNotes.add(tempNote);
				}
				insert newNotes;
				delete notes;
			}
			
			// clone attachments
			List<Attachment> newAttachments = new List<Attachment>();
			List<Attachment> attachments = [SELECT Body, ContentType, Description, IsPrivate, Name, OwnerId, ParentId
											FROM Attachment
											WHERE parentid IN :contactToLeadMap.keySet()];
			if(attachments.size() > 0) {
				Attachment tempAtt;
				for(Attachment a : attachments) {
					tempAtt = a.clone(false,true,false,false);
					tempAtt.parentId = leadIdsWithOpp.get(contactToLeadMap.get(a.parentId));
					newAttachments.add(tempAtt);
				}
				insert newAttachments;
				delete attachments;
			}
		}
		
	} // end method
} // end class

 
Abhay AroraAbhay Arora

Just try using test.start() / test.stop()  for each insert,update or delete

 

Please mark it as solved if above is what you need
willjwillj

You meant putting those in my trigger test class? They are already in there...test class attached.  Is it my trigger or test class problem?  My guess is the error for too many queries 101 refers to the test class, not trigger right?  if it's trigger, it should be 21?..please shed some light on how to tackle this problem.  Thanks

 

@isTest(SeeAllData=true)
private class trac_Lead_Test {
    static testMethod void leadConversionTestsChase() {
        Id LEAD_RT_UNCLAIMED = [SELECT id FROM RecordType WHERE sobjecttype = 'Lead' AND name = 'Unclaimed Lead'].id;
		Id LEAD_RT_MANUAL = [SELECT id FROM RecordType WHERE sobjecttype = 'Lead' AND name = 'Manually Entered'].id;
		Id ACCT_RT_PARTNER = [SELECT id FROM RecordType WHERE sobjecttype = 'Account' AND name = 'Corporate Referral Partner'].id;
    	Id EMPLOYEE_RT = [SELECT id FROM RecordType WHERE sobjecttype = 'Account' AND name = 'Internal Employee'].id;        
        
        Profile pro = [SELECT id FROM Profile WHERE name = 'Standard User']; 
        User u = new User(username = 'TESTUSER1@bleh.com',
                          email = 'test@example.com',
                          lastname = 'test',
                          alias = 'test',
                          TimezoneSIDKey = 'America/Los_Angeles',
                          LocaleSIDKey = 'en_US',
                          EmailEncodingKey = 'UTF-8',
                          ProfileId = pro.id,
                          LanguageLocaleKey = 'en_US');
        
        Account a = new Account(name = 'Partner', type = 'Partner', recordTypeId = ACCT_RT_PARTNER);
        insert a;
        
        Account pa = new Account(name = 'PA', recordtypeid = EMPLOYEE_RT, Method_of_Payment__c = 'EFT', Bank_Account_Number1__c = '333', Institution_Number_Partner__c = '111', Transit_Number_Partner__c = '32343' );
        insert pa;
        
        String[] rates = new String[9];
        for(Integer i=0;i<9;i++) {
	        Rate__c rate = new Rate__c(
	        							Account_Setup_Fee__c = i,
										Amex_JCB_Fee_del__c = i,
										Annual_Fee__c = i,
										Chargeback_del__c = i,
										Cross_Border_Fee__c = i,
										Deconversion_Cancellation_Fee_per_loc__c = i,
										Discover_Rate__c = i,
										Discover_Transaction_Fee__c = i,
										Interac_Fee__c = i,
										MC_VISA_Authorization__c = i,
										MasterCard_Cross_Border_Transaction_Fee__c = i,
										MasterCard_Rate__c = i,
										MasterCard_Transaction_Fee__c = i,
										Monthly_Account_Fee__c = i,
										Monthly_Minimums__c = i,
										Non_Qualifying_Surcharge_Fee__c = i,
										Other_Monthly_Fees__c = i,
										VISA_Debit_Rate__c = i,
										VISA_Debit_Transaction_Fee__c = i,
										VISA_MasterCard_Card_Brand_Fee__c = i,
										VISA_Rate__c = i,
										VISA_Transaction_Fee__c = i,
										Voice_Authorization__c = i,
										Wireless_Terminal_Setup_Fee__c = i       
	        );
	        insert rate;
	        rates[i]=rate.Id;
        }
        
        Product2 p = new Product2(name = 'Test Product', isActive = true, purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert p;
        
        Product2 p2 = new Product2(name = 'Test Product', isActive = false, purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert p2;
        
        Campaign c = new Campaign(name = 'convTest',
        						  campaign_code__c = 'AAAA',
        						  manager_percentage__c = 1,
        						  operations_percentage__c = 2,
        				  		  pa_percentage__c = 3,
        		  				  payfirma__c = 4,
        				 		  rpm_percentage__c = 5,
        				 		  referrer__c = a.id,
        				 		  partner__c = a.id,
        				 		  Mobile_Rate__c = rates[0],
        				 		  Rate__c = rates[1],
        				 		  Online_Rate__c = rates[2],
        				 		  FD_Mobile_Rate__c = rates[3],
        				 		  FD_Instore_Rate__c = rates[4],
        				 		  FD_Online_Rate__c = rates[5],
        				 		  PsiGate_Mobile_Rate__c = rates[6],
        				 		  PsiGate_Instore_Rate__c = rates[7],
        				 		  PsiGate_Online_Rate__c = rates[8],
        				 		  isActive = TRUE);
        insert c;
        
        Campaign c1 = new Campaign(name = 'convTest',
        						  campaign_code__c = 'BAAAA',
        						  manager_percentage__c = 1,
        						  operations_percentage__c = 2,
        				  		  pa_percentage__c = 3,
        		  				  payfirma__c = 4,
        				 		  rpm_percentage__c = 5,
        				 		  referrer__c = a.id,
        				 		  partner__c = a.id,
        				 		  Mobile_Rate__c = rates[0],
        				 		  Rate__c = rates[1],
        				 		  Online_Rate__c = rates[2],
        				 		  FD_Mobile_Rate__c = rates[3],
        				 		  FD_Instore_Rate__c = rates[4],
        				 		  FD_Online_Rate__c = rates[5],
        				 		  PsiGate_Mobile_Rate__c = rates[6],
        				 		  PsiGate_Instore_Rate__c = rates[7],
        				 		  PsiGate_Online_Rate__c = rates[8],
        				 		  isActive = TRUE);
        insert c1;
                
        List<Campaign_Product__c> newCPs = new List<Campaign_Product__c>();
			
		newCPs.add(new Campaign_Product__c(Campaign__c = c.id, Product__c = p2.Id));

		insert newCPs;
        
        // Pricebook entry auto-created by trigger
        
        Id qid = [SELECT id FROM Group WHERE Type = 'Queue' AND name = 'Default Lead Queue'].id;
        
        SIC__c sic1 = new SIC__c(name = '1', description__c = 'industry1');
        insert sic1;
        
        SIC__c sic2 = new SIC__c(name = '2', description__c = 'industry2');
        insert sic2;
        
        Lead l = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'In-Store', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l;

        Lead l2 = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'Online', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l2;
        
        l = [SELECT ownerid, recordtypeid, sic_lookup__c, industry FROM Lead WHERE Id = :l.id];
        
        test.startTest();
        
        system.assertEquals(LEAD_RT_UNCLAIMED, l.recordtypeid);
        
        system.assertEquals(sic1.description__c, l.industry);
        
        l.ownerid = UserInfo.getUserId();
        l.sic_lookup__c = sic2.id;
        
        update l;
        
        l = [SELECT company, lastname, campaign__c, recordtypeid, ownerid, status, industry FROM Lead WHERE Id = :l.id];
        
        system.assertEquals(LEAD_RT_MANUAL, l.recordtypeid);
        
        system.assertEquals(sic2.description__c, l.industry);
        
        Lead_Product__c lp = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp;
        
        Lead_Product__c lp2 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Lease', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp2;
        
        Lead_Product__c lp3 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Rent - Chase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp3;
        
        Lead_Product__c lp4 = new Lead_Product__c(lead__c = l.id, product__c = p2.id, quantity__c = 1, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp4;        
        
        Ownership__c own = new Ownership__c(lead__c = l.id, name = 'last', owner_first_name__c = 'first');
        insert own;
        
        String noteTitle = 'TEST' + Datetime.now().format(); // generate a unique string
        Note n = new Note(Title = noteTitle, Body = 'This is a note.', parentid = l.id);
        insert n;
        
        Attachment att = new Attachment(Name = noteTitle, parentid = l.id,
        								Body = Blob.valueOf('This is a file.'));
        insert att;
        
        l.status = 'Offer Accepted';
        l.Product_Group__c = 'Online';
        l.Campaign__c = c1.id;
        
        update l;
        
        l = [SELECT status, recordtypeid FROM Lead WHERE Id = :l.id];
        
        Database.LeadConvert lc = new Database.LeadConvert();
        lc.setLeadId(l.id);
        
        LeadStatus convertStatus = [SELECT Id, MasterLabel
        							FROM LeadStatus
        							WHERE IsConverted = true
        							LIMIT 1];
        lc.setConvertedStatus(convertStatus.MasterLabel);
        
        
        // lead should not be able to convert with inactive products in associated lead products
        Boolean foundError = false;
        try {
        	Database.LeadConvertResult lcr = Database.convertLead(lc);
        } catch (Exception e) {
        	foundError = true;
        }
      
        delete lp4; // removing inactive lead product
        
        Database.LeadConvertResult lcr = Database.convertLead(lc);
        
        test.stopTest();
    }
}

 
Abhay AroraAbhay Arora

You have to understand testing procedure

 


for(Integer i=0;i<9;i++) {
            Rate__c rate = new Rate__c(

this will cause 10 DMLS you need to make a list of rate and insert it after loop
instead of inserting leadproducts seperatly make a list and do a single insert

Lead_Product__c lp = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp;
        
        Lead_Product__c lp2 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Lease', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp2;
        
        Lead_Product__c lp3 = new Lead_Product__c(lead__c = l.id, product__c = p.id, quantity__c = 2, term__c = 'Rent - Chase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp3;
        
        Lead_Product__c lp4 = new Lead_Product__c(lead__c = l.id, product__c = p2.id, quantity__c = 1, term__c = 'Purchase', purchase_price__c = 1, lease_price__c = 2, rental_price__c = 3);
        insert lp4;    

Same for leads

Lead l = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'In-Store', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l;

        Lead l2 = new Lead(Processor__c = 'Chase', Payment_Advisor__c = pa.id, Product_Group__c = 'Online', LeadSource = 'Self', company = 'Test Lead', lastname = 'leadlast', campaign__c = c.id, ownerid = qid, sic_lookup__c = sic1.id, Basis_Points_del__c = 1);
        insert l2;

Same for SIC

        
        SIC__c sic1 = new SIC__c(name = '1', description__c = 'industry1');
        insert sic1;
        
        SIC__c sic2 = new SIC__c(name = '2', description__c = 'industry2');
        insert sic2;

This was selected as the best answer
willjwillj

it worked.  thank you so much.

 

One follow up question from a theoretical perspective:  what if either in a trigger or a test class, the method requires sequential transaction actions(meaning static queries would not work because data would be out of date by the time it is used) to be done to say 200 objects.  It does not matter how efficient you write the queries, the best case on the number of queries required would be 200, right?  Wouldn't that hit the 100 query limit?  How do we overcome this?

 

Response is highly appreciated.
Abhay AroraAbhay Arora

Well in such a case we go for batch apex so that we can handel soql limits