function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
MellowYellowMellowYellow 

Invalid Foreign Key error when trying to lookup a reference field

Newbie Question -
 I am trying to write a trigger which creates a new task when the Contract is saved.
In the task I need to populate the Sales_Name__c field with the value from the contract field Sales_Rep_c.
I am gettin a compile error indicating an invalid foreign key relationship for c.Sales_Rep_c;

Sales_Name__c = [SELECT Name From User Where Id = : c.Sales_Rep_c.id];


If I look at the properties of the Contract field Sales_rep_c it is a reference(custom) with foreign key Sales_rep_r
The Contract field Sales_Rep_c is populated with the correct id value for the user name.

Any help is appreciated!



Best Answer chosen by Admin (Salesforce Developers) 
shruthishruthi

If you need the name you can try these,

 

Sales_Name__c = c.Sales_Rep__r.Name;

or

Sales_Name__c = [SELECT Name From User Where Id = :c.Sales_Rep__c limit 1].Name;



All Answers

sherodsherod

should you be using either of:

 

c.Sales_Rep__r.id  
c.Sales_Rep__c

 

MellowYellowMellowYellow

Doh!  Let me try that.  I've only been working with Apex one week and I've already been burned by that __ double underscore more times that I want to admit.   Why do they use the __ as the standard instead of _ like everyone else?

 

 

Thanks for your help!

MellowYellowMellowYellow

Still getting the Invalid foreign key error after correcting the field name to use the __ double underscore.  I tried both the __c and __r types.

 

sherodsherod

It's likely you are trying to assign an id of TypeA to a lookup field of TypeB.

 

It may be a valid ID, but its an ID to a different kind of object.

 

The double underscores are to tell the difference between space replacements _ and the __C on custom objects and fields.

 

You'll get used to it :)

 

 

shruthishruthi

If Sales_Name__c is of Object type A and c.Sales_Rep__c is of Object type A, You can do something like

Task.Sales_Name__c = c.Sales_Rep__c; [Note: You might need to query for Id if at all you are using a query to get c's data]

or

Task.Sales_Name__c = c.Sales_Rep__r.Id; [Note: You might need to query for Id if at all you are using a query to get c's data]

MellowYellowMellowYellow

Thanks for the suggestions.  Sales_Name__c is field type Picklist.  c.Sales_Rep__c is field type Lookup(User).

 

I am trying to set Sales_Name__c with the user name from c.Sales_Rep__c.

 

Sales_Name__c = c.Sales_Rep__r.id,  // this works (compiles) but returns blank to Sales_Name__c?

Sales_Name__c = c.Sales_Rep__c,  // this returns the correct user id

Sales_Name__c = 'a string'  // this works, it accepts a string

 

I believe that what I need to do is lookup the User Name based on the id, but I haven't been able to get it to compile;

Sales_Name__c =[SELECT Name From User Where Id = :c.Sales_Rep__c],

Sales_Name__c =[SELECT Name From User Where Id = :c.Sales_Rep__r.id],

 

spraetzspraetz

This may be silly, but are you forgetting to give the variable you're using the SOQL query to assign a name?

 

As in

 

Sales_Name__c FOO =[SELECT Name From User Where Id = :c.Sales_Rep__r.id],

shruthishruthi

If you need the name you can try these,

 

Sales_Name__c = c.Sales_Rep__r.Name;

or

Sales_Name__c = [SELECT Name From User Where Id = :c.Sales_Rep__c limit 1].Name;



This was selected as the best answer
MellowYellowMellowYellow

Thank You - I needed to add the .Name to the end of the Select statement.