0

I've got to update a custom field on Account.

This field is the number of Opportunities of that Account that are "Prospecting".

I've written a trigger for this.

The problem is, I've solved it using a DML statement inside a loop (yes, i know, this is very bad).

I donot know any other way to do this, without using DML in loops.

for(account acc_in_loop = trigger.new)
{
    list<opportunity> opp_list = [Select Id, Stagename from Opportunity where AccountID =: acc_in_loop.id];

    for(opportunity opp_in_loop : opp_list)
    {
        if condition etc....
    } 

    acc_updt_list.add(acc_in_loop);
}
insert acc_updt_list;

Any tips/help would be appreciated.

2
  • Have you looked at using a roll up summary field? Also may help: salesforce.stackexchange.com/questions/33650/…
    – Girbot
    Aug 8, 2016 at 10:10
  • Hello Girbot, thats an option, but I wish to try with triggers. The above question is to help me understand relationships better, and how to get child records from parent ID.
    – fraglord
    Aug 8, 2016 at 10:19

3 Answers 3

2

If you only want to do a simple count, you should use a Roll-Up Summary Field

If that doesn't work for you and you need more complex logic, you can reduce the number of queries with a subquery

for(Account a : [SELECT Id, (SELECT Id, StageName from Opportunities) FROM Account WHERE Id IN :Trigger.new]){
    Integer count = 0;
    for(Opportunity opp : a.Opportunities){
        if(opp.StageName = 'Prospecting') count = count+1;
    }
    a.MyCountField__c = count;
}

This way you only query once.

4
  • Hello, I'm not sure whats going on in the first line, will it not call DML every time in the loop?
    – fraglord
    Aug 8, 2016 at 10:21
  • 2
    No. It'll only query once and then loop through the results. P.s.: DML are changes to the database (update, insert, delete, etc...). The 'selects' are called queries Aug 8, 2016 at 10:40
  • Oh, didnt know THAT! I just lump query + changes as DML. Well ty for showing me the way!
    – fraglord
    Aug 8, 2016 at 10:42
  • perfect, it worked!
    – fraglord
    Aug 8, 2016 at 11:16
0

There are many errors in your code ou can do something like this

for(account acc_in_loop : [Select id ,no_Of_opp__c, ( select Id from Opportunities where stagename ='Prospecting') from account where ID IN: trigger.new)
{
    acc_in_loop.no_Of_opp__c = acc_in_loop.Opportunities.size();
}

And put this code in before update of account and you don't need any DML.

4
  • but will it not call the DML every single time in the loop? for eg, I have updated 200 accounts via Data Loader, will it not exceed the DML limit?
    – fraglord
    Aug 8, 2016 at 10:20
  • @fraglord did you see any dml in my code. No it will not call the DML. Use this in before update Aug 8, 2016 at 10:27
  • are you SURE that [Select id ,no_Of_opp__c, ( select Id from Opportunities where stagename ='Prospecting') from account where ID IN: trigger.new] will not count for governor limit?
    – fraglord
    Aug 8, 2016 at 10:30
  • This is a SOQL and will count in governor limit. You are asking about DML. Aug 8, 2016 at 12:52
0

You can create a roll up summary field on Account object which will calculate the opportunities with Prospecting status for Account.

A roll-up summary field calculates values from related records, such as those in a related list. You can create a roll-up summary field to display a value in a master record based on the values of fields in a detail record. The detail record must be related to the master through a master-detail relationship.

By using this way, you don't requied to write a code.

enter image description here

1
  • Thank you, I know this method, but I'm trying to do this with code.
    – fraglord
    Aug 8, 2016 at 11:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .