+ Start a Discussion
Max_gMax_g 

Trigger on Custom object

I am attempting to populate my lookup field from Accounts using a query to get the correct id.  I need to update several thousand records, but my existing trigger will not do more than 100.  Here is the code.  What changes do I need to make for this to work?

trigger CustNameStatus on Sales_History__c (before insert, before update){          

 List<Sales_History__c> HWA = [select id, Customer__c, JDE_Cust__c, Cust_Name__c

                                                     from Sales_History__c where Id IN :Trigger.newMap.keySet()];        

 

for(Sales_History__c h: HWA){     

string JDE = h.JDE_Cust__c;     

 system.debug('JDE CUST = ' + h.JDE_Cust__c);        

 Account a = [Select Id,Name,JDE_Customer__c from Account where JDE_Customer__c =: JDE limit 1 ] ;

                 h.Customer__c = a.id;      

                 h.JDE_Cust__c = a.JDE_Customer__c;       

                 h.Cust_Name__c = a.Name;

                     system.debug('Account = ' + a);       

                      system.debug('Sales History = ' + h); 

                    }

            }

Best Answer chosen by Admin (Salesforce Developers) 
Damien_Damien_

Your trigger should correctly process records.  My guess is that in your batch, you are running a query for each record brought back.  Do you read your logs to see what line causes your issues?

All Answers

Damien_Damien_

You need to get all of the associated account data in a single query and then use a Map to access the correct one.  This link should give you what you need.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bulk_idioms.htm

Max_gMax_g

Modified as per the example to the following code.  Now getting and Invalid ID on my first record of the trigger. 

 

trigger GetAcctIdTrigger on Sales_History__c (before insert, before update) {

 

                  Set<Id> actIds = new Set<Id>();    

for (Sales_History__c sh : Trigger.new)        

actIds.add(sh.Jde_Cust__c);         

system.debug('ACTID = ' + actIds);            

 Map<Id, Account> entries = new Map<Id, Account>(        

 [select Id from Account where JDE_Customer__c  in :actIds limit 1]);                   

system.debug('Account ID = ' + entries);                   

for (Sales_History__c sh : Trigger.new)        

sh.Customer__c = entries.get(sh.JDE_Cust__c).Id;  

 }

JWSJWS

I think you probably need to check to make sure trigger.new isn't equal to null.  If you don't have anything in the trigger, there's nothing bailing out the code.  Are you sure you have something in the trigger?

Damien_Damien_

I see 3 issues here:

 

trigger GetAcctIdTrigger on Sales_History__c (before insert, before update)
{
  Set<Id> actIds = new Set<Id>();    
  for (Sales_History__c sh : Trigger.new)
  {
    //You need to make sure this value isn't null if you're adding it to a set of Ids     
    actIds.add(sh.Jde_Cust__c);
  }
  system.debug('ACTID = ' + actIds);
  //Why are you limiting your query to 1?
 Map<Id, Account> entries = new Map<Id, Account>(        
 [select Id from Account where JDE_Customer__c  in :actIds limit 1]);                   
  system.debug('Account ID = ' + entries);                   
  for (Sales_History__c sh : Trigger.new)
  {
    //You need to make sure the value returned from the map isn't null before you try to access the id
    sh.Customer__c = entries.get(sh.JDE_Cust__c).Id;
  }
}

 

Max_gMax_g

I have removed the limit on the process and reran the upsert.  Checking my debug log shows the following error. 

 

This is the sh.JDE_Cust__c field for the first id in this set of 200 id's, so I know that values are there.

25.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO 14:24:20.328 (328178000)|EXECUTION_STARTED 14:24:20.328 (328213000)|CODE_UNIT_STARTED|[EXTERNAL]|TRIGGERS 14:24:20.328 (328291000)|CODE_UNIT_STARTED|[EXTERNAL]|01qZ00000008VUs|GetAcctIdTrigger on Sales_History trigger event BeforeUpdate for [a06Z0000001uuuL, a06Z0000001uuuM, a06Z0000001uuuN, a06Z0000001uuuO, a06Z0000001uuuP, a06Z0000001uuuQ, a06Z0000001uuuR, a06Z0000001uuuS, a06Z0000001uuuT, a06Z0000001uuuU, a06Z0000001uuuV, a06Z0000001uuuW, a06Z0000001uuuX, a06Z0000001uuuY, a06Z0000001uuuZ, a06Z0000001uuua, a06Z0000001uuub, a06Z0000001uuuc, a06Z0000001uuud, a06Z0000001uuue, a06Z0000001uuuf, a06Z0000001uuug, a06Z0000001uuuh, a06Z0000001uuui, a06Z0000001uuuj, a06Z0000001uuuk, a06Z0000001uuul, a06Z0000001uuum, a06Z0000001uuun, a06Z0000001uuuo, a06Z0000001uuup, a06Z0000001uuuq, a06Z0000001uuur, a06Z0000001uuus, a06Z0000001uuut, a06Z0000001uuuu, a06Z0000001uuuv, a06Z0000001uuuw, a06Z0000001uuux, a06Z0000001uuuy, a06Z0000001uuuz, a06Z0000001uuv0, a06Z0000001uuv1, a06Z0000001uuv2, a06Z0000001uuv3, a06Z0000001uuv4, a06Z0000001uuv5, a06Z0000001uuv6, a06Z0000001uuv7, a06Z0000001uuv8, a06Z0000001uuv9, a06Z0000001uuvA, a06Z0000001uuvB, a06Z0000001uuvC, a06Z0000001uuvD, a06Z0000001uuvE, a06Z0000001uuvF, a06Z0000001uuvG, a06Z0000001uuvH, a06Z0000001uuvI, a06Z0000001uuvJ, a06Z0000001uuvK, a06Z0000001uuvL, a06Z0000001uuvM, a06Z0000001uuvN, a06Z0000001uuvO, a06Z0000001uuvP, a06Z0000001uuvQ, a06Z0000001uuvR, a06Z0000001uuvS, a06Z0000001uuvT, a06Z0000001uuvU, a06Z0000001uuvV, a06Z0000001uuvW, a06Z0000001uuvX, a06Z0000001uuvY, a06Z0000001uuvZ, a06Z0000001uuva, a06Z0000001uuvb, a06Z0000001uuvc, a06Z0000001uuvd, a06Z0000001uuve, a06Z0000001uuvf, a06Z0000001uuvg, a06Z0000001uuvh, a06Z0000001uuvi, a06Z0000001uuvj, a06Z0000001uuvk, a06Z0000001uuvl, a06Z0000001uuvm, a06Z0000001uuvn, a06Z0000001uuvo, a06Z0000001uuvp, a06Z0000001uuvq, a06Z0000001uuvr, a06Z0000001uuvs, a06Z0000001uuvt, a06Z0000001uuvu, a06Z0000001uuvv, a06Z0000001uuvw, a06Z0000001uuvx, a06Z0000001uuvy, a06Z0000001uuvz, a06Z0000001uuw0, a06Z0000001uuw1, a06Z0000001uuw2, a06Z0000001uuw3, a06Z0000001uuw4, a06Z0000001uuw5, a06Z0000001uuw6, a06Z0000001uuw7, a06Z0000001uuw8, a06Z0000001uuw9, a06Z0000001uuwA, a06Z0000001uuwB, a06Z0000001uuwC, a06Z0000001uuwD, a06Z0000001uuwE, a06Z0000001uuwF, a06Z0000001uuwG, a06Z0000001uuwH, a06Z0000001uuwI, a06Z0000001uuwJ, a06Z0000001uuwK, a06Z0000001uuwL, a06Z0000001uuwM, a06Z0000001uuwN, a06Z0000001uuwO, a06Z0000001uuwP, a06Z0000001uuwQ, a06Z0000001uuwR, a06Z0000001uuwS, a06Z0000001uuwT, a06Z0000001uuwU, a06Z0000001uuwV, a06Z0000001uuwW, a06Z0000001uuwX, a06Z0000001uuwY, a06Z0000001uuwZ, a06Z0000001uuwa, a06Z0000001uuwb, a06Z0000001uuwc, a06Z0000001uuwd, a06Z0000001uuwe, a06Z0000001uuwf, a06Z0000001uuwg, a06Z0000001uuwh, a06Z0000001uuwi, a06Z0000001uuwj, a06Z0000001uuwk, a06Z0000001uuwl, a06Z0000001uuwm, a06Z0000001uuwn, a06Z0000001uuwo, a06Z0000001uuwp, a06Z0000001uuwq, a06Z0000001uuwr, a06Z0000001uuws, a06Z0000001uuwt, a06Z0000001uuwu, a06Z0000001uuwv, a06Z0000001uuww, a06Z0000001uuwx, a06Z0000001uuwy, a06Z0000001uuwz, a06Z0000001uux0, a06Z0000001uux1, a06Z0000001uux2, a06Z0000001uux3, a06Z0000001uux4, a06Z0000001uux5, a06Z0000001uux6, a06Z0000001uux7, a06Z0000001uux8, a06Z0000001uux9, a06Z0000001uuxA, a06Z0000001uuxB, a06Z0000001uuxC, a06Z0000001uuxD, a06Z0000001uuxE, a06Z0000001uuxF, a06Z0000001uuxG, a06Z0000001uuxH, a06Z0000001uuxI, a06Z0000001uuxJ, a06Z0000001uuxK, a06Z0000001uuxL, a06Z0000001uuxM, a06Z0000001uuxN, a06Z0000001uuxO, a06Z0000001uuxP, a06Z0000001uuxQ, a06Z0000001uuxR, a06Z0000001uuxS, a06Z0000001uuxT, a06Z0000001uuxU, a06Z0000001uuxV, a06Z0000001uuxW, a06Z0000001uuxX, a06Z0000001uuxY] 14:24:20.332 (332402000)|SYSTEM_METHOD_ENTRY|[6]|LIST<Sales_History__c>.iterator() 14:24:20.332 (332730000)|SYSTEM_METHOD_EXIT|[6]|LIST<Sales_History__c>.iterator() 14:24:20.332 (332764000)|SYSTEM_METHOD_ENTRY|[6]|system.ListIterator.hasNext() 14:24:20.332 (332798000)|SYSTEM_METHOD_EXIT|[6]|system.ListIterator.hasNext() 14:24:20.332 (332907000)|SYSTEM_METHOD_ENTRY|[7]|SET.add(ANY) 14:24:20.332 (332971000)|EXCEPTION_THROWN|[7]|System.StringException: Invalid id: 91838 14:24:20.333 (333095000)|SYSTEM_METHOD_EXIT|[7]|SET.add(ANY) 14:24:20.333 (333193000)|FATAL_ERROR|System.StringException: Invalid id: 91838

Damien_Damien_

That error isn't because of removing LIMIT 1.  It states 'invalid id'.  What line is it erroring on?

Max_gMax_g

Here is my understanding of what the trigger is doing.

Reading all the update records and then adding the JDE_Cust__c field to the set actids.  This is the field needed to query the account object to get the associated id necessary to populate my account lookup field on the original object.

Map account using the select statment to get the id for each of the accounts and place in the map named entries.

 

for loop gets all the update records and sets the value of Customer__c from the entries map. 

 

The invalid id listed in the debug log is the value of JDE_Cust__c from the first update record in the list.  The error is occurring prior to the first system.debug statement.

 

Damien_Damien_

So, you're saying it errors on this line:

 

actIds.add(sh.Jde_Cust__c);

If this is the case, then either 1 of 2 things is true:

1) Jde_Cust__c is null for a value

2) Your Jde_Cust__c is not a lookup, and is instead a string field which allows non-Ids to be added

     If a non-Id is added as an Id into your Set<Id>, this will cause an error.

Max_gMax_g

I made that chenge to be sure I am only adding the Id.  Now I am erroring out at 100 records (Too many SOQL queries: 101).  Dataloader puts 200 records per block, so I am not processing correctly.  This is back to my original question of how do I change my trigger to process all the records?

 

Here is the latest code.

trigger GetAcctIdTrigger on Sales_History__c (before insert, before update) {    

Set<Id> actIds = new Set<Id>();    

for (Sales_History__c sh : Trigger.new)        

actIds.add(sh.Id);         

system.debug('ACTID = ' + actIds);    

Map<Id, Account> entries = new Map<Id, Account>(        

 [select Id, Name from Account where JDE_Customer__c  in :actIds]);                   

 system.debug('Account ID = ' + entries);                   

for (Sales_History__c sh : Trigger.new)        

sh.Customer__c = entries.get(sh.JDE_Cust__c).Id;  

 }

Damien_Damien_

Your trigger should correctly process records.  My guess is that in your batch, you are running a query for each record brought back.  Do you read your logs to see what line causes your issues?

This was selected as the best answer