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.