Understanding the Force.com lookup and master-detail relationships, and how to access the related fields using the relationships, is one of the most important starting points for any Force.com project. This topic can be quite confusing if we don’t understand it but can be equally rewarding once we ‘get’ it!

In this article, I have tried to compile some useful tips for accessing the parent objects from a given child object, and accessing the child objects from a given parent object. Getting a hang of __r and __c relationship fields is obviously the key which is what I have tried to explain.

How to get to the fields of the lookup or parent objects for a given child object (Upward traversal)

1. Find out the lookup or the parent objects that you need to access. There are two ways you can determine this. In our example, our goal to identify lookup and master-detail relationships in Line_Item__c custom object.

a. Setup->Create->Objects. Select the object. Study ‘Custom Fields & Relationship’ section of Line_Item__c

Now, in order to get to the lookup or parent object from the Line Items object (child), we should replace __c by __r in the relationship field name of the Line Items object. In our example Merchandise__r will give us access to the fields of the Merchandise object from within the Line Items object; Invoice__r will give us access to the fields of the Invoice object from within the Line Items object.

b. Using Eclipse Force.com IDE, browse salesforce.schema. I like this option over the first one as the schema can give us a better view of all the relationships, field names with exact API names without any guess work. A closer view reveals two custom parent (lookup or master-detail) relationship fields as noted by word ‘reference’.

Going a level down tells us the name of the relationship to the related parent objects. In our case, Line_Item__c object has a master-detail relationship to Invoice__c (master object) using the master-detail field Invoice__c. Schema tells us to use Invoice__r for us to get to the Invoice__c’ fields. And, use Merchandise__r relationship to get to the Merchandise__c object as depicted below.

2. Now that we know the relationship name, we can reference any fields from the lookup or master objects. As an example, in order to refer the invoice name, invoice status and quantity of the line item, we will use the following SOQL when working within Line_Items__c

Select Invoice__r.Name, Invoice__r.Status__c, Quantity__c from Line_Item__c where CreatedDate < TODAY

How to get to the fields of child objects in a lookup or master-detail relationship for given parent object (Downward traversal)

1. Find out the related child objects. I would recommend using schema browser in Ecplise Force.com IDE.
2. Go to the object for which we are trying to find child relationships. In our example, let us focus on Invoice__c.

3. Drill down further into ‘Child Relationships’ node.
4. You will find two child relationships: 1) to Delivery__c and 2) to Line_Item__c

5. Relationship name to Delivery__c child object is Deliveries__r
6. Relationship name to Line_Item__c child object is Line_Items__r.
7. Now, we can traverse down to any fields in the child objects. As an example, to get quantity from all related child line items for invoice ‘INV-0000’, SOQL will look like:

select name, (select quantity__c from line_items__r) from invoice__c where name = ‘INV-0000’

We can include other child relationships as needed. So, to get order numbers from the related deliveries:

select name, (select quantity__c from line_items__r), (select order_number__c from deliveries__r) from invoice__c where name = ‘INV-0000’

Kudos to Avi’s blog source: http://11concepts.com/sfdc/blog/understanding-__c-and-__r/