I have a list of data populated via SOQL, in this list I have a column FinancialYear__c. I'd like to generate a number of PageBlockTables based on groupings of the FinancialYear__c value.

e.g. PBT 1 = list of 13/14 data
     PBT 2 = list of 14/15 data etc etc

Im thinking a List of Lists and apex:repeat but not sure if this is the right approach/how to implement?

Here is the query:

List<Site__c> SupermarketList = [SELECT StoreNo__c
                                    , Probability__c
                                    , Name
                                    , JS_Region__c
                                    , JS_Zone__c
                                    , Address__c
                                    , Postcode__c
                                    , (SELECT MeetingDate__c
                                            , RecordType.name
                                            , Total_Sales_Area_Uplift__c
                                            , TU_Sales_Area_Uplift__c
                                            , TUSalesAreaPre__c
                                            , TUSalesAreaPost__c
                                            , SalesAreaPreExcCO__c
                                            , SalesAreaPostExcCO__c
                                            , NonFoodSalesAreaUplift__c
                                            , NonFoodSalesAreaPre__c
                                            , NonFoodSalesAreaPost__c
                                            , GMSalesAreaUplift__c
                                            , GMSalesAreaPre__c
                                            , GMSalesAreaPost__c
                                            , FoodSalesAreaUplift__c
                                            , FoodSalesAreaPre__c
                                            , FoodSalesAreaPost__c 
                                        FROM Financial__r 
                                            WHERE MeetingDate__c != null AND Recordtype.Name IN('CCM','CCM Update','IB')
                                            ORDER BY MeetingDate__c DESC
                                            LIMIT 1)
                                    , (SELECT Launch_date__c
                                            , Start_On_Site__c
                                            , Status__c
                                            , Store__c
                                            , ProjectName__c
                                            , Investment_Type__c
                                            , ProjectManager__c
                                            , MainContractor__c
                                            , RetailHandover__c
                                            , Closure__c
                                            , Launch_FY__c
                                            , Launch_Period__c
                                            , Launch_Quarter__c 
                                        FROM Investment_Programs__r 
                                            WHERE Status__c IN ('Live','Feasibility','Completed') AND RecordType.Name ='New Store' )
                                    FROM Site__c
                                    WHERE Recordtype.name = 'Supermarket'
                                        AND Probability__c IN('Open','Certain','Possible','Probable')
                                        AND Id IN (SELECT Site__c 
                                                    FROM Financial__c 
                                                    WHERE MeetingDate__c != null AND Recordtype.Name IN ('CCM','CCM Update','IB'))
                                        AND Id IN (SELECT Site__c
                                                    FROM InvestmentProgram__c 
                                                    WHERE Status__c IN ('Live','Feasibility','Completed') AND RecordType.Name ='New Store' ) 
                                    ORDER BY Launch_Date__c ASC 
                                    LIMIT 999];

I want a new PageBlockTable for every change in Launch_FY__c, I am assuming list of list is the way to just not sure how to populate a list of lists from a query like this?

  • Paul can you post the code you have, someone here would be able to help you better if they see through what you have implemented :)
    – Rao
    Sep 24 '13 at 16:28
  • You didn't post how you want to group column FinancialYear__c and why you need multiple PageBlockTables...
    – Ashwani
    Sep 24 '13 at 16:31
  • i need multiple tables as i want to use the footer to display summary info for each Financial Year. So a new table at every change in FinancialYear.
    – paul
    Sep 24 '13 at 16:42
  • A query with two subqueries doesn't automatically, in my mind, lend itself to being a sequence of flat lists. Can you mock up what you're trying to do? Sep 24 '13 at 18:19
  • should have mentioned the subqueries only ever return one record, will put Limit 1 in anyway.
    – paul
    Sep 25 '13 at 8:26

I think the easiest way to do this would be to loop through the results in your controller and populate a map of lists (note I'm using a String for FY in this example but it should show the gist):

public Map<String, List<Object>> mapFYToList {get; private set;}

// in init or wherever:
mapFYToList = new Map<String, List<Object>>();

for(Result r : [query])
  if(mapFYToList.get(r.FY) == null)
    mapFYToList.put(r.FY, new List<Object>{r]);

You'll probably want to get these years in order as well (and you could cache this result):

public List<String> GetFYKeys()
  List<String> keys = new List<String>();
  return keys;

Then in the page you can just loop over the map contents, doing a page block table for each list:

<apex:repeat value="{!keys}" var="fy">
  <apex:pageBlockDataTable value="{!mapFYToList[fy]}" var="obj">
    <apex:column value="{!obj.Name}"/>
    <!-- etc -->

I've written all this straight in the editor so it could have a few syntax errors but hopefully the idea makes sense!

  • exactly what i needed
    – paul
    Sep 25 '13 at 8:25
  • 1
    However run into a little issue displaying some of the data, previously had been using: <apex:column value="{!obj.Investment_Programs__r[0].Status__c}"/> But now i get the error: Incorrect parameter type for subscript. Expected Text, received Number Works fine for for data thats not coming from a subquery, any ideas?
    – paul
    Sep 25 '13 at 8:47
  • Hi Paul, had to convert that to a comment since it's not an answer. Not sure why that's causing an issue... might be worth throwing obj.InvestmentPrograms__r[0].Status__c into a map using the obj.Id as the key, then you can just reference that map: {!statusMap[obj.Id]}.
    – Matt Lacey
    Sep 25 '13 at 23:44

You can do something similar on your page to:

<apex:repeat value="{!myListOfList}" var="aList">
  <apex:pageBlockTable value="{!aList}" var="anItem}">
      <apex:facet name="header">Name</apex:facet>
      <apex:outputText value="{!anItem.Name}" />

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.