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
kieran.osullivankieran.osullivan 

SOQL Limits when Importing using DataLoader

I have a trigger which creates a new opportunity when an asset has a status of Paid and has an expiry date.

This code works fine when records are input using the web interface but when I tried to import 5,000 records using the DataLoader only 23 records imported and the rest were rejected because of SOQL limits on this trigger.

 

I need to know is my code very innefficient?

I thaught I had taken care to ensure that there was as few inserts and updates as possible.

 

Strangely when I re-imported the records they imported without any problems except MY TRIGGER SEEMS TO HAVE BEEN IGNORED SALESFORCE DID NOT RUN IT.  IS THAT POSSIBLE?

 

 

trigger CreateOpportunities on Asset (after update, after insert) {
    Boolean NewOpp = false;
    List <Opportunity> OppToUpdate = new List <Opportunity> {};
    List <Opportunity> OppToInsert = new List <Opportunity> {};
    for(Asset aset: Trigger.new){
        if (aset.Status == 'Paid'){
            Opportunity opp = null;
            try {
                opp =  [select Name,ACCOUNTID,CurrencyIsoCode,CLOSEDATE,Asset__c from Opportunity where asset__c =: aset.ID limit 1];
            } catch (Exception e){}
            if (opp == null) {
                opp = new Opportunity();
                NewOpp = true;
            }
            opp.ACCOUNTID = aset.AccountID;
            opp.Name = 'Automated Opportunity for ' + aset.Name;
            opp.CurrencyIsoCode = aset.CurrencyIsoCode;
            opp.CLOSEDATE = aset.Maintenance_Expires__c;
            opp.Asset__c = aset.ID;
            if (NewOpp){
                opp.StageName = 'Work in Progress';
                OppToInsert.add(opp);
                // insert opp;
            } else {OppToUpdate.add(opp);}
            NewOpp = false;
        }
    }

    if (OppToUpdate.size() > 0) {update OppToUpdate;}
    if (OppToInsert.size() > 0) {insert OppToInsert;}
}

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

You need to use an aggregate-query-update (AQU) pattern. Fortunately, the pattern is shortcut a little because of where this trigger is executing. Here's how I would write this trigger:

 

trigger CreateOpportunities on Asset (after insert,after update) {
	Map<Id,Opportunity> opps = new Map<Id,Opportunity>();
	for(Opportunity record:[SELECT Id,StageName,Asset__c FROM Opportunity WHERE Asset__c IN :Trigger.new]) {
		opps.put(record.Asset__c,record);
	}
	for(Asset record:Trigger.new) {
		if(record.Status=='Paid') {
			opps.put(record.id,
				new opportunity(
					id=opps.containskey(record.id)?opps.get(record.id).id:null,
					accountid=record.accountid,
					name='Automated Opportunity for '+record.Name,
					CurrencyIsoCode=record.currencyisocode,
					closedate=record.maintenance_expires__c,
					asset__c=record.id,
					StageName=opps.containskey(record.id)?opps.get(record.id).stagename:'Work in Progress'));
		}
	}
	upsert opps.values();
}

Let me know if you need any assistance understanding it.

All Answers

Vinit_KumarVinit_Kumar

Kieran,

 

I see that you are running a SOQL inside for loop which is not recommended at all.You are bound to get Too many SOQL queries exception if the no. of records is more than 100.

 

I would suggest you need to bulkify your code by removing the SOQL from for loop.

 

 
sfdcfoxsfdcfox

You need to use an aggregate-query-update (AQU) pattern. Fortunately, the pattern is shortcut a little because of where this trigger is executing. Here's how I would write this trigger:

 

trigger CreateOpportunities on Asset (after insert,after update) {
	Map<Id,Opportunity> opps = new Map<Id,Opportunity>();
	for(Opportunity record:[SELECT Id,StageName,Asset__c FROM Opportunity WHERE Asset__c IN :Trigger.new]) {
		opps.put(record.Asset__c,record);
	}
	for(Asset record:Trigger.new) {
		if(record.Status=='Paid') {
			opps.put(record.id,
				new opportunity(
					id=opps.containskey(record.id)?opps.get(record.id).id:null,
					accountid=record.accountid,
					name='Automated Opportunity for '+record.Name,
					CurrencyIsoCode=record.currencyisocode,
					closedate=record.maintenance_expires__c,
					asset__c=record.id,
					StageName=opps.containskey(record.id)?opps.get(record.id).stagename:'Work in Progress'));
		}
	}
	upsert opps.values();
}

Let me know if you need any assistance understanding it.

This was selected as the best answer
kieran.osullivankieran.osullivan

No the insert and update are not in the loop.  There is only one } after them.

thanks anyway
Vinit_KumarVinit_Kumar

Kieran,

 

I am not talking about the DML statements ,I am talking about SOQL query below one which is running  inside for loop.

 

opp =  [select Name,ACCOUNTID,CurrencyIsoCode,CLOSEDATE,Asset__c from Opportunity where asset__c =: aset.ID limit 1];