Sign Up ›

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

Trigger Update Lookup Field

I'm trying a different approach for programming a trigger I need to update a lookup field called Zipcode_Lookup__c on our Account object from a previous post of mine. I used an example from pacstrats. Here is my code:

 

trigger ZipcodeLookup on Account (before insert, before update) {
List<String> BillingPostalCodes = new List<String>();
for (Account a:Trigger.new)
{
BillingPostalCodes.add(a.BillingPostalCode);
}
List <Zip_Code__c> ZipCodeList = [Select Name from Zip_Code__c where Name in :BillingPostalCodes];
for (Integer i = 0; i < Trigger.new.size(); i++)
{
if (Trigger.new[i].BillingPostalCode != null)
{
Trigger.new[i].Zipcode_Lookup__c = ZipcodeList[i].Name;
}
else
{
Trigger.new[i].Zipcode_Lookup__c = null;
}
}
}

 

The jest of it is to populate the Zipcode_Lookup__c custom lookup field on Account with the BillingPostalCode field on Account if it is available. The lookup is to the Name field on a custom object called Zip_Code__c.

 

When I go to update a record (or add one) I recieve this error: ZipCodeUpdate: execution of BeforeUpdate caused by: System.StringException: Invalid id: 32403: Trigger.ZipCodeUpdate: line 12, column 1

 

I've received this Invalid id exception error with a simple version of this trigger also.

 

I would appreciate any advise as I need this lookup field populated to get around the limitations of Account Assignment rules in the Manage Territories Hierarchy.

 

Thank you!

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek

This is an interesting problem.  My guess in to what is happening is this:

 

Some of your BillingPostalCodes that you are uploading are blank, so your zipcodeList is shorter than Trigger.new and so at some point it is trying to call an index that is out of bounds.

 

We could solve this one of two ways, I believe that we could use a map instead of a list for zipCodeList and solve it that way, but I am more comfortable using some nested for loops.

 

trigger ZipcodeLookup2 on Account (before insert, before update) {
	List<String> BillingPostalCodes = new List<String>(); 
	
	for (Account a:Trigger.new){
		BillingPostalCodes.add(a.BillingPostalCode);
	}

	List <Zip_Code__c> ZipCodeList = [Select ID, Name from Zip_Code__c where Name in :BillingPostalCodes];

	for (Integer i = 0; i <Trigger.new.size(); i++){
		if (ZipCodeList.size() > 0 && Trigger.new[i].BillingPostalCode !=null){
                        for (Zip_Code__c z:ZipCodeList){
				if (Trigger.new[i].BillingPostalCode == z.name){
		        		Trigger.new[i].Zipcode_Lookup__c = z.ID;
                                }
                        }
		}
		else{
		Trigger.new[i].Zipcode_Lookup__c = null;
		}
		
	}
}

 You'll notice that I realized that we can combine the two original if statements, to eliminate one of the elses and make the code more readable.  Also basically what we are doing is, for each account that meets the criteria in the if statement we are comparing the billingPostalCode to our list of zip codes and then when we find a match we set the appropriate field.  I think that I got all this right, but I do not have a test environment for it so if you have a problem let me know.

 

Jake

 

All Answers

Jake GmerekJake Gmerek

Look up fields store the ID of the record that they are looking up, but display the name.  So your code should look like this:

 

}

trigger ZipcodeLookup on Account (before insert, before update) {
List<String> BillingPostalCodes = new List<String>(); 
for (Account a:Trigger.new)
{
BillingPostalCodes.add(a.BillingPostalCode);
}
List <Zip_Code__c> ZipCodeList = [Select ID from Zip_Code__c where Name in :BillingPostalCodes];
for (Integer i = 0; i < Trigger.new.size(); i++)
{
if (Trigger.new[i].BillingPostalCode != null) 
{
Trigger.new[i].Zipcode_Lookup__c = ZipcodeList[i].ID;
}
else
{
Trigger.new[i].Zipcode_Lookup__c = null;
}
}
awoelfelawoelfel

Your code change works perfectly! Thank you so much!
Jake GmerekJake Gmerek

No problem
OnCloud9OnCloud9

Thanks for this! I'm in a simliar situation:

 

I have 2 objects:

 

Lead object

Data Tabe object

 

My goal is to link the two (via lookup on lead to data table) and through formula fields, carry information from data table onto the lead record.  Using the code above, I've made this work.

 

However, I am running into a problem where if the Zip code doesn't exist in the data table, I get an apex error.  Is there a way to alter the code to dismiss a non-existing zip code request?

 

for example:

 

if zip = 12345, then ABC should appear

however, if zip = 00000 (invalid), then nothing should appear (right now, i get an error)

 

I made the following changes to the last "else" line: 

 

else
{
Trigger.new[i].Survey_Zip__c = 'I put the ID of a default record here';
}
}

 where the 'ID' area is a blank (but live) record.  

 

Does that make sense?  Please let me know if you can help with the exception rule!  Thanks-

 

 
Jake GmerekJake Gmerek

Post your modified code and I will edit it to do what you want.
OnCloud9OnCloud9

Thanks Jake.  Here's my code:

 

trigger updateZipCode on Lead (before insert, before update) {



List<String> NEZips = new List<String>(); 
for (Lead a:Trigger.new)

{
NEZips.add(a.Zip_NE__c);
}

List <NE_Survey_Table__c> ZipCodeList = [Select ID from NE_Survey_Table__c where Name in :NEZips];
for (Integer i = 0; i < Trigger.new.size(); i++)

{
if (Trigger.new[i].Zip_NE__c != null) 
{
Trigger.new[i].NE_Survey_Zip__c = ZipcodeList[i].ID;
}

else
{
Trigger.new[i].NE_Survey_Zip__c = null;
}
}

 
Jake GmerekJake Gmerek

So I am thinking that you probably want something like this:

trigger updateZipCode on Lead (before insert, before update) {



List<String> NEZips = new List<String>(); 
for (Lead a:Trigger.new)

{
NEZips.add(a.Zip_NE__c);
}

List <NE_Survey_Table__c> ZipCodeList = [Select Name from NE_Survey_Table__c where Name in :NEZips];

if (ZipCodeList.size() > 0 ){

for (Integer i = 0; i < Trigger.new.size(); i++)

{
if (Trigger.new[i].Zip_NE__c != null) 
{
Trigger.new[i].NE_Survey_Zip__c = ZipcodeList[i].ID;
}

else
{
Trigger.new[i].NE_Survey_Zip__c = null;
}
}
}

 Essentially, if your zipcode list does not return any responses then just skip the whole process.  That should help your issue.
OnCloud9OnCloud9

Awesome!! This works exactly as it should.  Thank you x 100!  

 

You're the man!!
Jake GmerekJake Gmerek

No problems!!
OnCloud9OnCloud9

One question on functionality:

 

If I clear the original "Zip_NE__c"- it doesn't necessairly clear the value in the lookup (therefore displays formula fields showing invalid data).  How can I alter the code so that this doesn't happen?

 

In other words:

 

Currently:

 

if Zip_NE__c = 12345

then lookup__c = 12345

therefore, formula = ABCDE

 

if Zip_NE__c = (null), then the following still remains: 

lookup__c = 12345

therefore, formula = ABCDE

 

 

I'd like the functionality so that if

Zip_NE__c = (null) 

lookup__c = (null)

formula = (null)

 

Does that make sense?
Jake GmerekJake Gmerek

Not 100% sure what you want, but here is what I think that you want:

 

trigger updateZipCode on Lead (before insert, before update) {



List<String> NEZips = new List<String>(); 
for (Lead a:Trigger.new)

{
NEZips.add(a.Zip_NE__c);
}

List <NE_Survey_Table__c> ZipCodeList = [Select Name from NE_Survey_Table__c where Name in :NEZips];

if (ZipCodeList.size() > 0 ){

for (Integer i = 0; i < Trigger.new.size(); i++)

{
if (Trigger.new[i].Zip_NE__c != null) 
{
Trigger.new[i].NE_Survey_Zip__c = ZipcodeList[i].ID;
}

else
{
Trigger.new[i].NE_Survey_Zip__c = null;
}
}
else{
Trigger.new[i].NE_Survey_Zip__c = null;
}
}

 I am going on two assumptions 1) NE_Survey_Zip__c is your lookup field that you are talking about, I am pretty sure that this is the case and 2) NE_Survey_Table__c does not contain an entry NULL.  

 

So essentially if you change the original field to NULL your list will come up as size 0 and the new else clause will be triggered setting your lookup to NULL and the Formula should update automatically.

 

I hope that this is what you want.

 

 
OnCloud9OnCloud9

I see what you're saying and your assumptions are correct.  I'm getting some type of bracket error, must be syntax somewhere.  Going to try to fix.

 

Thanks for your help again, triggers are pretty sweet!!  Can't wait to learn them and be on your level.
Jake GmerekJake Gmerek

What is the error, maybe I can help?
OnCloud9OnCloud9

At line #29 (the second else), eclipse says: error: expecting right curly bracket, found 'else'
Jake GmerekJake Gmerek

Yeah I think that you need to add one more right curly bracket before the else.  It looks like we are one short, I could have copied and pasted wrong.
awoelfelawoelfel

Thanks Jake for this addition. I too would like to add the code to ignore null zipcodes. In production we receive this error if the zipcode isn't in the lookup:

 

 

Apex script unhandled trigger exception by user/organization: 005400000016yO1/00D400000008yNy

 

ZipcodeLookup2: execution of BeforeUpdate

 

caused by: System.ListException: List index out of bounds: 0

 

Trigger.ZipcodeLookup2: line 12, column 48

 

 

 

So I added the additional code you provided for OnCloud9 so now my code looks like this:

 

trigger

ZipcodeLookup_w_Null onAccount (beforeinsert, beforeupdate) {

List<String> BillingPostalCodes =

new List<String>();

for

(Account a:Trigger.new)

{

BillingPostalCodes.add(a.BillingPostalCode);

}

List <

Zip_Code__c> ZipCodeList = [SelectNamefromZip_Code__cwhereID in :BillingPostalCodes];

 

if

(ZipCodeList.size() > 0 ) {

for

(Integer i = 0; i < Trigger.new.size(); i++)

{

if

(Trigger.new[i].BillingPostalCode != null)

{

Trigger.

new[i].Zipcode_Lookup__c = ZipcodeList[i].ID;

}

else

{

Trigger.

new[i].Zipcode_Lookup__c = null;

}

}

}

else

{

Trigger.

new[i].Zipcode_Lookup__c = null;

}

}

 

While it saves fine in the IDE, I receive this error when trying to save the code in Salesforce:

 

Error: Compile Error: Variable does not exist: i at line 21 column 13

 

I would appreciate any suggestions you may have.

 

Thank you.
Jake GmerekJake Gmerek
for (Integer i = 0; i < Trigger.new.size(); i++) {
        if (ZipCodeList.size() > 0 ) {
		if (Trigger.new[i].BillingPostalCode != null) {
			Trigger.new[i].Zipcode_Lookup__c = ZipcodeList[i].ID;
		}
		else{
		Trigger.new[i].Zipcode_Lookup__c = null;
		}
	}
	else{
		Trigger.new[i].Zipcode_Lookup__c = null;
	}
}

 Try that.  That is my fault, I was not thinking when I wrote that code, the problem is that once we leave the for loop the variable i becomes undefined.  This is called scope, the scope of i is that for loop.  So we move the if...else block inside the for loop and hopefully the problem goes away.  Sorry about that.
awoelfelawoelfel

Close!

 

Here is the new code:

 

trigger ZipcodeLookup2 on Account (before insert, before update) {
List<String> BillingPostalCodes = new List<String>();
for (Account a:Trigger.new)
{
BillingPostalCodes.add(a.BillingPostalCode);
}
List <Zip_Code__c> ZipCodeList = [Select Name from Zip_Code__c where ID in :BillingPostalCodes];
for (Integer i = 0; i <Trigger.new.size(); i++){
if (ZipCodeList.size() > 0 ) {
if (Trigger.new[i].BillingPostalCode !=null){
Trigger.new[i].Zipcode_Lookup__c = ZipCodeList[i].ID;
}
else{
Trigger.new[i].Zipcode_Lookup__c = null;
}
}
else{Trigger.new[i].Zipcode_Lookup__c = null;
}
}
}

 

Saves and executes without errors. The problem now is that it doesn't populate the lookup field if the zipcode is available. So the Zipcode_Lookup__c field is now always null.

 

I checked the debug logs and the trigger runs both on a new record insert and a changed insert. I doubled checked that the zipcode was available in the Zipcode object.

 

Here is the debug log, if it helps:

 

23.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO
10:42:09.043 (43176000)|EXECUTION_STARTED
10:42:09.043 (43209000)|CODE_UNIT_STARTED|[EXTERNAL]|TRIGGERS
10:42:09.043 (43236000)|CODE_UNIT_STARTED|[EXTERNAL]|01qT000000091e5|ZipcodeLookup2 on Account trigger event BeforeInsert for [new]
10:42:09.044 (44233000)|SYSTEM_METHOD_ENTRY|[3]|LIST.iterator()
10:42:09.044 (44438000)|SYSTEM_METHOD_EXIT|[3]|LIST.iterator()
10:42:09.044 (44459000)|SYSTEM_METHOD_ENTRY|[3]|system.ListIterator.hasNext()
10:42:09.044 (44778000)|SYSTEM_METHOD_EXIT|[3]|system.ListIterator.hasNext()
10:42:09.044 (44797000)|SYSTEM_METHOD_ENTRY|[3]|system.ListIterator.next()
10:42:09.044 (44814000)|SYSTEM_METHOD_EXIT|[3]|system.ListIterator.next()
10:42:09.044 (44988000)|SYSTEM_METHOD_ENTRY|[5]|LIST.add(ANY)
10:42:09.045 (45018000)|SYSTEM_METHOD_EXIT|[5]|LIST.add(ANY)
10:42:09.045 (45027000)|SYSTEM_METHOD_ENTRY|[3]|system.ListIterator.hasNext()
10:42:09.045 (45036000)|SYSTEM_METHOD_EXIT|[3]|system.ListIterator.hasNext()
10:42:09.045 (45486000)|SOQL_EXECUTE_BEGIN|[7]|Aggregations:0|select Name from Zip_Code__c where ID IN :tmpVar1
10:42:09.047 (47396000)|SOQL_EXECUTE_END|[7]|Rows:0
10:42:09.047 (47621000)|SYSTEM_METHOD_ENTRY|[8]|LIST.size()
10:42:09.047 (47653000)|SYSTEM_METHOD_EXIT|[8]|LIST.size()
10:42:09.047 (47674000)|SYSTEM_METHOD_ENTRY|[9]|LIST.size()
10:42:09.047 (47690000)|SYSTEM_METHOD_EXIT|[9]|LIST.size()
10:42:09.047 (47808000)|SYSTEM_METHOD_ENTRY|[8]|LIST.size()
10:42:09.047 (47819000)|SYSTEM_METHOD_EXIT|[8]|LIST.size()
10:42:09.534 (47839000)|CUMULATIVE_LIMIT_USAGE
10:42:09.534|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 1 out of 100
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Number of script statements: 5 out of 200000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 10
  Number of Email Invocations: 0 out of 10
  Number of fields describes: 0 out of 100
  Number of record type describes: 0 out of 100
  Number of child relationships describes: 0 out of 100
  Number of picklist describes: 0 out of 100
  Number of future calls: 0 out of 10

10:42:09.534|CUMULATIVE_LIMIT_USAGE_END

10:42:09.047 (47881000)|CODE_UNIT_FINISHED|ZipcodeLookup2 on Account trigger event BeforeInsert for [new]
10:42:09.264 (264622000)|CODE_UNIT_STARTED|[EXTERNAL]|TerritoryRealign
10:42:09.264 (264770000)|WF_RULE_EVAL_VALUE|null
10:42:09.264 (264893000)|WF_RULE_EVAL_VALUE|null
10:42:09.264 (264965000)|CODE_UNIT_FINISHED|TerritoryRealign
10:42:09.283 (283630000)|CODE_UNIT_FINISHED|TRIGGERS
10:42:09.283 (283642000)|EXECUTION_FINISHED

 

Thanks again for your help!

 

Ann Marie

 
Jake GmerekJake Gmerek

It looks like somehow this line got changed:

 

List <Zip_Code__c> ZipCodeList = [Select Name from Zip_Code__c where ID in :BillingPostalCodes];

 

It should be:

List <Zip_Code__c> ZipCodeList = [Select ID from Zip_Code__c where Name in :BillingPostalCodes];

 

It was correct in earlier versions of your code, so I am not sure what happened.  Give that a shot  and it should help.

 

Jake
awoelfelawoelfel

I can't believe I missed that. Sorry!

 

Works perfect in production.

 

Thanks again.
Jake GmerekJake Gmerek

No problem, 

It was actually the debug log that gave me the clue i needed.  If you look through it you will see that the query was returning 0 results, I wondered why it was doing that and went from there.

 
awoelfelawoelfel

One more little glitch...when I use a data loader I receive a System>listException: List index out of bounds Trigger.ZipcodeLookup2: line 11, column 48 Status code: 13.

 

I'm thinking this is because my code only allows for single entry, which it does without errors.  I tried a couple of things without success to fix this without success.

 

I'm sure you would like to run to the hills at this point but if you would be willing to offer advice, I would be so willing to learn.

 

Here is the code in production:

 

trigger ZipcodeLookup2 on Account (before insert, before update) {
List<String> BillingPostalCodes = new List<String>(); 
for (Account a:Trigger.new)
{
BillingPostalCodes.add(a.BillingPostalCode);
}
List <Zip_Code__c> ZipCodeList = [Select ID from Zip_Code__c where Name in :BillingPostalCodes];
for (Integer i = 0; i <Trigger.new.size(); i++){
if (ZipCodeList.size() > 0 ) {
if (Trigger.new[i].BillingPostalCode !=null){
Trigger.new[i].Zipcode_Lookup__c = ZipCodeList[i].ID;
}
else{
Trigger.new[i].Zipcode_Lookup__c = null;
}
}
else{Trigger.new[i].Zipcode_Lookup__c = null;
}
}
}
Jake GmerekJake Gmerek

This is an interesting problem.  My guess in to what is happening is this:

 

Some of your BillingPostalCodes that you are uploading are blank, so your zipcodeList is shorter than Trigger.new and so at some point it is trying to call an index that is out of bounds.

 

We could solve this one of two ways, I believe that we could use a map instead of a list for zipCodeList and solve it that way, but I am more comfortable using some nested for loops.

 

trigger ZipcodeLookup2 on Account (before insert, before update) {
	List<String> BillingPostalCodes = new List<String>(); 
	
	for (Account a:Trigger.new){
		BillingPostalCodes.add(a.BillingPostalCode);
	}

	List <Zip_Code__c> ZipCodeList = [Select ID, Name from Zip_Code__c where Name in :BillingPostalCodes];

	for (Integer i = 0; i <Trigger.new.size(); i++){
		if (ZipCodeList.size() > 0 && Trigger.new[i].BillingPostalCode !=null){
                        for (Zip_Code__c z:ZipCodeList){
				if (Trigger.new[i].BillingPostalCode == z.name){
		        		Trigger.new[i].Zipcode_Lookup__c = z.ID;
                                }
                        }
		}
		else{
		Trigger.new[i].Zipcode_Lookup__c = null;
		}
		
	}
}

 You'll notice that I realized that we can combine the two original if statements, to eliminate one of the elses and make the code more readable.  Also basically what we are doing is, for each account that meets the criteria in the if statement we are comparing the billingPostalCode to our list of zip codes and then when we find a match we set the appropriate field.  I think that I got all this right, but I do not have a test environment for it so if you have a problem let me know.

 

Jake

 

This was selected as the best answer
awoelfelawoelfel

Jake,

 

I was just able to move this into Production and it works great! No errors with the data loader. Also works with new and updated entries.

 

Thank you so much for sticking with me on this one as you have solved a very complicated territory hierarchy issue!

 

Ann Marie
Jake GmerekJake Gmerek

I'm glad that I could help.
caroline.monkcaroline.monk

How about code for a trigger to update my Lookup_Contact_c field. I have another field created, Contact_c, once the Lookup_Contact_c field is populated and the record is saved, the Contact_c field is updated by workflow with the value of Lookup_Contact_c. I then need, on save, for the Lookup_Contact_c field to clear after updating Contact_c. Ideas? 
lmonterosa1.3927807890214556E12lmonterosa1.3927807890214556E12
This was a super helpful topic. I was able to take your code Jake Gmerek and make it my own by comparing Emails between 2 seperate custom objects and then return the matching id to populate my custom lookup field. 

Best,

Louis M.
Ralf WittenbergerRalf Wittenberger
Hi there, 

I have used this Trigger and changed it, unfortunately, when i thought, I could use for 2 fields in the Contact List, it doesn´t seems to work.

Any idea, why?

Trigger ContactOnCases on Case (before insert, before update) {
List<String> Contact = new List<String>(); 
for (Case a:Trigger.new)
{
Contact.add(a.SuppliedEmail && a.Customer_Number_C__c);
}
    List <Contact> ContactList = [Select ID from Contact where  Email && Customer_Number__c  in :Contact ];
    for (Integer i = 0; i < Trigger.new.size(); i++)
        
if (Trigger.new[i].SuppliedEmail != null) {

    Trigger.new[i].ContactID = ContactList[i].ID; 
}
else
{
Trigger.new[i].ContactID = null;
}
}

Thanks in advance for your help