I have Application object and References as Child object.

I want to display list of Applications and corresponding "number of References records" for each application in VF Page. There is a status picklist field in References with values - Confirmed & Completed. I want to display something like below

  Application Name   Confirmed References   Completed References

           ABC                1                      1

           XYZ                2                      1

With the below query I'm able to get the total count of completed and confirmed references count together but not Individual count as above. How do I get the individual confirmed and completed count for each application in a simple way!

List<Application__c> ReviewList = [SELECT Id,Name,(SELECT Id,Name,Status__c FROM References__r WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed'))
                                       FROM Application__c];

The references__r.size below gives the total count of the records returned in the Inner Query (in this case count of confirmed & completed records together).

<apex:pageBlockTable value="{!ReviewList}" var="item">
   <apex:column headerValue="Application Name">
       <apex:outputText value="{!item.Name}" />
   <apex:column headerValue="References">
       <apex:outputText value="{!item.References__r.size}" />

There can be two solutions

  1. Apex based:- retrieve all related records in apex and then perform aggregation in apex.

Basically, extract all required records and their references (completed or confirmed)

[SELECT Id,Name,(SELECT Id,Name,Status__c FROM References__r WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed'))
                                       FROM Application__c];

Iterate over all records in apex code to calculate aggregated values

  1. SOQL + Formula fields:- Create two formula fields (of type number) within "References__c" custom object
    • Complete = Should return 1 if "status__c = Complete"
    • Confirmed = Should return 1 if "status__c = Confirmed"

Use following SOQL query to extract application record along with completed and confirmed references (verify field names):-

SELECT Application__r.Name, SUM(IsComplete), SUM(IsConfirmed) FROM References__c WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed')
GROUP BY Application__r.Name

Considering performance of formula fields in SOQL query, if data volume is high, I would recommend using first approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.