function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
deux4everdeux4ever 

Filtering SOQL query for VF ooutput

My requirement is to display records of a child's child's records in the parent (Account). I am able to do this via Apex, SOQL and Visualforce for output, but the problem I am running into is being able to filter out records that don't pertain to the current account (outputted via Visualforce inside the Account page layout. It currently returns all records). How can I filter out to only show the records of the child's child that pertain to the Account?

The account Id in the SOQL statement is "Current_Product__r.Current_Services__r.Account__r.Id"

Class:
public class PEQueryController{
    private final Account acct;
    public PEQueryController(ApexPages.StandardController stdController){
        this.acct = (Account)stdController.getRecord();
    }
    public List<Price_Exception__c> getRelatedPEs(){
        List<Price_Exception__c> pequery = [Select Name, Proposed_Oil_Rate__c, Proposed_Price_Ranking__c, Price_Exception_Reason__c, Approval_Status__c, Current_Product__r.Current_Services__r.Account__r.Id From Price_Exception__c];
        return pequery;
    }
}
Visualforce page:
<apex:page standardController="Account" extensions="PEQueryController" >
    <apex:pageBlock>
        <apex:pageBlockTable value="{!RelatedPEs}" var="pe">
            <apex:column>
                <apex:pageBlockTable value="{!pe.Name}" var="peName">        
                    <apex:column headerValue="Price Exception Name">
                        <apex:outputField value="{!pe.Name}"/>
                    </apex:column>
                    <apex:column headerValue="Proposed Price Ranking">
                        <apex:outputField value="{!pe.Proposed_Price_Ranking__c}"/>
                    </apex:column>
                    <apex:column headerValue="Proposed Oil Rate">
                        <apex:outputField value="{!pe.Proposed_Oil_Rate__c}"/>
                    </apex:column>
                    <apex:column headerValue="Price Exception Reason">
                        <apex:outputField value="{!pe.Price_Exception_Reason__c}"/>
                    </apex:column>
                    <apex:column headerValue="Approval Status">
                        <apex:outputField value="{!pe.Approval_Status__c}"/>
                    </apex:column>
                    <apex:column headerValue="Account.Id">
                        <apex:outputField value="{!pe.Current_Product__r.Current_Services__r.Account__r.Id}"/>
                    </apex:column>
                </apex:pageBlockTable>
            </apex:column>		
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

kk
Hi,

Please use the below statememnt to get Account Id in apex class constructor and filter it in in the SOQL

Id AcctId = ApexPages.currentPage().getParameters().get('Id')
deux4everdeux4ever
So I tried the below and I got an error on line 8 stating 'invalid ID field: AcctId'

public class PEQueryController{
    private final Account acct;
    public Id AcctId = ApexPages.currentPage().getParameters().get('Id');
    public PEQueryController(ApexPages.StandardController stdController){
        this.acct = (Account)stdController.getRecord();
    }
    public List<Price_Exception__c> getRelatedPEs(){
        List<Price_Exception__c> pequery = [Select Name, Proposed_Oil_Rate__c, Proposed_Price_Ranking__c, Price_Exception_Reason__c, Approval_Status__c, Current_Product__r.Current_Services__r.Account__r.Id From Price_Exception__c Where Current_Product__r.Current_Services__r.Account__r.Id = 'AcctId'];
        return pequery;
    }
}
kk
Hi,

Please try the below statement, I am not sure whether you can use 2 level relation field (Current_Product__r.Current_Services__r.Account__r.Id)
If this doesn't work you need to perform one more query on Current_Services__c object based on Account ID .

public class PEQueryController{
    private final Account acct;
    public PEQueryController(ApexPages.StandardController stdController){
        this.acct = (Account)stdController.getRecord();
    }
    public List<Price_Exception__c> getRelatedPEs(){
        List<Price_Exception__c> pequery = [Select Name, Proposed_Oil_Rate__c, Proposed_Price_Ranking__c, Price_Exception_Reason__c, Approval_Status__c, Current_Product__r.Current_Services__r.Account__r.Id From Price_Exception__c Where Current_Product__r.Current_Services__r.Account__r.Id = : ApexPages.currentPage().getParameters().get('Id')];
        return pequery;
    }
}
deux4everdeux4ever
Yes, this did not work. What do you mean by performing a query for the Current_Services__c object? How would I use that as a filter?
kk
Please see the sample code below

Public List<Price_Exception__c> getRelatedPEs(){
List<Current_Services__c> currSer = [select Id from Current_Services__c where Account__c = : ApexPages.currentPage().getParameters().get('Id')]
set<Id> currSerId = new set<Id>();
for (Current_Services__c cr:currSer)
{currSerId.add(cr.Id))}
        List<Price_Exception__c> pequery = [Select Name, Proposed_Oil_Rate__c, Proposed_Price_Ranking__c, Price_Exception_Reason__c, Approval_Status__c, Current_Product__r.Current_Services__r.Account__r.Id From Price_Exception__c Where Current_Product__r.Current_Services__r.Account__r.Id in: currSerId];
        return pequery;
    }
deux4everdeux4ever
I figured out the solution. I just used the correct syntax below.
public class PEQueryController{
    private final Account acct;
    public Id AcctId = ApexPages.currentPage().getParameters().get('Id');
    public PEQueryController(ApexPages.StandardController stdController){
        this.acct = (Account)stdController.getRecord();
    }
    public List<Price_Exception__c> getRelatedPEs(){
        List<Price_Exception__c> pequery = [Select Name, Proposed_Oil_Rate__c, Proposed_Price_Ranking__c, Price_Exception_Reason__c, Approval_Status__c, Current_Product__r.Current_Services__r.Account__r.Id From Price_Exception__c Where Current_Product__r.Current_Services__r.Account__r.Id = : ApexPages.currentPage().getParameters().get('Id')];
        return pequery;
    }
}