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
BtormarBtormar 

Apex Trigger to update all related child records

I'm new to Triggers and are trying to put in a trigger that updates all related child records on Opportunity. I have a custom object with a Master-Detail to the Opportunity and I'm trying to accomplish the following in a trigger:
 
Everytime a new record on the custom object is created I'd like it to uncheck a checkbox on all records for that Opportunity ID (and subsequently check that box for the newly created record but since the first part doesn't work I haven't tried to code this yet).
 
I also haven't put in the loop yet as since it won't even update a single record so far.
 
Here's my (bad) code:
 
trigger flag_fsc on Funnel_Scorecard__c (after update, after insert)
{
CustomObject__c[] fsc = trigger.new;
CustomObject__c[] fsc_update;
 
fsc_update[0] = [SELECT id, Most_Recent__c FROM Custom Object__c WHERE id = :fsc[0].opportunity__c] AND fsc[0].Most_Recent__c = TRUE;
 
fsc_update[0].Most_Recent__c = False;
  
update fsc_update[0];

}
 
And here's the error message:
 
Apex trigger flag_fsc caused an unexpected exception, contact your administrator: flag_fsc: execution of AfterUpdate caused by: System.ListException: List index out of bounds: 0: Trigger.flag_fsc: line 9, column 14
 
Any ideas anyone?
 
Thanks!
EJWEJW
The first issue is this line:

fsc_update[0] = [SELECT id, Most_Recent__c FROM Custom Object__c WHERE id = :fsc[0].opportunity__c] AND fsc[0].Most_Recent__c = TRUE;

There are a couple of typos there that I'm assuming are just in your psuedo code for your post, so I'll skip those.  Primarily you're trying to assign the result of the query to fsc_update[0] (the first item in the array) and the array wasn't initialized before-hand.  Doing it this way also assumes that you'll only ever get one response from your query.  If, however, you remove the [0] from fsc_update[0] = , that should work fine as the query will return a full array (even if it's only one item), which will initialize your variable.

The second issue I see is that you're not checking to see if your query actually returned data of if there were no records found, you're just assuming one item is returned.  Here's how I'd re-write your trigger:

Code:
trigger flag_fsc on Funnel_Scorecard__c (after update, after insert)
{
    CustomObject__c[] fsc_query = new CustomObject__c[0];
    CustomObject__c[] fsc_update;
    Id[] OppIds = new Id[0];

    // Loop through our updated/inserted rows and check for items that
    // have most recent set to true add them to the list of objects
    // that need to be updated.
    for ( CustomObject__c fsc : trigger.new )
    {
        if ( fsc.Most_Recent__c == true )
        {
           fsc_query.add( fsc );
           // Track the opportunity IDs of the parent opps
           // that need to have their children updated.
           OppIds.add( fsc.Opportunity__c );
        }
    }

    // If we found some that were marked as most recent, query
    // the other FSCs for the parent opportunity and set their
    // most recent flag to false if their FSC flag is true and
    // they're not this FSC. 
    if ( fsc_query.size() > 0 )
    {
        // We don't need to query the Most Recent field as we're just
        // going to overwrite it.
        fsc_update = [SELECT Id FROM Custom_Object__c WHERE Opportunity__c IN :OppIds AND Id NOT IN :fsc_list AND Most_Recent__c = TRUE];
    }

    // Loop through our results and change Most Recent to false.
    for ( Custom_Object__c fsc : fsc_update )
        fsc.Most_Recent__c = false;
 
    // If we have anything to update, do so.
    if ( fsc_update.size() > 0 )
        update fsc_update;
}

 I made some guesses about what you're trying to do there.



BtormarBtormar

This is great - thanks EJW!

However... I still get the following error message when it triggers - any suggestions?

Apex trigger flag_fsc caused an unexpected exception, contact your administrator: flag_fsc: execution of AfterInsert caused by: System.DmlException: Update failed. First exception on row 0 with id a03S0000000EqVJIA0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, flag_fsc: execution of AfterUpdate caused by: System.NullPointerException: Attempt to de-reference a null object Trigger.flag_fsc: line 33, column 38: Trigger.flag_fsc: line 39, column 9

 
EJWEJW
Give this a shot, moved the update statement and added another check for data.

Code:
trigger flag_fsc on Funnel_Scorecard__c (after update, after insert)
{
    CustomObject__c[] fsc_query = new CustomObject__c[0];
    CustomObject__c[] fsc_update;
    Id[] OppIds = new Id[0];

    // Loop through our updated/inserted rows and check for items that
    // have most recent set to true add them to the list of objects
    // that need to be updated.
    for ( CustomObject__c fsc : trigger.new )
    {
        if ( fsc.Most_Recent__c == true )
        {
           fsc_query.add( fsc );
           // Track the opportunity IDs of the parent opps
           // that need to have their children updated.
           OppIds.add( fsc.Opportunity__c );
        }
    }

    // If we found some that were marked as most recent, query
    // the other FSCs for the parent opportunity and set their
    // most recent flag to false if their FSC flag is true and
    // they're not this FSC. 
    if ( fsc_query.size() > 0 )
    {
        // We don't need to query the Most Recent field as we're just
        // going to overwrite it.
        fsc_update = [SELECT Id FROM Custom_Object__c WHERE Opportunity__c IN :OppIds AND Id NOT IN :fsc_list AND Most_Recent__c = TRUE];

        // Only attempt to update if we actually retrieved results in the previous
        // query.
        if ( fsc_update != null & fsc_update.size() > 0 )
        {
            // Loop through our results and change Most Recent to false.
            for ( Custom_Object__c fsc : fsc_update )
                fsc.Most_Recent__c = false;
 
            update fsc_update;
        }
    }
}

 



Message Edited by EJW on 01-29-2008 12:28 PM
BtormarBtormar

Works great - Thanks so much EJW!



Message Edited by Btormar on 02-08-2008 12:14 PM
renurenu

Hello EJW. I have seen most of your  replies. hope you can suggest me. I know this is very huge code. But please

help me with some solution.thanks a lot

 

I'm trying to write test cases for my APEX code.I searched  the Apex documentation and  the Apex discussion board but did not find solution. I am getting two errors. I tried  but unfortunately not getting the required output. Let me know where i am getting error.  I had written the same code in the trigger without using Apex class it was working perfect. But since i need to deploy the trigger i started writing Apex class which gives me these errors.
Thanks in advance for the solution.
 
 
This is what i am trying to accomplish.
 
When ever a new custom object(Client)  is created or updated i need to update few fields accordingly.
 
 
And the errors are at line 35 and 54
 
Errrors
1)  System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientUpdates: execution of AfterInsert
caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Even if a field is indexed a filter might still not be selective when:
1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)
Class.ClientApex.afterinsertupdate: line 69, column 1
Trigger.ClientUpdates: line 19, column 1
 
2)  System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ClientUpdates: execution of AfterInsert
caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.
Even if a field is indexed a filter might still not be selective when:
1. The filter value includes null (for instance binding with a list that contains null)
2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times)
Class.ClientApex.afterinsertupdate: line 69, column 1
Trigger.ClientUpdates: line 19, column 1
 
 
Trigger
trigger ClientUpdates on Client__c (before insert,before update,after insert,after update) {
if(trigger.isBefore)
{
if(trigger.isinsert){
Client__c[] m=Trigger.new;
ClientApex.beforeinsertupdate(m);
 }
if(trigger.isupdate){
Client__c[] m=Trigger.old;
ClientApex.beforeinsertupdate(m);
 }
}
if(trigger.isafter)
{
if(trigger.isinsert)
{
Client__c[] m=Trigger.new;
ClientApex.afterinsertupdate(m);
}
if(trigger.isupdate)
{
Client__c[] m=Trigger.new;
ClientApex.afterinsertupdate(m);
}
}
}

 
Apex Class
public class ClientApex {
public static void beforeinsertupdate(Client__c [] m){
List<Account> acctList = new List<Account>();
 for( Client__c o: m){
if(o.Wholesale_Relationship__c==true)
{
Account a=new Account(Id = o.client_prospect_account__c);
    o.Client_Authorization__c=true;
         a.Client_type__c = 'Wholesale';
  
    acctList.add ( a );
   
  
    }
    else
        {
     Account a=new Account(Id = o.client_prospect_account__c);
      a.Client_type__c = '';
    
      acctList.add ( a );
     
        }update acctList;
                   }
}

static testMethod void beforeinsertupdatetest1() {
Account a =new Account(Name='UnitTest1');
insert a;
Client__c b=new Client__c(client_prospect_account__c=a.id,Wholesale_Relationship__c=true);
insert b;  //Line 35
ID key = b.id;
Client__c val=[select id,Client_Authorization__c,Wholesale_Relationship__c from Client__c where id= :key];
System.assert(val.Wholesale_Relationship__c==true);
System.assert(val.Client_Authorization__c==true);
ID key1 =a.id;
Account val1=[select id,ADP_Client_type__c from Account where id=:key1];
System.assert(val1.Client_type__c=='wholesale');
}

static testMethod void beforeinsertupdatetest2() {
Account a =new Account(Name='UnitTest2');
insert a;
Client__c b=new Client__c(client_prospect_account__c=a.id,Wholesale_Relationship__c=false);
insert b; //Line 54
ID key = b.id;
Client__c val=[select id,Wholesale_Relationship__c from _Client__c where id= :key];
System.assert(val.Wholesale_Relationship__c==false);
ID key1 =a.id;
Account val1=[select id,ADP_Client_type__c from Account where id=:key1];
//System.assert(val1.ADP_Client_type__c=='');
}
public static void afterinsertupdate(Client__C[] m){
List<Account> acctList = new List<Account>();
 for( Client__c o: m)
 {
Client__c[] az=[select Id from Client__c where Accounting_Provider__c=.Accounting_Provider__c and Wholesale_Relationship__c =true];
Account ac =[select Id from Account where Id=.Accounting_Provider__c];
if(az.size()>0)
{   
         ac.Status__c='Wholesale';
  
      }
      else
      {
      ac.Status__c='';
      }   
      acctList.add(ac);
    }
    update acctList;
}

static testMethod void afterinsertupdatetest1() {
Account c=new Account(Name='UnitTest4',Status__c='Wholesale');
insert c;
ID key2=c.id;
Account a =new Account(Name='UnitTest3');
insert a;
ID key1=a.id;
Client__c b=new Client__c(client_prospect_account__c=a.id,Accounting_Provider__c=c.id,Wholesale_Relationship__c=true);
insert b;
ID key3 = b.id;
Client__c val=[select id,Client_Authorization__C from Client__c where Accounting_Provider__c=:b.Accounting_Provider__c and Wholesale_Relationship__c=true];
Account val1=[select id,Status__c from Account where id=:key2];
System.assert(val1.Status__c=='Wholesale');
}
static testMethod void afterinsertupdatetest2() {
Account c=new Account(Name='UnitTest5',Status__c='');
insert c;
ID key2=c.id;
Account a =new Account(Name='UnitTest3');
insert a;
ID key1=a.id;
Client__c b=new Client__c(client_prospect_account__c=a.id,Accounting_Provider__c=c.id,Wholesale_Relationship__c=true);
insert b;
ID key3 = b.id;
Client__c val=[select id,Client_Authorization__C from Client__c where Accounting_Provider__c=:b.Accounting_Provider__c and Wholesale_Relationship__c=true];
Account val1=[select id,Status__c from Account where id=:key2];
//System.assert(val1.Status__c=='');
}
}

 

 
OnCloud9OnCloud9

Nice work here!

 

I have a smiliar situation with a minor caveat...

 

I have two custom objects (parent & child).  on the child, i capture:

 

1) start date

2) end date

3) charges ($$)

 

i want to get a rolling 12mo average, starting with the most recent child object.  i may have a child from june 2010 >> june 2011, or (if today is june)  feb 2010 >> feb 2011.  

 

I'd want to "flag" feb 2011, roll back for 12 months and tally up the charges.

 

Do you know how I can alter the code to approach this?  

 

Many thanks in advance.

 

 
OnCloud9OnCloud9

^^ In addition, I should mention that the 12mo average should be captured on the parent object.

 

Thanks again

 
PashmanPashman
Ok, time to give back to the Community here.   So I'm learning the platform, and wanted to mock up a solution that we put in place at my previously employeer.   The basic business case is that we worked in a compete group, and the sales force could escalate to our team when they ran into head-to-head compete situations.   When they would log a case, we'd create an Escalation.  So I have a custom object called Escalation__c.    When enough escalations would come in and we'd start seeing trends, we decided we needed to create a mechanism to track those high level trends.  They were called Key Sales Blockers and Observations, or KSBOs.  So I have another custom Object called KSBO__c.   Because an Escalation can be associated with many KSBOs, and a KSBO can be associated with many Escalations, my solution needed to have a junction object with Master-Detail relationships to both the KSBO and Escalation object.  That junction object is called KSBOEscalationAssociation__c.   It looks like so:
User-added image
As I was building out the solution, I wanted to see on a KSBO record the total amount of revenue at risk represented by the rollup of the associated Escalations.   This turned out to be pretty tough to figure out.   Why?  Well for a number of reasons.   The right way to show a running total of something in a parent record is to use a Rollup field.   Easy enough, except the 
PashmanPashman
<Was hoping I could save my work and keep editing, but guess not, need to keep going here> ... KSBO parent is not directly associated with the Escalation object (has to go through the Junction object), so values on the Escalation object are not directly available to the KSBO object for a Rollup.   Ok, so needed to find a way to pull the Revenue At Risk value from the Escalation object into the KSBOEscalationAssociation object.  Fine, use a formula to do that, no problem works great.   But then oops, you can't do a Rollup in the parent based on a formula field in the child.   Shoot.   Ok, so you need to push the value of the formula field into another "regular" currency field on the junction object.   Fine, so how do you get that to trigger automatically?  Not so easy.   Workflow rules on parent records can't reach into child records and do stuff (believe me I tried).   Workflow rules *can* reach up into parent records from children, but don't fire when the parent changes, only when the child record is updated.  In this case updating the revenue at risk on the Escalation didn't actually change the child record on the junction Object.  For that same reason you couldn't just do a local workflow that sets the one field equal to the other, because it wasn't updated when the parent was updated.  So then I looked at Process Builder.  Nice tool, and improving I hear over time.   I originally was thrown off about the fact that the "Type" field on the Actions pane of the Process Builder showed Currency, so I figured it was just telling me record type I was going to be populating.  In short I thought that you could only populate a static value on a field, which is not what I needed it to do.  Turns out that the dropdown is misleading, and when you click on it you can do exactly what I needed it to do, which is to set a value of one field on a child record equal to a formula based field on that same record when the parent updates. So that's cool, and is how I'll do it next time, but because I didn't find that setting initially, I thought I was stuck doing an APEX trigger.   I must have read 20 examples on how to write an APEX trigger to this, and not being a developer and with the relatively unhelpful IDE that is embedded in the APEX Trigger UI, I really struggled.   However, I did ultimately prevail, and here is a snippet of the code I wrote that works.   Hope others that are this same journey find it helpful.  
User-added image
Bottom line is that Process Builder is your friend, and Clicks Not Code is a mantra to live by.   But then again, knowing how to write a little bit of APEX code (and perhaps downloading a halfway decent editor like Eclipse to do this work in) will serve you well in the long run and is a useful skill to possess.