Recipes by Category

App Distribution (2) Bundle logic, interface and services for distribution. App Logic (37) The Apex programming language, workflow and formulas for logic. Collaboration (5) The Salesforce Chatter collaboration platform. Database (29) Data persistence, reporting and analytics. Integration (33) Web Service APIs and toolkits for integration. Security (9) Platform, application and data security. Tools (4) Force.com tooling User Interface (36) Visualforce MVC and metadata-drive user interfaces. Web Sites (12) Public web sites and apps with optional user registration and login.
Beta Feedback
Cookbook Home » Displaying Fields from a Related Record on a Detail Page

Displaying Fields from a Related Record on a Detail Page

Post by Developer Force  (2010-07-16)

Status: Certified
Level: novice

Problem

You want to show field values from a related object on a detail page.

Solution

Use a cross-object formula field to retrieve and display the field values from a related object.

To illustrate this example, we'll look at the Review object in the sample Recruiting application. The Review object is the detail record of the Job Application object. The Job Application object has lookup relationships to the Position and Candidate objects. Using cross-object formulas, we will display the title of the related position and the name of the related candidate on each review record.

  1. Click Setup | Create | Objects.
  2. Click Review.
  3. In the Custom Fields & Relationships related list, click New.
  4. Select the Formula data type, and click Next.
  5. In the Field Label field, enter Position. Once you move your cursor, the Field Name text box should be automatically populated with Position.
  6. Select the Text formula return type and click Next.
  7. Click the Advanced Formula tab.
    You can create cross-object formulas only on the Advanced Formula tab.
  8. Click the Insert Field button.
  9. Select Review > in the first column. The second column displays all of the Review object's fields as well as its related objects, which are denoted by a greater-than (>) sign .
  10. Select Job Application > in the second column. The third column displays the fields of the Job Application object.
  11. Select Position> in the third column. The fourth column displays the fields of the Position object.

    Be sure that you select Position > (with the greater-than sign) and not Position. The one with the greater-than sign is the Position object, while the one without the greater-than sign is the Position lookup field on the Job Application object.

  12. Choose Position Title in the fourth column.
  13. Click Insert. Your formula now looks like this:
    Job_Application__r.Position__r.Name
  14. Click Next.
  15. Configure the remaining field-level security and page layout settings as desired.
  16. Click Save.

The Review object now displays the value of the Position Title field from the related position record. Next, create a cross-object formula field on the Review object that displays the first and last names of the candidate being reviewed, and we'll use the HYPERLINK function so that users can access the candidate's record by clicking the formula field.

  1. Click Setup | Create | Objects.
  2. Click Review.
  3. In the Custom Fields & Relationships related list, click New.
  4. Select the Formula data type, and click Next.
  5. In the Field Label field, enter Candidate. Once you move your cursor, the Field Name text box should be automatically populated with Candidate.
  6. Select the Text formula return type and click Next.
  7. Click the Advanced Formula tab.
  8. From the Functions list, double-click HYPERLINK.
  9. Delete url from the HYPERLINK function you just inserted, but leave your cursor there.
  10. Click the Insert Field button, select Review >, Job Application >, Candidate >, Record ID, and click Insert.
  11. Delete friendly_name from the HYPERLINK function, but leave your cursor there.
  12. Click the Insert Field button, select Review >, Job Application >, Candidate >, First Name, and click Insert.
  13. Enter a space, click the Insert Operator button, and choose Concatenate.
  14. Enter another space, then type a blank space enclosed in quotes:
    " "
    
    This appends a blank space after the first name of the candidate.
  15. Enter a space, click the Insert Operator button, and choose Concatenate once more to add a second ampersand in your formula.
  16. Click the Insert Field button, select Review >, Job Application >, Candidate >, and Last Name, then click Insert.
  17. Delete [ target ] from the HYPERLINK function. This is an optional parameter that isn't necessary for our formula field.
  18. Click Check Syntax to check your formula for errors. Your finished formula should look like this:
    HYPERLINK( Job_Application__r.Candidate__r.Id , Job_Application__r.Candidate__r.First_Name__c & " " & Job_Application__r.Candidate__r.Last_Name__c )
    
  19. Click Next.
  20. Configure the remaining field-level security and page layout settings as desired.
  21. Click Save.

Discussion

Cross-object formulas are formulas that span two or more objects by referencing merge fields from related records. They are available anywhere you can use formulas except for default values and summary reports. Use them in calculations or simply to display fields from related objects on detail pages, list views, related lists, and reports.

Each formula can reference up to five related objects, and can span to an object that is five relationships away. For example, consider the following formula we created in the first set of solution steps:
Job_Application__r.Position__r.Name
This formula spans two relationships: first it spans to the review's related job application (Job_Application__r), then to the job application's related position (Position__r). The formula ultimately references the position's title (Name) on the Position object. Notice that each part of the formula is separated by a period, and that the relationship names consist of the related object followed by __r.

In the second cross-object formula field we created, we used the Concatenate (&) operator to join two separate fields (First_Name__c and Last_Name__c) and inserted a space between them. We also used the HYPERLINK function, which lets you to create a hyperlink to any URL or record in Salesforce.com. Note that the label of the hyperlink can differ from the URL itself, which is especially useful when working with a cross-object formula field that displays a value that a user will want to click. In this recipe, we used the HYPERLINK function to let users conveniently access the candidate's record by clicking the Candidate's Name field on the Review object.

Share

Recipe Activity - Please Log in to write a comment

good

voted as verified by a093000000YjXDO  (2015-04-08)

This is wonderful except one little issue. If you wanted to use the Candidate name in an approval process, this would not work because the candidate field created is a formula field. The field required to an approval process would have to be a lookup field to the user detail record. The only way I know is to either create a trigger or a URL button to fill the field. Any ideas to get around this would be appreicated. Just something for everyone to keep in mind if they are filling fields this way.

by Karol Freeberg  (2014-03-26)

How can this technique be applied to Opportunities and a related Custom Object?  For instance, I have a Related Custom Object called "Bidding Scopes" where any Opportunity could have 1-50 Bidding Scopes.  The Bidding Scope Record has a user lookup field to indicate who is responsible for that record within our organization.  I'm trying to create a formula-type field on the opportunity page that would look at that User Lookup field in the Bidding Scope Records and show/summarize all unique entries from the various Record Lookup fields without displaying any duplicate entries (since a user can have responsibility over more than one Bidding Scope Record).  Is this possible?  When creating a formula field on the opportunity, I cannot drill down into the bidding scopes related object at all.

by a093000000Wc8SX  (2013-02-12)

This was EXACTLY what I was looking for, a key piece of information that is needed early on in Force.com development.

voted as verified by Aaron Bean  (2012-12-14)

This is good reference, but rather than the point and click solutions, I would like to see how to acheive this same functionality within an Apex trigger.

Using this  "__r" suffix within an Apex trigger DOES NOT WORK! It always returns NULL - unless one builds and populates a map of the related records first.

How is this done? What are the caveats and how does one ensure the trigger is properly "bulkified." 

Would love to see some straightforward examples.

Thanks!

by Alexis Kasperavicius  (2012-04-27)

X

Vote to Verify a Recipe

Verifying a recipe is a way to give feedback to others and broaden your own understanding of the capabilities on Force.com. When you verify a recipe, please make sure the code runs, and the functionality solves the articulated problem as expected.

Please make sure:
  • All the necessary pieces are mentioned
  • You have tested the recipe in practice
  • Have sent any suggestions for improvements to the author

Please Log in to verify a recipe

You have voted to verify this recipe.