You need to sign in to do that
Don't have an account?
louisa 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
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
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
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
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
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
List<OpportunityLineItem> lstOLI = [SELECT OpportunityID, Quantity,PriceBookEntry.Name FROM OpportunityLineItem WHERE OpportunityID =: projSheet.Project_Sheet_Opportunity__c];
Regards,
ColudSavvyProg
I understand the whole thing far better now.
It worked perfectly
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
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.
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......
I have added s . As the reference names of lookup's are plural names.
Hope this helps.
Regards,
CloudSavvyProg