4

I have created a table of 'sales data' in visualforce for display on accounts page, by creating custom fields within opportunities, and have created each 'opportunity' as a 'year' of sales data. the # column represents the amount of tickets sold for the month, the other the sales total for the month.

Is there any way, from professional edition salesforce (no custom controllers or classes) to control the order that these opportunities are showing up in the table?

I've included a screenshot to illustrate an example of each opportunity the way it shows up in the table. (2014, 2013, 2012) - this 'seems' to show up depending on the order the data was entered (the order that the opportunities were created).. but doesn't seem to be that way exclusively. Can anyone tell me the conclusive control for what tells it the order opportunities are to be displayed in the table, and if I can control that order some how? such as by the 'year' field in ascending order or some other means?

I'd also like to know, if using the standardcontroller, if I have any way of only including 'closed/won' opportunities in the table.

My Code:

 <apex:page standardController="Account" sidebar="false">
      <apex:form >
      <apex:pageBlock >

   <apex:pageBlockTable value="{!Account.Opportunities}" var="opp">

 <apex:column headerValue="Year" value="{!opp.Year__c}"/>
 <apex:column headerValue="Description" value="{!opp.Desc__c}"/>
 <apex:column headerValue="#" value="{!opp.Jantix__c}" style="background:lightgray;"/>
 <apex:column headerValue="Jan" value="{!opp.Jan__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Febtix__c}"/>
 <apex:column headerValue="Feb" value="{!opp.Feb__c}"/>
 <apex:column headerValue="#" value="{!opp.Martix__c}" style="background:lightgray;"/>
 <apex:column headerValue="Mar" value="{!opp.Mar__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Aprtix__c}"/>
 <apex:column headerValue="Apr" value="{!opp.Apr__c}"/>
 <apex:column headerValue="#" value="{!opp.Maytix__c}" style="background:lightgray;"/>
 <apex:column headerValue="May" value="{!opp.May__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Juntix__c}"/>
 <apex:column headerValue="Jun" value="{!opp.Jun__c}"/>
 <apex:column headerValue="#" value="{!opp.Jultix__c}" style="background:lightgray;"/>
 <apex:column headerValue="Jul" value="{!opp.Jul__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Augtix__c}"/>
 <apex:column headerValue="Aug" value="{!opp.Aug__c}"/>
 <apex:column headerValue="#" value="{!opp.Septix__c}" style="background:lightgray;"/>
 <apex:column headerValue="Sep" value="{!opp.Sep__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Octtix__c}"/>
 <apex:column headerValue="Oct" value="{!opp.Oct__c}"/>
 <apex:column headerValue="#" value="{!opp.Novtix__c}" style="background:lightgray;"/>
 <apex:column headerValue="Nov" value="{!opp.Nov__c}" style="background:lightgray;"/>
 <apex:column headerValue="#" value="{!opp.Dectix__c}"/>
 <apex:column headerValue="Dec" value="{!opp.Dec__c}"/>
 <apex:column headerValue="Total #" value="{!opp.TixSum__c}" style="background:lightgray;"/>
 <apex:column headerValue="Total $" value="{!opp.Sum__c}" style="background:lightgray;"/>

 </apex:pageBlockTable>
 </apex:pageBlock>
 </apex:form>
 </apex:page>

salesforce data


After much generous help, I was able to come up with 2 solutions that worked for my needs. One that was able to still utilize pageblocktable (easier styling) with PageBlockTableEnhancerADV , a free plugin available. That however, didn't 'automatically' sort by a particular column, so I went with the 2nd solution that utilized datatables:

<apex:page standardController="Account" showHeader="false">
    <link rel="stylesheet" type="text/css" src="https://cdn.datatables.net/1.10.0/css/jquery.dataTables.css"></link>
    <script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js">   </script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>

<table id="opps">
    <thead>
          <tr>
            <th>Year</th>
            <th>Description</th>
            <th>#</th>
            <th>Jan</th>
            <th>#</th>
            <th>Feb</th>
            <th>#</th>
            <th>Mar</th>
            <th>#</th>
            <th>Apr</th>
            <th>#</th>
            <th>May</th>
            <th>#</th>
            <th>Jun</th>
            <th>#</th>
            <th>Jul</th>
            <th>#</th>
            <th>Aug</th>
            <th>#</th>
            <th>Sep</th>
            <th>#</th>
            <th>Oct</th>
            <th>#</th>
            <th>Nov</th>
            <th>#</th>
            <th>Dec</th>
            <th>Total #</th>
            <th>Total $</th>
         </tr>
        </thead>
        <tbody>
            <apex:repeat value="{!Account.Opportunities}" var="o">
            <tr>
                    <td><apex:outputText value="{0,date,yyyy}"><apex:param value="{!o.CloseDate}" /></apex:outputText></td>
                    <td><apex:outputField value="{!o.Desc__c}"/></td>
                    <td><apex:outputField value="{!o.Jantix__c}"/></td>
                    <td><apex:outputField value="{!o.Jan__c}"/></td>
                    <td><apex:outputField value="{!o.Febtix__c}"/></td>
                    <td><apex:outputField value="{!o.Feb__c}"/></td>
                    <td><apex:outputField value="{!o.Martix__c}"/></td>
                    <td><apex:outputField value="{!o.Mar__c}"/></td>
                    <td><apex:outputField value="{!o.Aprtix__c}"/></td>
                    <td><apex:outputField value="{!o.Apr__c}"/></td>
                    <td><apex:outputField value="{!o.Maytix__c}"/></td>
                    <td><apex:outputField value="{!o.May__c}"/></td>
                    <td><apex:outputField value="{!o.Juntix__c}"/></td>
                    <td><apex:outputField value="{!o.Jun__c}"/></td>
                    <td><apex:outputField value="{!o.Jultix__c}"/></td>
                    <td><apex:outputField value="{!o.Jul__c}"/></td>
                    <td><apex:outputField value="{!o.Augtix__c}"/></td>
                    <td><apex:outputField value="{!o.Aug__c}"/></td>
                    <td><apex:outputField value="{!o.Septix__c}"/></td>
                    <td><apex:outputField value="{!o.Sep__c}"/></td>
                    <td><apex:outputField value="{!o.Octtix__c}"/></td>
                    <td><apex:outputField value="{!o.Oct__c}"/></td>
                    <td><apex:outputField value="{!o.Novtix__c}"/></td>
                    <td><apex:outputField value="{!o.Nov__c}"/></td>
                    <td><apex:outputField value="{!o.Dectix__c}"/></td>
                    <td><apex:outputField value="{!o.Dec__c}"/></td>
                    <td><apex:outputField value="{!o.TixSum__c}"/></td>
                    <td><apex:outputField value="{!o.Sum__c}"/></td>
                </tr>
            </apex:repeat>
        </tbody>
    </table>

    <script>
    // Enhance the plain table with jQuery DataTables plugin, sorted by year column descending.
    $(document).ready(function(){
        $('#opps').dataTable({
            'order': [0,'desc'],
            'paging' : false,
            'searching' : false
         });
    });
    </script>
  <apex:outputText style="font-style:italic" value="Legend: # = Amount of Tickets sold for that month"/>
</apex:page>
4

Is there any way, from professional edition salesforce (no custom controllers or classes) to control the order that these opportunities are showing up in the table?

If you cannot query for them in the order you want - tricky. Your best course of action might be to include a client-side formatting & sorting solution such as http://datatables.net

I'd also like to know, if using the standardcontroller, if I have any way of only including 'closed/won' opportunities in the table.

Convert your looping mechanism from <apex:pageBlockTable> to manually crafted HTML with <apex:repeat>. That way you'll be able to control displaying of rows. Of course it's not as effective as simply not SELECT'ing them but well... Something like this:

<table>
    <thead>
       <tr><th>header1</th><th>header2></th>...</tr>
    </thead>
    <tbody>
       <apex:repeat value="{!Account.Opportunities}" var="opp">
           <apex:outputPanel rendered="{!opp.Status = 'Closed/Won'}" layout="none">
               <tr><td> columns here </td></tr>
           </apex:outputPanel>
       </apex:repeat>
    </tbody>
</table>

P.S. Dump the <apex:form> tag and set <apex:page readonly="true"> so your collection will be able to deal with 10K opps instead of just 1K.


Edit to answer comments:

To style it closer to what <apex:pageBlockTable> produces you could have a look at the HTML generated by the pageBlockTable version and figure out what CSS classes you'd need to <table> and possibly also the <tr>s. I don't think there's a nice way to conditionally display row in pageBlockTable as what you "define" in it are columns rather than rows...

As for more conditions in the rendered - looks like you have to read up a bit about the syntax used in Visualforce and other places where formulas are used (formula fields, workflow rules & field updates, any other place where certain condition has to be met like say case assignment rules...)

In this particular example I'd experiment with

{!opp.StageName = 'Closed/Won' || opp.StageName = 'Closed/Lost'}

Alternative way to write it:

{!OR(opp.StageName = 'Closed/Won', opp.StageName = 'Closed/Lost')}

But since Stage is a special field with hidden related logic actually it might be better to show only closed Opportunities. There's a hidden checkbox field (you can't add it to page layout but you can use it in reports or see it in the object description):

rendered="{!opp.IsClosed}"

That way your VF page will continue to work even if somebody adds a new "closed" Stage.


Final edit... Man, you're really stretching it, I'd say you should edit your question to include what you got so far and what you're struggling with... or maybe raise a new question altogether...

This should be a nice example (it lists all Opps and not the ones associated with an Account but the principle is the same):

<apex:page standardController="Opportunity" recordSetVar="opportunities" showHeader="false">
    <link rel="stylesheet" type="text/css" src="https://cdn.datatables.net/1.10.0/css/jquery.dataTables.css"></link>
    <script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.0/js/jquery.dataTables.js"></script>

    <table id="opps">
        <thead>
            <tr>
                <th>{!$ObjectType.Opportunity.fields.Name.Label}</th>
                <th>{!$ObjectType.Opportunity.fields.StageName.Label}</th>
                <th>{!$ObjectType.Opportunity.fields.CloseDate.Label}</th>
                <th>Year</th>
            </tr>
        </thead>
        <tbody>
            <apex:repeat value="{!opportunities}" var="o">
                <tr>
                    <td><apex:outputField value="{!o.Name}"/></td>
                    <td><apex:outputField value="{!o.StageName}"/></td>
                    <td><apex:outputField value="{!o.CloseDate}"/></td>
                    <!-- Custom date format is useful to sort the table by year -->
                    <td><apex:outputText value="{0,date,yyyy}"><apex:param value="{!o.CloseDate}" /></apex:outputText></td>
                </tr>
            </apex:repeat>
        </tbody>
    </table>

    <script>
    // Enhance the plain table with jQuery DataTables plugin, sorty by 3rd column descending.
    $(document).ready(function(){
        $('#opps').dataTable({
            'order': [3,'desc'],
            'paging' : false,
            'searching' : false
         });
    });
    </script>
</apex:page>

enter image description here

| improve this answer | |
  • Thanks for this info eyescream! - A bit of tinkering and I figured it out. {!opp.Stagename = 'Closed Won'} gives the filtering with the code you've suggested. Now I have to learn more about styling with this as it doesn't match the styling nicely like pageblocktable did. Do you know if this will work with the datatable you mentioned trying to use? I don't know how I would set the datatable value / var in conjunction with the apex:repeat value / var . Thanks again for your help, this has fixed the 'filtering' issue at least. Now to fix sorting before attempting style – Ryan T Jun 11 '14 at 20:21
  • also, curious, am I able to access different things within an object without custom controllers/etc? (I am truly a noob with coding, i'm slowly learning) like with opp.stagename = , it's looking at the field name. what if I wanted it to filter by 'type' within Stagename instead (which are 'open' 'Closed/Won' & 'Closed/Lost')? I tried !opp.Stagename.type but that wasn't it. – Ryan T Jun 11 '14 at 20:27
  • Hi eyescream, thanks again for all of this helpful information. I have quite a bit of reading to go through to more thoroughly understand the syntax. Just to clarify, the 'Closed/Won' (& 'Closed/Lost') does not work for me, as it would appear that is the 'type' and instead the name of it is 'Closed Won' or 'Closed Lost'. That's what led me to ask about the 'type' rather than the name - Thank you for those differing approaches to this question. – Ryan T Jun 12 '14 at 13:07
  • Is there any way you could get me started on how to use apex:datatable with the <table> that i'm now using with the apex:repeat? I don't know what I'd set as the value and variable for the apex:datatable (as without the repeat section in there, i'd imagine i'd use <apex:datatable value="{!Account.Opportunities}" var="opp"> similar to how I had it with pageblocktable - but when using datatable, table, and repeat, how do i properly set those up to work together? I've tried to find some examples on this being used together, have had no luck. (the main thing here is to control sorting order) – Ryan T Jun 12 '14 at 14:29
  • No no, I didn't mean apex:datatable (which would be similar to how pageBlockTable works). I've meant the jQuery plugin from datatables.net. Set some CSS class or id to your <table> and enhance it with $('#myTableId').dataTable();. You know how to refer the javascript libraries in VF pages, right? – eyescream Jun 12 '14 at 15:27

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.