+ Start a Discussion
louisa barrett 7louisa barrett 7 

reference lookup from custom object in apex code

Hi,

I have created a custom object named Project_Sheet__c, within that object I have a lookup field to the associated Opportunity.
I want to display a list of the opportunity line items on the Project Sheet page layout.
I'm new to Apex and VF, so spologies if this is an easy thing and I've gone about it all wrong.
The code is compiling ok, but I'm getting the following message on the page 
Content cannot be displayed: SObject row was retrieved via SOQL without querying the requested field: Project_sheet__c.Project_sheet_Opportunity__r

I've tried creating a formula field that returns the OpportunityID, and used that in the query instead, but the error message just changes it's reference to the new field instead.
Any help would be gratefully appreciated.

I have the following for my controller and VF page:

Controller:
public class ProjectSheetOpportunityProductsAP {
    public Project_sheet__c custObj;
        
    public ProjectSheetOpportunityProductsAP(){
                   }
    
    public ProjectSheetOpportunityProductsAP(ApexPages.StandardController controller){
        custObj= (Project_sheet__c)controller.getRecord();
           }
    
    public List<OpportunityLineItem> getOppProducts(){
        List<OpportunityLineItem> lstOLI = [SELECT Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: custObj.Project_Sheet_Opportunity__r.ID];
        return lstOLI;
    }
    }

VF:
<apex:page standardController="Project_Sheet__c" extensions="ProjectSheetOpportunityProductsAP" showHeader="false" sidebar="false">
    <apex:pageBlock title="Product list">
    <apex:pageblocktable value="{!OppProducts}" var="oli">
        <apex:column value="{!oli.Quantity}"/>
    </apex:pageblocktable>
    </apex:pageBlock>
</apex:page>

I haven't referenced all of the columns in the VF page yet.

Many thanks
Best Answer chosen by louisa barrett 7
cloudSavvyProgcloudSavvyProg
Hi Louisa,

Then you need to query the records for Project sheet again explicitly.

custObj= (Project_sheet__c)controller.getRecord();

Project_sheet__c projSheet = [Select id, Name , Project_Sheet_Opportunity__c from Project_sheet__c where id = custObj.Id limit 1];

Then use it the following query,
List<OpportunityLineItem> lstOLI = [SELECT Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: projSheet.Project_Sheet_Opportunity__c];

Hope this helps.

Regards,
CloudSavvyProg

All Answers

cloudSavvyProgcloudSavvyProg
Hi Louisa,

The controller.getRecord() only returns parent data not its related lists data. If you need any of the related list data in your case opportunity then you should requery the project sheet records again (soql query).

Between as i can see you only want opportunity Id from project sheet. So in the below query
List<OpportunityLineItem> lstOLI = [SELECT Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: custObj.Project_Sheet_Opportunity__r.ID];

Instead of using custObj.Project_Sheet_Opportunity__r.ID use custObj.Project_Sheet_Opportunity__c. See if that works.
If Project_Sheet_Opportunity__c field is the lookup field for Opportunity in Project sheet custom object,   custObj.Project_Sheet_Opportunity__c should work.

As lookup field references displays Name on the screen, but internally its an Id. So Project_Sheet_Opportunity__c gives you Id value when you reference them in queries.

If you want to reference any other field from opporutnity, then you should use <references relation ship name>__r.<field name> like Project_Sheet_Opportunity__r.Name

Hope all this makes sense.


Regards,
CloudSavvyProg
 
louisa barrett 7louisa barrett 7
Hi,

Many thanks for the reply.
I had that originally and just got this message
Content cannot be displayed: SObject row was retrieved via SOQL without querying the requested field: Project_sheet__c.Project_sheet_Opportunity__c

The Project_sheet_Opportunity__c is the lookup field for the Opportunity.

No matter what I seem to use in the query I get that error
cloudSavvyProgcloudSavvyProg
Hi Louisa,

Then you need to query the records for Project sheet again explicitly.

custObj= (Project_sheet__c)controller.getRecord();

Project_sheet__c projSheet = [Select id, Name , Project_Sheet_Opportunity__c from Project_sheet__c where id = custObj.Id limit 1];

Then use it the following query,
List<OpportunityLineItem> lstOLI = [SELECT Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: projSheet.Project_Sheet_Opportunity__c];

Hope this helps.

Regards,
CloudSavvyProg
This was selected as the best answer
cloudSavvyProgcloudSavvyProg
Sorry, change the line item query as,
List<OpportunityLineItem> lstOLI = [SELECT OpportunityID, Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: projSheet.Project_Sheet_Opportunity__c];

Regards,
ColudSavvyProg
louisa barrett 7louisa barrett 7
Thank you so much.
I understand the whole thing far better now.
It worked perfectly
 
louisa barrett 7louisa barrett 7
Sorry, quick question, would it be possible to pull the records from another custom object on the opportunity onto this VF page as well?
So something like:
SELECT ID, name,(SELECT name FROM Project_checklist__r) FROM Opportunity WHERE OpportunityID = '0068E000003DmJw'

Obviously the ID would be replaced with my projSheet variable that has the Opportunity ID

Thanks
Vishal Negandhi 16Vishal Negandhi 16

yes ofcourse you can do that. 
As long as you have the related record's ID you should be able to query the necessary data and use the same on your page if needed. 

SELECT ID, name,(SELECT name FROM Project_checklist__r) FROM Opportunity WHERE ID = '0068E000003DmJw'

Note: when you query from Opportunity, you simply use Id = 'whatever_id". See text in BOLD.

louisa barrett 7louisa barrett 7
I want to pull back all the available records from the Project_checkilist__r object that were associated with Opportunity ID 0068E000003DmJw

This is what I've got:
 List<Project_Checklist__c> lstChecklists = [SELECT Name,(SELECT name FROM Project_checklist__R) FROM Opportunity WHERE ID =:projSheet.PS2_Opportunity__c];

It won't compile and throws the error that it doesn't understand the relationship 'Project_checklist__r'

The Project_Sheet and Project_Checklist are both custom objects(related lists) on the opportunity.
I wanted to display all of the Project_Checklist records on the Project_Sheet VF Page and vice versa.
I assumed as I had the Opportunity ID, there would be a reference to that in the custom object so my above query would pull the relevant records back.
Apologies, I thought I understood the relationship, but obviously not...... 
cloudSavvyProgcloudSavvyProg
Try Project_checklists__r

I have added s . As the reference names of lookup's are plural names.

Hope this helps.

Regards,
CloudSavvyProg