You need to sign in to do that
Don't have an account?

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!
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.
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
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;
}
}
Your code change works perfectly! Thank you so much!
No problem
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:
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-
Post your modified code and I will edit it to do what you want.
Thanks Jake. Here's my code:
So I am thinking that you probably want something like this:
Essentially, if your zipcode list does not return any responses then just skip the whole process. That should help your issue.
Awesome!! This works exactly as it should. Thank you x 100!
You're the man!!
No problems!!
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?
Not 100% sure what you want, but here is what I think that you want:
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.
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.
What is the error, maybe I can help?
At line #29 (the second else), eclipse says: error: expecting right curly bracket, found 'else'
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.
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.
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.
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:
Thanks again for your help!
Ann Marie
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
I can't believe I missed that. Sorry!
Works perfect in production.
Thanks again.
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.
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;
}
}
}
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.
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
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
I'm glad that I could help.
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?
Best,
Louis M.
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