How to Sort a Custom Product Table with Visualforce

We recently implemented some customization around Products in a customer’s Salesforce org. Everything was great except the customer wanted to easily sort the columns in the Product related list.  The standard behavior is not much fun: click the sort button to navigate to a sort page;  manually sort the Products;  click save or cancel to navigate back to the Opportunity detail page.  Even worse, you only see the Product names while sorting.  Our customer had some custom fields in the Product list. They wanted to just click the column headers on the custom fields to sort, so a custom Visualforce Pagelet was required.  I’ll highlight a couple of implementation details that may be useful to other developers.

Here is the standard Product List:

And here is the custom VisualForce Pagelet:

First, I’ll describe how we handled sorting.  The client will typically build a list of 10 to 20 Products on the Opportunity.  The default Sort button brings up the following:

This is too clunky, and only sorts by name.  I had several thoughts on how to approach this.  I could do the sorting in the controller, which would require a round trip to the server with each sort.  In the controller, we could store the list of products and sort in Apex, or use dynamic SOQL to query and sort. Another way would be to sort on the client.

 

It is easy to implement sorting with this library in an HTML table, as long as you use <thead> and <tbody> tags.  The sorting is initialized like this:

$j("#productTable").tablesorter();

Tablesorter has a number of built in parsers, so it automatically recognizes character strings, numbers, and currencies.  Note that if you use multiple currencies they will display like USD 20,000.00 or EUR …  You may need to implement a custom parser to sort when supporting multiple currencies.

I needed to implement several buttons to redirect navigation outside of the detail page. This was accomplished using a tip from stackoverflow.com:

The active ingredient is line 40 in the VF below.  By redirecting window.top.location.href we can navigate away from the detail page from inside the iframe.  The outputPanel is re-rendered by setting shouldRedirect=true and setting the redirectUrl as on lines 46 and 49 in the controller code below.

If there are no Products in the list, we show an “empty” message and hide the Edit All and Delete Selected buttons like this:

Here is the source of the VisualForce page, redacted to remove client specific information:

1   <apex:page standardController="Opportunity" extensions="ProductTable_Controller" showChat="false" >
2   <apex:includeScript value="{!URLFOR($Resource.jquery182)}"  />
3   <apex:includeScript value="{!URLFOR($Resource.jquerytablesorterjs)}" />
4  
5   <style>
6   .productTable { cellspacing: 0px; cellpadding: 0px; border-collapse: collapse; }
7   #productTable tr:hover { background: #e3f3ff; cursor:pointer; }
8   #productTable th {
9     background:#f2f3f3; font-size:90%; padding:5px 2px 4px 5px; border: 1px solid #e0e3e5;
10  }
11  #productTable td { border-top: 1px solid #e0e3e5; border-bottom: 1px solid #e0e3e5; }
12  .left { text-align:left; margin-left:4px; }
13  .right { text-align:right; margin-right:4px; }
14  .leftborder { border-left: 1px solid #e0e3e5; }
15  .rightborder { border-right: 1px solid #e0e3e5; }
16  a.editLink { text-decoration:none; }
17  a.editLink:hover { text-decoration:underline; }
18  .productLink { color:#000000; }
19  .productLink:hover { color:#015ba7; }
20  .cmdBtn { padding:5px; }
21
22  </style>
23  
24  <script language="JavaScript">
25  
26  $j = jQuery.noConflict();
27  $j(document).ready(function() {
28       $j("#productTable").tablesorter();
29   });
30   
31   </script>
32  
33   <apex:pageMessages />
34  
35   <apex:form >
36  
37   <apex:outputPanel id="redirectPanel" >
38     <apex:outputText rendered="{!shouldRedirect}">
39       <script type="text/javascript">
40         window.top.location.href = '{!redirectUrl}';
41       </script>
42     </apex:outputText>
43   </apex:outputPanel>
44  
45  <div>
46    <table style="width:100%;">
47      <tr>
48        <td style="width:30%;"></td>
49        <td>
50        <apex:commandButton style="margin:3px;" value="Edit All" action="{!editAll}" 
51                            rerender="redirectPanel" rendered="{! !noProducts}"/>
52        <apex:commandButton style="margin:3px;" value="Delete Selected" action="{!deleteSelected}" 
53                            rerender="redirectPanel" rendered="{! !noProducts}"/>
54        <apex:commandButton style="margin:3px;" value="Add Product" action="{!addProduct}" 
55                            rerender="redirectPanel" />
56        <apex:commandButton style="margin:3px;" value="Add Product Bundle"
57                            action="{!addProductBundle}" rerender="redirectPanel" />
58        </td>
59      </tr>
60    </table>
61  </div>
62  <apex:outputPanel id="noProductsPanel" rendered="{!noProducts}" >
63  <div style="width:100%;padding-top:10px;" >
64     <table style="background:#f8f8f8;width:100%;" border="0" cellpadding="0" cellspacing="0">
65       <tbody>
66         <tr>
67           <th style="border:1px solid #e0e3e5;padding:5px 5px 4px 5px;font-weight:normal;">
68             No records to display</th>
68         </tr>
69       </tbody>
70     </table>
71   </div>
72   </apex:outputPanel>
73
74   <apex:outputPanel id="productPanel" rendered="{! !noProducts}" >
75   <div id="tableDiv" style="height:215px;overflow-y: auto; overflow-x: none;">
76     <table id="productTable">
77       <thead>
78         <tr>
79           <th>Action</th>
80           <th>Product</th>
81           <th>Quantity</th>
82           <th>List Price</th>
83           <th>Sales Price</th>
84           <th>Subtotal</th>
85           <th>Discount</th>
86           <th>Total Price</th>
87         </tr>
88       </thead>
89       <tbody>
90       <apex:repeat value="{!products}" var="nextOLI">
91         <tr>
92         <td style="min-width:55px;" >
93           <apex:inputCheckbox value="{!nextOLI.checked}" />&nbsp;|&nbsp;
94           <apex:commandLink value="Edit" action="{!editOneProduct}" rerender="redirectPanel" 
95                             style="color:#015ba7;" styleClass="editLink" >
96             <apex:param name="editProductId" assignTo="{!editProductId}" value="{!nextOLI.oli.Id}" />
97           </apex:commandLink>
98         </td>
99         <td>
100          <apex:commandLink value="{!nextOLI.oli.PricebookEntry.Name}" action="{!gotoLink}" 
101                            rerender="redirectPanel" styleClass="productLink" >
102            <apex:param name="editProductId" assignTo="{!editProductId}" 
103                        value="{!nextOLI.oli.PricebookEntry.Product2Id}" />
104          </apex:commandLink>
105        </td>
106        <td style="min-width:56px;" ><apex:outputField value="{!nextOLI.oli.Quantity}" /></td>
107        <td style="min-width:92px;" ><apex:outputField value="{!nextOLI.oli.ListPrice}" /></td>
108        <td style="min-width:92px;" ><apex:outputField value="{!nextOLI.oli.UnitPrice}" /></td>
109        <td style="min-width:92px;" ><apex:outputField value="{!nextOLI.oli.Subtotal}" /></td>
110        <td style="min-width:58px;" ><apex:outputField value="{!nextOLI.oli.Discount}" /></td>
111        <td style="min-width:92px;"><apex:outputField value="{!nextOLI.oli.TotalPrice}" /></td>
112        </tr>
113      </apex:repeat>
114      </tbody>
115    </table>
116  </div>
117  </apex:outputPanel>
118  </apex:form>
119  </apex:page>

 

1   /*
2   ** Class:  ProductTable_Controller
3   ** Created by OpFocus on 12/06/2013
4   ** Description: 
5   **    This class implements the controller extension for the ProductTable VF component.
6   */	
7   public with sharing class ProductTable_Controller {
8
9   public List  olis { public get; public set; }
10 
11  private ApexPages.StandardController ctl;
12  public ID oppId { public get; public set; }
13  public String redirectUrl {public get; private set;}
14  public Boolean shouldRedirect {public get; private set;}
15  public List  products { public get; public set; }
16  public Boolean noProducts {public get; private set;}
17  public Integer productCount {public get; private set;}
18
19  // Constructor
20  public ProductTable_Controller(ApexPages.StandardController ctl) {
21    this.ctl = ctl;
22    oppId = ctl.getId();
23  
24    olis = 
25      [select 
26         UnitPrice, TotalPrice, SystemModstamp, Subtotal, SortOrder, ServiceDate, Quantity, 
27         PricebookEntryId, OpportunityId, ListPrice, Id, Discount, 
28         Description, PricebookEntry.Name, PricebookEntry.Product2Id
29       from OpportunityLineItem
30       where OpportunityId = :oppId];
31
32    products = new List  ();
33    for (OpportunityLineItem nextOLI : olis) {
34      products.add(new ProductHelper(nextOLI));
35    }
36    noProducts = products.size() == 0;
37    productCount = products.size();
38
39    shouldRedirect = false;
40  }
41
42  public String editProductId {public get; public set;}
43
44  // redirect to a product detail page
45  public PageReference gotoLink() {
46    shouldRedirect = true;
47    PageReference pageRef = new PageReference('/'+editProductId);
48    pageRef.setRedirect(true);
49    redirectUrl = pageRef.getUrl();
50    return null;
51  }	
52
53  // redirect to a product edit page
54  public PageReference editOneProduct() {
55    shouldRedirect = true;
56    PageReference pageRef = new PageReference('/'+editProductId+'/e?retURL=%2F'+oppId);
57    pageRef.setRedirect(true);
58    redirectUrl = pageRef.getUrl();
59    return null;
60  }
61
62  // redirect to the edit all products page
63  public PageReference editAll() {
64    shouldRedirect = true;
65    PageReference pageRef = 
66      new PageReference('/oppitm/multilineitem.jsp?oppId='+oppId+'&retURL=%2F'+oppId);
67    pageRef.setRedirect(true);
68    redirectUrl = pageRef.getUrl();
69    return null;
70  }	
71
72  // delete any product with the associated checkbox checked
73  public PageReference deleteSelected() {
74    System.Savepoint sp = Database.setSavepoint();
75    try {
76      List  deleteOLIs = new List  ();
77      for (ProductHelper nextHelper : products) {
78        if (nextHelper.checked) deleteOLIs.add(new OpportunityLineItem(Id=nextHelper.oli.Id));
79      }
80      if (deleteOLIs.size() > 0) {
81        delete deleteOLIs;
82      }
83    }
84    catch (Exception e) {
85      Database.rollback(sp);
86      ApexPages.addmessage(
87        new ApexPages.Message(ApexPages.Severity.ERROR, 
88                              'Unable to delete products: '+e.getMessage(), e.getMessage()));			
89      return null;
90    }
91
92    shouldRedirect = true;
93    PageReference pageRef = new PageReference('/'+oppId);
94    pageRef.setRedirect(true);
95    redirectUrl = pageRef.getUrl();
96    return null;
97  }
98
99  public PageReference addProduct() {
100   // do some stuff…
101   return null;
102 }
103
104 public PageReference addProductBundle() {
105   // do some stuff…
106   return null;
107 }
108
109 public class ProductHelper {
110   public Boolean checked {public get; public set;}
111   public OpportunityLineItem oli {public get; public set;}
112
113   public ProductHelper (OpportunityLineItem newOLI) {
114     oli = newOLI;
115     checked = false;
116   }
117 }
118
119 }