+ Start a Discussion
hal9001hal9001 

Query parent field

I have two custom tables:

Location__c (Master)

Trans__c (Detail)

 

When I run this query:

select

Qty__c,

Location__c,

Location__r.Location_Type__c

from Trans__c

 

I get the field name "Location__c" for the third field instead of the value from that field in the parent table.  What am I doing wrong?

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
Starz26Starz26

When you say you get the Field Location__c, how are you viewing it?

 

Trans__c[] t = [select Qty__c, Location__c, Location__r.Location_Type__c from Trans__c];

System.debug(t);

 

Above will only output Location__c (ID Of Master Record)

 

Trans__c[] t = [select Qty__c, Location__c, Location__r.Location_Type__c from Trans__c];

 

for(Trans__c tr : t)

     System.debug(tr.Location__r.Location_Type__c);

 

Above will output the value of the Location_Type__c field on the master for each child record.

 

All Answers

Starz26Starz26

When you say you get the Field Location__c, how are you viewing it?

 

Trans__c[] t = [select Qty__c, Location__c, Location__r.Location_Type__c from Trans__c];

System.debug(t);

 

Above will only output Location__c (ID Of Master Record)

 

Trans__c[] t = [select Qty__c, Location__c, Location__r.Location_Type__c from Trans__c];

 

for(Trans__c tr : t)

     System.debug(tr.Location__r.Location_Type__c);

 

Above will output the value of the Location_Type__c field on the master for each child record.

 

This was selected as the best answer
hal9001hal9001

That worked, but I'm still having a hard time understanding how to construct a query from a child table that includes fields from parent or grandparent tables.

Starz26Starz26

Simply navigate through the relationships....

 

The field that links the child to the parent uses __r instead of __c when navigating to the parent....

 

i.e. From child 3 object:

 

child2__r.child1_r.parent__r.ID will give you the ID of the record that is the great-grand parent of the child 3

 

 

J. J. MarkJ. J. Mark

I think the problem is that the order of the relationships is counter-intuitive. Once that is cleared up, things start to flow.

 

SInce it's counter-intuitive, I'd refine the explanation above as follows:

 

(parent).(field)

(parent).(grandparent).(field)
(parent).(grandparent).(great-grandparent).(field)

 

etc up to 5 levels.

 

Here are two examples:

 

(1) 
Relationship chain from grandparent to parent to child:
Opportunity - Quote - QuoteLineItem

 

Sample query:
SELECT Id, Quantity, Quote.Name, Quote.Opportunity.Name FROM QuoteLineItem

 

(2)

Relationship chain from grandparent to parent to child:
Account - Bill_to_Contact__r (custom) - Quote

 

Sample query:
SELECT Name, Bill_To_Contact__r.Name, Bill_To_Contact__r.Account.Name FROM Quote