Saturday, 26 November 2011

Retrieve Related Object Fields

A short post this week, again based on a question from the DeveloperForce Visualforce Discussion Board regarding the retrieval of a related objects fields.  In this particular case, an sobject is being created/edited via Visualforce and once a lookup field is populated, the developer wanted to display some fields related to the lookup.

Unsurprisingly, simply adding some outputfields for the  related object as shown below doesn't do the trick:


<apex:page standardcontroller="Contact">
 <apex:form >
   <apex:pageBlock title="Contact Create/Edit">
      <apex:pageBlockSection title="Contact Information">
       <apex:inputField value="{!contact.FirstName}"/>
       <apex:inputField value="{!contact.LastName}"/>
      </apex:pageBlockSection>
      <apex:pageBlockSection title="Account Information">
       <apex:inputField value="{!contact.AccountId}"/>
       <apex:outputField value="{!contact.Account.AccountNumber}"/>
       <apex:outputField value="{!contact.Account.Site}"/>
      </apex:pageBlockSection>
   </apex:pageBlock>
  </apex:form>
</apex:page>

Once the lookup is selected, the outputfield values remain empty, as all that has been specified is the id of the account via the inputfield - the related object details aren't populated in the sobject graph, so attempting to traverse the account relationship doesn't bring back any data:



One way to get the information back would be to save the contact once the lookup is populated and carry out a client side redirect to the current page with the id of the newly created contact.  When the standard controller retrieves the new sobject, it will populate the related Account information automatically.  This isn't a great user experience though, as the save is likely to be well before the user is ready, and doesn't give them a chance to change their mind part way through.  Likely to lead to a lot of unwanted and half-populated contacts.

Therefore it looks like an extension controller is the route to go. The page has been updated with an actionsupport component that is attached to the account lookup. This invokes an action method on the extension controller which executes a SOQL query to populate the related account information.

Revised Page:

<apex:page standardcontroller="Contact" extensions="RelatedController">
 <apex:form >
   <apex:pageMessages id="msgs"/>
   <apex:pageBlock title="Contact Create/Edit">
      <apex:pageBlockSection title="Contact Information">
       <apex:inputField value="{!contact.FirstName}"/>
       <apex:inputField value="{!contact.LastName}"/>
      </apex:pageBlockSection>
       <apex:actionRegion >
          <apex:pageBlockSection id="accinfo" title="Account Information">
         <apex:inputField value="{!contact.AccountId}">
            <apex:actionSupport event="onchange" action="{!AccountPopulated}" rerender="accinfo, msgs"/> 
         </apex:inputField>
         <apex:outputField value="{!contact.Account.AccountNumber}"/>
         <apex:outputField value="{!contact.Account.Site}"/>
        </apex:pageBlockSection>
      </apex:actionRegion>
      <apex:pageBlockButtons >
        <apex:commandButton value="Cancel" action="{!cancel}"/>
        <apex:commandButton value="Save" action="{!save}"/>
      </apex:pageBlockButtons>
   </apex:pageBlock>
  </apex:form>
</apex:page>

Extension controller:

public with sharing class RelatedController 
{
 private ApexPages.StandardController stdCtrl;
 
 public RelatedController(ApexPages.StandardController std)
 {
  stdCtrl=std;
 }
 
 public void AccountPopulated()
 {
  Contact cont=(Contact) stdCtrl.getRecord();
  cont.Account=[select AccountNumber, Site from Account where id=:cont.AccountId];
 }
}
Choosing the account now carries out an Ajax request and rerenders the account information with the fields populated.


Also, as I've been able to write the information into the object relationship, its the same markup that renders the account information regardless of whether it was retrieved via my extension controller or via the standard controller "reflection" when the page is opened with a specified contact id.

Finally, a word of advice - if you are writing an extension controller for this purpose, make sure that the related object has some data present in the fields, otherwise you'll be convinced your code is failing when in fact its working perfectly, just rendering empty fields!

37 comments:

  1. The onchange event does not seem to fire for a lookup field and so the rerender is not happening.

    I see multiple articles in the discussion boards saying this is a bug. Any insights into this ? How does this work for you ? I am running into this issue.

    ReplyDelete
    Replies
    1. Its working fine for me in chrome - there were problems with this a couple of years ago, but I haven't seen any recently. If onchange isn't working you could try onblur, but that would fire every time the user tabbed out of the lookup. Failing that you'd need to add a button.

      Delete
  2. Hi Bob,

    I tried the above example, you mentioned. If we clear the value of the lookup after the initial selection and again click the Lookup Icon we get an error.

    caused by: System.QueryException: List has no rows for assignment to SObject

    Can you please help with it.

    ReplyDelete
    Replies
    1. That will be the onchange firing with an empty value - just add a check in the AccountPopulated field to skip the query if the account id is null or empty.

      Delete
    2. So you need to use the same solution that I posted.

      Delete
  3. This is not working with custom object

    ReplyDelete
    Replies
    1. There's no reason why a custom object would be different to standard objects. I suggest you post this to the developerforce discussion boards.

      Delete
    2. Hi Bob

      Thanks for ur reply i m getting error when use same stuff with custom object

      Error: RelatedController Compile Error: Illegal assignment from LIST to Id at line 15 column

      Delete
    3. That sounds like you have assigned the query result to an '__c' field, rather than the '__r' relationship. If that isn't the case, post to the boards.

      Delete
  4. how do you save the rendered fields back into salesforce?

    ReplyDelete
    Replies
    1. I don't - that's why they are outputfields. You'd have to change them to inputfields and save the related object.

      Delete
  5. Hello Bob,

    I noticed that if I wipe the account lookup field, the rerender still works (ofcourse), however, it returns a System.QueryException: List has no rows for assignment to SObject;

    Any thoughts? I have to be honest I am very new to apex development and I guess it is because the rerender has no ID to lookup, correct?

    Can you please guide me on how to solve this issue?

    Thanks,

    Ronaldo.

    ReplyDelete
    Replies
    1. It just needs to check if the contact account id is null, and if it is to skip the query.

      Delete
  6. Very useful post. Works as advertised. Thanks Bob

    ReplyDelete
  7. Bob, thanks for posting this. Question: is the required even if I don't intend to display the related values? I need the related values for background calculations but not for display.

    When I remove ActionRegion and submit the page, it seems that actionsupport method is not executed so I get no related data. (I tested this in my submit method by assigning the related record value to a field in the contact. No value was assigned.)

    ReplyDelete
  8. Hi Bob,

    I wanted to pass the value selected using lookup field to a contoller, so that i can write a query using that value. Please help.

    ReplyDelete
  9. Hello Bob,

    Can we do something for the above requirement using a custom controller? Not an extension!

    ReplyDelete
    Replies
    1. Definitely. You'll need to manage the record that you fill in the lookup field on, but aside from that it will all work fine.

      Delete
    2. Hm! I've got a search button ... I need to query User based on the value selected in the Lookup (which is a lookup to user from a custom object). I've used the method you've described in the action method of the search button!

      However, the above doesn't work for some reason ... I mean Im getting NULL!

      Delete
    3. Actually, please ignore my above comment! I got it to work! And Thank you Bob! You did help me solve the issue

      Cheers!

      Delete
  10. Is it possible to do this with custom lookups? I have a Professor__c field, which I want to do the same lookup against Contacts for. But since Professor__c is an Id, I can't put the queried data there.

    If I understand what is happening at least, we are getting a changed AccountId, and filling in the Account object with the details that it would have after a save. Approximately. When I try the same thing (on an opp) opp.Professor__c=[select FirstName, LastName from Contact where id=:opp.Professor__c]; It of course fails, because that code is silly, but I don't know what I need to do to make it accomplish the same ends as your example.

    Do you still exist to help poor lost and confused amateur SF customizers?

    ReplyDelete
    Replies
    1. Did you find the solution? Even I am stuck up here.

      Delete
  11. HI Bob Buzzard,

    My scenario is like this but i am not getting to the solution.
    I have 2fields
    1. Approving Manager(Lookup to user)
    2. Date time(text field type)

    when select the lookup(Approving Manager) , i need to display current date&time automaticallyvfor this field Date time(text field type).

    I used custom components. But idon't want to use Action Function please use any other(action support or action region) these.

    Regards
    kumar

    ReplyDelete
  12. Hi Bob,

    I have a requirement, to display ProductFamily as a picklist and once the user clicks on the family, it has to display all the records, pertaining to that family. Could you please help me?

    Regards,
    Pavan

    ReplyDelete
  13. Its Not Working How Would The selected Id pass Page to Controller that is why its not rendering the output field value on Page. and its also a reason of null value is passing to the queary..

    ReplyDelete
    Replies
    1. The code is working fine - the selected id is sent to the controller as part of the postback via the actionsupport on the apex:inputfield for the lookup. If you are trying to clear down the input field, that needs additional code to handle the id being null, as I've mentioned in a number of comments above.

      Delete
  14. Thanks a lot for the post Bob. Its very helpful !!!

    ReplyDelete
  15. Hi Bob,

    Can you post the code for empty and Null check?

    Thank you

    ReplyDelete
  16. Thanks!!! code was a grt help...

    ReplyDelete
  17. Great Post Bob! i am trying to use this functionality within my salesforce app. We have created a rentals application. The reservations custom object is related to the Inventory custom object, look up relationship.
    I want to display the price per hour (stored in the inventory custom object) in a field on the Reservations page as soon as the customer selects what object they want to rent via the lookup field on Reservation, all this ofcourse before the record is saved.

    Please advise.....

    thanks

    ReplyDelete
  18. It awesome, your post is very usefull for me.. Thank you Bob

    ReplyDelete
  19. It awesome, your post is very usefull for me.. Thank you Bob

    ReplyDelete
  20. This is working great for std objects, Account and Contact. But when I try to apply it to custom objects I keep getting this error.

    Error: Compile Error: Illegal assignment from List to Id at line 13 column 3

    Here's my code which to me looks exactly like Bob's working example:

    public with sharing class RelatedController
    {
    private ApexPages.StandardController stdCtrl;

    public RelatedController(ApexPages.StandardController std)
    {
    stdCtrl=std;
    }

    public void PopulateProject()
    {
    Project__c proj=(Project__c) stdCtrl.getRecord();
    proj.Proposal__c=[select Scope_of_Work__c from Proposal__c where Id=:proj.Proposal__c];
    proj.Scope_of_Work__c=proj.Proposal__c.Scope_of_Work__c;
    }
    }

    ReplyDelete
  21. This is working great now in my sandbox. Any suggestion for how to write a test class for this so I can move it into my production org?

    ReplyDelete