1

I am building a Visualforce PDF page with a controller extension to display details from the parent object and child object in the same table. The table's first two columns reference the records list from the extension to provide high level information and then the third column references the records2 list from the extension to retrieve all of the activity records related to the records list.

The architecture is the Visualforce page references the standard controller for the SalesPlan__c object and also references a controller extension. There is a child object of SalesPlan__c called SalesPlanGoal__c where high level objectives are captured. Then there is a child of SalesPlanGoal__c called SalesPlanActivity__c where actionable tasks are noted. SMART_Goal__c is the name of the lookup field for SalesPlanGoal__c object on SalesPlanActivity__c.

I have no problem displaying the Name and Description of the SalesPlanGoal__c records as separate rows in a table (i.e. Initiative and SMART Goals columns in image below). I am struggling with filtering the SalesPlanActivity__c records to show in the third column (i.e. Benchmark Activities) with their corresponding SalesPlanGoal__c record. I am currently displaying all SalesPlanActivity__c records for the SalesPlan__c on each SalesPlanGoal__c instead of having all the SalesPlanActivity__c records specific to a SalesPlanGoal__c.

enter image description here

Below is my controller extension:

public with sharing class SalesPlanPDFExtension{

public List<SalesPlanGoal__c> records = new List<SalesPlanGoal__c>();
public List<SalesPlanActivity__c> records2 = new List<SalesPlanActivity__c>();

public List<SalesPlanGoal__c> getrecords() {    
    return records;
}
public List<SalesPlanActivity__c> getrecords2() {    
    return records2;
}
    /*Constructor*/
    public void SalesPlanPDFExtension() {  
    }/*End Constructor*/

    /*Constructor*/
    public SalesPlanPDFExtension(ApexPages.StandardController controller) {
        records = [SELECT Id,Name,Description__c,Sales_Plan__c,SMART_Goal__c,Key_Initiative__c
                        /*,
                        (SELECT Id,Name,Account__c,Contact__c,Key_Initiative__c,Sales_Plan__c,SMART_Goal__c,Status__c,Subject__c 
                        FROM BenchmarkActivities__r)
                        */
                   FROM SalesPlanGoal__c 
                   WHERE Sales_Plan__c=:ApexPages.currentPage().getParameters().get('id')
                   ORDER BY Name ASC
                  ];
        records2 = [SELECT Id,Name,Account__c,Contact__c,Due_Date__c,Key_Initiative__c,Sales_Plan__c,SMART_Goal__c,SMART_Goal_Objective__c,Status__c,Subject__c
                    FROM SalesPlanActivity__c 
                    WHERE SMART_Goal__c IN :records
                    //AND Key_Initiative__c = 'Exteriors'
                    ORDER BY Key_Initiative__c ASC
                   ]; 
    }
    /*End Constructor*/
   }

I can get the SalesPlanActivity__c to filter if I add a text literal match to the WHERE statement in records2 such as Key_Initiative__c = 'Exteriors' but I need it do be more dynamic than that because then the SalesPlanActivity__c records for a different initiative/goal type only shows the SalesPlanGoal__c Exteriors SalesPlanActivity__c records. I would ideally like something like WHERE SMART_Goal__c IN :records AND Key_Initiative__c = records.Name.

I figure I need something like records[i].name where i is the iteration of the row, but I have no idea how to accomplish this.

Any ideas on how to solve this problem?

Will you please help?

1
  • Your title seems a little misleading.
    – Adrian Larson
    Commented Sep 4, 2015 at 16:16

2 Answers 2

3

If you are looking to do a collection comparison, it is fairly straightforward. Use the IN operator as you do with your Id check, and build up a collection of the values you are looking for. It is a little unusual because you can check if an Id is in a collection of SObject, normally you need your collection to be of the same type as the field you are trying to filter.

Set<String> goalNames = new Set<String>();
Set<Id> goalIds = new Set<Id>();
// the second set is not necessary but would be if you wanted to pull a different Id
for (SalesPlanGoal__c goal : goals)
{
    goalNames.add(goal.Name);
    goalIds.add(goal.Id);
}

List<SalesPlanActivity__c> activities = [
    SELECT Id FROM SalesPlanActivity__c
    WHERE SMART_Goal__c IN :goalIds
    AND Key_Initiative__c IN :goalNames
];
0

I simplified the extension to...

public with sharing class SalesPlanPDFExtension{

public List<SalesPlanGoal__c> records = new List<SalesPlanGoal__c>();   

public List<SalesPlanGoal__c> getRecords() {    
    return records;
}

/*Constructor*/
public void SalesPlanPDFExtension() {  
}/*End Constructor*/

/*Constructor*/
public SalesPlanPDFExtension(ApexPages.StandardController controller) {
    records = [SELECT Id,Name,Description__c,Sales_Plan__c,SMART_Goal_Objective__c,Key_Initiative__c,
                    (SELECT Id,Name,Account__c,Contact__c,Key_Initiative__c,Sales_Plan__c,SMART_Goal__c,Status__c,Subject__c 
                    FROM BenchmarkActivities__r)                
               FROM SalesPlanGoal__c 
               WHERE Sales_Plan__c=:ApexPages.currentPage().getParameters().get('id')
               ORDER BY Name ASC
              ];
}   
/*End Constructor*/
}

I removed the second list and kept it simple with the records list. I modified the subquery to grab the activity records for each goal. I then changed the Visualforce to...

<!-- SMART GOAL INFORMATION -->
                <apex:repeat value="{!records}" var="init">
                    <tr>
                        <td>{!init.Name}</td>
                        <td>{!init.SMART_Goal_Objective__c}</td>
                        <td>
                            <!-- BENCHMARK ACTIVITIES -->
                            <apex:dataTable value="{!init.BenchmarkActivities__r}" var="ba" width="100%" columns="1" columnsWidth="100%" cellspacing="6">
                                <apex:column >
                                    <apex:outputText value="{!ba.Subject__c}" />
                                    <apex:outputPanel >
                                        <apex:outputText value=" " />
                                        <apex:outputField value="{!ba.Contact__c}" />
                                        <apex:outputText value=" " />
                                        <apex:outputField value="{!ba.Account__c}" />
                                    </apex:outputPanel>
                                    <br></br>
                                </apex:column>
                            </apex:dataTable>
                        </td>
                     </tr>
                </apex:repeat>

What is different about the Visualforce is that the apex:dataTable now references the subquery as the value using the initial query variable and the child relationship name. Declaring a variable for the value allows me to create a second apex:repeat where I can get the records for the subquery related to the query.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .