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
Larry D. DortchLarry D. Dortch 

SOQL to filter Child records matched to Account

Hello Group,

I'm trying to create a simple inline VF page but not able to filter just the records I need.

I'm working with 3 objects = Accounts >Customer Visit >Visit Notes.  
I just want to display the last 2 or 3 "Visit Notes" records when a new "Customer Visit" is created,  so our Reps can more easily follow up on their action items.

Customer Visit is a Lookup to Accounts, and Visit Notes is a Master-Detail to Customer Visit.
Visit Notes also has a Lookup relationship to Accounts.

I've tried several different WHERE clauses but only to produce a variety of compiler errors.
Any help would be greatly appreciated!
Best Answer chosen by Larry D. Dortch
EldonEldon

public class RelatedListExtension {

    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
	    this.CustomerVisitRecord = (CustomerVisit__c)controller.getRecord();
	    CustomerVisit__c CustomerVisitRecordFinal = [select id,Customer__c,Customer__r.name from CustomerVisit__c where id =: CustomerVisitRecord.id];

	    //add debug statement to view CustomerVisitRecordFinal results
	    system.debug('CustomerVisitRecordFinal results '+CustomerVisitRecordFinal);
		
	formResults = [SELECT Id, Name, Visit_Comments__c, from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__r.name ORDER BY CreatedDate DESC Limit 5];

		           //add debug statement to view results of SOQL statement
                     system.debug('formResults '+formResults);

  }
}

I forgot to query the name field. Try it.

All Answers

NForceNForce
Hi Larry,
Follow the steps provided in the below link
http://sfdcsrini.blogspot.com/2014/08/how-to-use-inline-visualforce-page.html.

Thanks,
Vasu
 
EldonEldon
Hi Larry,

Let me get this right, You have Visit Notes which is the master(parent) to Customer Visit and Customer Visit which is a Parent(lookup) object to Accounts object. You have a lookup relation from Visit Notes to Account object. Am i right?

Now you need an inline VF page in Customer Visit object which will display the latest Visit Notes (2 records). Do you need any particular filter criteria while displaying this Visit notes records?

Please explain a little.
Larry D. DortchLarry D. Dortch
Hi Eldon,

You're close.  Everything is correct except Visit Notes is the (Detail) to Customer Visit the (Master).
So the hierarchy is: Accounts 1) Customer Visit 2) Visit Notes 3).
The filter criteria is to return only the Visit Notes records for the related Account.

Here is the code I'm using now that works great to display the 5 most recent Visit Notes records.
But when I try to add a WHERE clause such as,   = to the AccountId or IN ('Account')  I just get compiler errors.
  
public List<VisitNotes__c> formResults {
            get
        {
            return [SELECT Id, Name, Visit_Comments__c,
                      from VisitNotes__c 
	              ORDER BY CreatedDate DESC Limit 5];
                    
   }
 }




 
EldonEldon
If the customer Visit is a parent to the account object there is a possibility of multiple accounts having same customer Visit record. Which account will you use then to filter the visit notes records?
EldonEldon
If account is the parent to 'customer visit' object we can query the 'visit notes' records having same parent account as that of the 'customer visit's parent account. The code is given below,
  
public class RelatedListExtension {
    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
        this.CustomerVisitRecord= (CustomerVisit__c)controller.getRecord();
        
		
		formResults = [SELECT Id, Name, Visit_Comments__c,
                      from VisitNotes__c where accountid =: CustomerVisitRecord.accountid ORDER BY CreatedDate DESC Limit 5];
	}
}

 
Larry D. DortchLarry D. Dortch
Sorry Eldon if I confused you but,  the Account is the ultimate parent.  So we have 1 Account with many Customer Visits and each Customer Visit has a Visit Notes record.

So what I want to do is,  when a new "Customer Visit" is created (display in the visualforce page) the last 2 or 3 "Visit Notes" records (created from the prior Customer Visits) that are specific to the 1 Account.

My reps visit their customers 1 once every week.
EldonEldon
Try the above code and let me know how it goes
Larry D. DortchLarry D. Dortch
Will do Eldon,  thank you much!
Larry D. DortchLarry D. Dortch
Hey Eldon,

I tried the code but producing new errors.
In the WHERE clause:  where accountid =: CustomerVisitRecord.accountid - I get compile error:  Variable does not exist accountid

I changed accountid with the Lookup field name to Account 'Customer__c'  - it compiles and I can Save but the visualforce page displays this error:
Content cannot be displayed: SObject row was retrieved via SOQL without querying the requested field: CustomerVisit__c.Customer__c

I searched around and tried a few things such as adding Customer__c to the SOQL and the VF but still get the same error.
Seems like this is very close to working.  Any additional help you can offer is greatly appreciated.
EldonEldon
COuld you try this code, (look for the correct lookup field names you have given) 
public class RelatedListExtension {
    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
        this.CustomerVisitRecord= (CustomerVisit__c)controller.getRecord();
        CustomerVisit__c CustomerVisitRecordFinal = [select id,Customer__c from CustomerVisit__c where id =: CustomerVisitRecord.id];
		
		formResults = [SELECT Id, Name, Visit_Comments__c,
                      from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__c ORDER BY CreatedDate DESC Limit 5];
	}
}

Regards
Larry D. DortchLarry D. Dortch
Hi Eldon,
The new code saved with no errors,  but unfortunately - no records display in the visualforce page.

inline visualforce page
EldonEldon
Could you share your VF code.
Larry D. DortchLarry D. Dortch
Here's the VF code.  var="vn" is used for the Visit Notes fields. Output link is so the user can navigate to the Visit Notes record.
  
<apex:page standardController="CustomerVisit__c" extensions="RelatedListExtension">
    <apex:form >
        <apex:pageBlock title="Prior Visit Notes" id="block" mode="edit">
            <apex:pageBlockSection id="resultsBlock" columns="1">
                <apex:pageBlockTable value="{!formResults}" var="vn" columns="2">
                    
                    <apex:column headerValue="Call Report">
                        <apex:outputLink value="/{!vn.Id}">
                            <apex:outputText value="{!vn.Name}"/>
                            </apex:outputLink>
                            </apex:column>
                            
                    <apex:column headerValue="Visit Comments">
                    <apex:outputText value="{!vn.Visit_Comments__c}"/>
                    </apex:column>
                    
                </apex:pageBlockTable>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>

 
EldonEldon
Try this VF page and see if the names are displaying, 
<apex:page standardController="CustomerVisit__c" extensions="RelatedListExtension">
    <apex:form >
        <apex:pageBlock title="Prior Visit Notes">
			 <apex:pageblocktable value="{!formResults}" var="vn"  >
				
				<apex:column value="{!vn.Name}"/>
				
            </apex:pageblocktable>
        </apex:pageblock>
    </apex:form>
</apex:page>

Also debug this formresults after SOQL statement in the apex code and check if you are getting results there.
formResults = [SELECT Id, Name, Visit_Comments__c,
                      from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__c ORDER BY CreatedDate DESC Limit 5];
        system.debug('formResults'+formResults); //add this line

Regards
EldonEldon
Hi,

Could you use only the VF and the apex code i shared and see if you are displaying the names you wanted.

What do you meanby StoreVisitRecordFinal variable? Please share the VF and apex code you are using now.
Larry D. DortchLarry D. Dortch
Sorry Eldon,  ignore that provious reply.  I got confused by another project I'm working on.
You'll notice I added a line to debug the results of CustomerVisitRecordFinal and it's working well.
It does return the Id's for the Customer Visit (CustomerVisit__c) and related Account (Customer__c) record.
But debug for formResults is returning no data. 
<apex:page standardController="CustomerVisit__c" extensions="RelatedListExtension">
    <apex:form >
        <apex:pageBlock title="Prior Visit Notes">
	 <apex:pageblocktable value="{!formResults}" var="vn"  >
	        <apex:column value="{!vn.Name}"/>		
            </apex:pageblocktable>
        </apex:pageblock>
    </apex:form>
</apex:page>
public class RelatedListExtension {

    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
	    this.CustomerVisitRecord = (CustomerVisit__c)controller.getRecord();
	    CustomerVisit__c CustomerVisitRecordFinal = [select id,Customer__c from CustomerVisit__c where id =: CustomerVisitRecord.id];

	    //add debug statement to view CustomerVisitRecordFinal results
	    system.debug('CustomerVisitRecordFinal results '+CustomerVisitRecordFinal);
		
	formResults = [SELECT Id, Name, Visit_Comments__c,
               	       from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__c ORDER BY CreatedDate DESC Limit 5];

		           //add debug statement to view results of SOQL statement
                     system.debug('formResults '+formResults);

  }
}


 
EldonEldon
Could you check if you have VisitNotes records having the same parent account as that of the corresponding customervisits parent account?
 
Larry D. DortchLarry D. Dortch
VisitNotes records does have an Account field showing the same Account as on the CustomerVisits records.
But the Account field on VisitNotes is a formula field (Text) using the formula (Customer_Visit__r.Customer__r.Name)
EldonEldon
Then that is not a lookup field, we have to compare with name instead. Try the following apex code,
  
public class RelatedListExtension {

    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
	    this.CustomerVisitRecord = (CustomerVisit__c)controller.getRecord();
	    CustomerVisit__c CustomerVisitRecordFinal = [select id,Customer__c from CustomerVisit__c where id =: CustomerVisitRecord.id];

	    //add debug statement to view CustomerVisitRecordFinal results
	    system.debug('CustomerVisitRecordFinal results '+CustomerVisitRecordFinal);
		
	formResults = [SELECT Id, Name, Visit_Comments__c,
               	       from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__c.name ORDER BY CreatedDate DESC Limit 5];
//formResults = [SELECT Id, Name, Visit_Comments__c, from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__r.name ORDER BY CreatedDate DESC Limit 5]; //Try this if above SOQL didnt work

		           //add debug statement to view results of SOQL statement
                     system.debug('formResults '+formResults);

  }
}

 
Larry D. DortchLarry D. Dortch
The 1st SOQL produced compiler error: A non foreign key field cannot be referenced in a path expression: Customer__c

The 2nd SOQL saved but produced VF page error:  Content cannot be displayed: SObject row was retrieved via SOQL without querying the requested field: CustomerVisit__c.Customer__r

I really appreciate all your help Eldon and if we get it working I owe you big time!  :-)
EldonEldon

public class RelatedListExtension {

    public List<VisitNotes__c> formResults {get;set;}
    private CustomerVisit__c CustomerVisitRecord; 
    
    public RelatedListExtension (ApexPages.StandardController controller) {
	    this.CustomerVisitRecord = (CustomerVisit__c)controller.getRecord();
	    CustomerVisit__c CustomerVisitRecordFinal = [select id,Customer__c,Customer__r.name from CustomerVisit__c where id =: CustomerVisitRecord.id];

	    //add debug statement to view CustomerVisitRecordFinal results
	    system.debug('CustomerVisitRecordFinal results '+CustomerVisitRecordFinal);
		
	formResults = [SELECT Id, Name, Visit_Comments__c, from VisitNotes__c where Customer__c =: CustomerVisitRecordFinal.Customer__r.name ORDER BY CreatedDate DESC Limit 5];

		           //add debug statement to view results of SOQL statement
                     system.debug('formResults '+formResults);

  }
}

I forgot to query the name field. Try it.
This was selected as the best answer
Larry D. DortchLarry D. Dortch
Sorry Eldon but I get Compile Error: only aggregate expressions use field aliasing
I also tried Customer__c.name but produced the same error.
Larry D. DortchLarry D. Dortch
wait a minute,  think I found my error (missing comma).  Now the names are showing!!!
I'll add the Visit Comments field to the VF page and let you know.  :-)
User-added image
Larry D. DortchLarry D. Dortch
It's working Eldon!  You are the man.  :-)
Thank you so much for all your help!!!
EldonEldon
Sorry for the typo. You are welcome :)