+ Start a Discussion
Zander ZumbrunnenZander Zumbrunnen 

Child to Parent SOQL Error

Hi,

I am trying to query a formula field from a custom grandchild object of the Account object but I get this error "Invalid field PHQ9_Score__c for SObject Account".

Account -> Assessment and Care Plan -> PS Assessment

Here is my code.

Controller:
public class Suspect_List_Controller {
    public final Account act;
    public Suspect_List_Controller(ApexPages.StandardController stdController) {
        this.act = (Account)stdController.getRecord();
    }

   public Id AccountId = ApexPages.currentPage().getParameters().get('id');

   public String PHQ9 = '012360000019nOBAAY';
   
   List<PS_Assessments__c> PHQ9Records = [SELECT Name, CreatedDate, PHQ9_Score__c FROM PS_Assessments__c WHERE Patient_Assessment_and_Care_Plan__r.Account__c = :AccountId AND RecordTypeId = :PHQ9 AND PHQ9_Score__c >= 9 AND CreatedDate= LAST_N_DAYS:365 ORDER BY CreatedDate asc];

   public List<PS_Assessments__c> getPHQ9() {
        return PHQ9Records;
    }
}

Visualforce:
<apex:page standardController="Account" extensions="Suspect_List_Controller">
    <apex:pageBlock title="Suspect List">
        <apex:pageBlockSection title="PHQ9's over 9">
            <apex:pageBlockTable value="{!PHQ9}" var="record">
                <apex:column value="{!record.Name}"/>
                <apex:column value="{!record.CreatedDate}"/>
                <apex:column value="{!record.PHQ9_Score__c}"/>
            </apex:pageBlockTable>
        </apex:pageBlockSection>
    </apex:pageBlock>
</apex:page>

This is only one part of the Visualforce page so using a report or something similar will probably not work.

I am assuming the error is somewhere in the where clause of the query. The PHQ9 score is a formula field on the PS Assessment. The PS assessment has no direct relation to the account but the Assessment and Care Plan does.

Any information on this type of query would be useful.
Best Answer chosen by Zander Zumbrunnen
Zander ZumbrunnenZander Zumbrunnen
Update:
Error was probably related to the account not being able to reference fields on a grandchild object with the current extension.
Took results from the query and added them 3 separate lists and used an html table as a work-around.

All Answers

Danish HodaDanish Hoda
Hi there,

May I know about this field - Patient_Assessment_and_Care_Plan__r.Account__c?
Zander ZumbrunnenZander Zumbrunnen
Hi Danish,
The Patient_Assessment_and_Care_Plan__c field on the PS Assessment Object is a Master-Detail(Assessment and Care Plan).
The Account__c field on the Assessment and Care Plan Object is a Master-Detail(Account).
Here are some screenshots.
User-added image
User-added image
 
Zander ZumbrunnenZander Zumbrunnen
Update:
Error was probably related to the account not being able to reference fields on a grandchild object with the current extension.
Took results from the query and added them 3 separate lists and used an html table as a work-around.
This was selected as the best answer
Danish HodaDanish Hoda
Yes, you got it correct.