+ Start a Discussion
Parker EdelmannParker Edelmann 

I have a simple trigger that is giving me an Invalid foreign key relationship error

Hello all,

On the Account Object, I have a field called Pipeline Rep (with an API name of Pipeline_Rep__c, obviously) that is a lookup to user. The trigger is supposed to update all related contact's owner to the Pipeline Rep when it is changed. Unfortunately, I'm given an "Invalid foreign key relationship: Account.Pipeline_Rep__c" on lines 8 and 10. Here's the trigger:
trigger UpdateContacttoPipelineRep on Account (after update) {
          
    List<Contact> Contacts_to_Update = new List<Contact>();
    
    for(Contact evaluatecontact: [SELECT Id FROM Contact
                                 WHERE AccountId IN :Trigger.New]) {
   
        if(evaluatecontact.Account.Pipeline_Rep__c.Trigger.old !=evaluatecontact.Account.Pipeline_Rep__c){ 
               System.debug('The Pipeline Rep was changed');
         evaluatecontact.owner.Id = evaluatecontact.Account.Pipeline_Rep__c.Id;
        Contacts_to_Update.add(evaluatecontact);
          }
                                            }
    update Contacts_to_Update;
}
I am new to apex, having only recently finished the Developer Beginner Trail, minus the VisualForce module. I know this can be done with Process Builder, but I thought I'd give it a shot programmatically in my Developer Edition. I'd greatly appreciate it if anyone could tell me what the Invalid foreign key relationship error means, and how to fix it.

Thanks,
Parker
Best Answer chosen by Parker Edelmann
Mahesh DMahesh D
Hi Parker,

Please find the latest code with comments, which will help you to understand the code easily.
 
//
// Trigger on Account object to handle update action.
//
trigger UpdateContacttoPipelineRep on Account (after update) {
    
	// Checking whether the Trigger context it Update or not.
    if(Trigger.isUpdate) {
        Set<Id> accIdSet = new Set<Id>();
		// Iterating through the input records.
        for(Account acc: Trigger.new) {
			// Checking whether the Pipeline_Rep__c is not null and there is a change as part of current update.
			// If it it not changed as part of the current update then we don't need to touch the Owner on Contact.
            if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c) {
                accIdSet.add(acc.Id);
            }
        }
        
		// Checking whether the Set is empty or not.
        if(!accIdSet.isEmpty()) {
            // Querying the Contact List based on the identified Accounts.
            List<Contact> conList = [Select Id, Name, OwnerId from Contact where AccountId IN: accIdSet];
			// Checking whether the Contact List is empty or not.
            if(conList != null && !conList.isEmpty()) {
				// Iterating through the Contact List.
                for(Contact con: conList) {
					// Populating the Owner Id field.
                    con.ownerId = Trigger.newMap.get(con.AccountId).Pipeline_Rep__c;
                }
                // Updating the Contact records.
                update conList;
            }
        }
    }
}

In your code, 

evaluatecontact.Account.Pipeline_Rep__c.Trigger.old 

We can't write like this. If you want to compare current and old values then use below:

if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c)


Please do let me know if it helps you.

Regards,
Mahesh

All Answers

Mahesh DMahesh D
Hi Parker,

Please find the below code:
 
trigger UpdateContacttoPipelineRep on Account (after update) {
    
    if(Trigger.isUpdate) {
        Set<Id> accIdSet = new Set<Id>();
        for(Account acc: Trigger.new) {
            if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c) {
                accIdSet.add(acc.Id);
            }
        }
        
        if(!accIdSet.isEmpty()) {
            
            List<Contact> conList = [Select Id, Name, OwnerId from Contact where AccountId IN: accIdSet];
            if(conList != null && !conList.isEmpty()) {
                for(Contact con: conList) {
                    con.ownerId = Trigger.newMap.get(con.AccountId).Pipeline_Rep__c;
                }
                
                update conList;
            }           
        }
    }
}

Apex class Best Practice :

https://developer.salesforce.com/page/Apex_Code_Best_Practices

Please do let me know if it helps you.

Regards,
Mahesh

 
Parker EdelmannParker Edelmann
Thanks Mahesh, the code works, just had to add AccountId to the SOQL query. I just have a few quick questions for you, then I'll mark it as best.
  1. You have a lot of If statements in your code that almost seem superfluous, can you explain why they're necessary?
  2. Why did my original trigger get the Invalid foreign key relationship error?
  3. Lastly, what does it mean?
I appreciate your answer to my question, keep up the good work.

Thanks again,
Parker
Mahesh DMahesh D
Hi Parker,

Please find the latest code with comments, which will help you to understand the code easily.
 
//
// Trigger on Account object to handle update action.
//
trigger UpdateContacttoPipelineRep on Account (after update) {
    
	// Checking whether the Trigger context it Update or not.
    if(Trigger.isUpdate) {
        Set<Id> accIdSet = new Set<Id>();
		// Iterating through the input records.
        for(Account acc: Trigger.new) {
			// Checking whether the Pipeline_Rep__c is not null and there is a change as part of current update.
			// If it it not changed as part of the current update then we don't need to touch the Owner on Contact.
            if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c) {
                accIdSet.add(acc.Id);
            }
        }
        
		// Checking whether the Set is empty or not.
        if(!accIdSet.isEmpty()) {
            // Querying the Contact List based on the identified Accounts.
            List<Contact> conList = [Select Id, Name, OwnerId from Contact where AccountId IN: accIdSet];
			// Checking whether the Contact List is empty or not.
            if(conList != null && !conList.isEmpty()) {
				// Iterating through the Contact List.
                for(Contact con: conList) {
					// Populating the Owner Id field.
                    con.ownerId = Trigger.newMap.get(con.AccountId).Pipeline_Rep__c;
                }
                // Updating the Contact records.
                update conList;
            }
        }
    }
}

In your code, 

evaluatecontact.Account.Pipeline_Rep__c.Trigger.old 

We can't write like this. If you want to compare current and old values then use below:

if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c)


Please do let me know if it helps you.

Regards,
Mahesh
This was selected as the best answer
Parker EdelmannParker Edelmann
Okay, thank you much for the revised code and that bit of info. I guess I didn't realize that having the if statements were necessary. I'll have to start looking to avoid iterating through sets I don't want changed. Also, learning the proper syntax will help.Thanks.

Best Regards,
Parker
Parker EdelmannParker Edelmann
Hey, @Mahesh, how would you go about getting a contact's account Id in a test? The get method is not working when I put contacts.AccountId inside the parenthesis.
Thanks,
Parker
Mahesh DMahesh D
Hi Parker,

Please find the latest Trigger:
 
trigger UpdateContacttoPipelineRep on Account (after update) {
    
    if(Trigger.isUpdate) {
        Set<Id> accIdSet = new Set<Id>();
        for(Account acc: Trigger.new) {
            if(acc.Pipeline_Rep__c != null && acc.Pipeline_Rep__c != Trigger.oldMap.get(acc.Id).Pipeline_Rep__c) {
                accIdSet.add(acc.Id);
            }
        }
        
        if(!accIdSet.isEmpty()) {
            
            List<Contact> conList = [Select Id, Name, OwnerId, AccountId from Contact where AccountId IN: accIdSet];
            if(conList != null && !conList.isEmpty()) {
                for(Contact con: conList) {
                    con.ownerId = Trigger.newMap.get(con.AccountId).Pipeline_Rep__c;
                }
                
                update conList;
            }           
        }
    }
}

Test Class:
 
@isTest
private class TestUpdateContacttoPipelineRep  {
    @isTest
    static void testMethodOne() {
        Account acc = new Account();
        acc.Name='Test Account';
        insert acc;
        
        Contact cont = new Contact();
        cont.FirstName='Test';
        cont.LastName='Test';
        cont.Email='demo@demo.com';
        cont.Accountid=acc.id;
        insert cont;
        
        Profile profile = [SELECT Id FROM Profile WHERE Name='System Administrator'];
        
        User user = new User();
        user.Alias = 'test';
        user.Email = 'test12345343@abc123.com';
        user.EmailEncodingKey = 'UTF-8';
        user.LastName = 'tester';
        user.LanguageLocaleKey = 'en_US';
        user.LocaleSidKey = 'en_US';
        user.ProfileId = profile.Id;
        user.TimeZoneSidKey = 'America/Los_Angeles';
        user.Username = 'test12345343@abc123.com';
        user.IsActive = true; 
        
        insert user;        
        
        acc.Pipeline_Rep__c  = user.Id;
        update acc;
                    
        Contact contNew = [select Id, OwnerId from Contact where Id =: cont.Id LIMIT 1];
        
        System.assertEquals(acc.Pipeline_Rep__c, contNew.OwnerId);
    }
}

I also tested the Test Class and code coverage is 100%.

Please do let me know if it helps you.

Regards,
Mahesh
Parker EdelmannParker Edelmann
Yes, I'll definately use your Test, but for future reference, how do I use a contacts account field values in a class? In the trigger you gave me, It involves a method specific to triggers. But, in a class, would I simply use map.get(Contact.AccountId).Account_Field__c? What's the best practice in any circumstance for traversing that relationship ladder?