Display Salesforce Records into hierarchical View

August 15, 2014

Objective:- Display Salesforce records into hierarchy view.

Example:- Consider an example of a custom object "Product__c" which has a self lookup relationship to itself . Now we have to display all the products in hierarchy View.

Here is an example of fictitious Electronic Product Hierarchy .

Problem Statement:- There are two main challanges here.

1. Salesforce database does not support Hierarchical SOQL queries.

2. Lack of native visualforce component to display hierarchy data or adjacency list data .

Possible solutions:- There are two possible solutions from my understanding.
Here is a little explanation for both solutions.
1. Use of Google visulization API org chart component to display hierarchy :- Here we query database and prepare data nodes, then we simply supply these nodes to Google Visulization API org chart.Rest,this API is enough intelligent to display hierarchy view.

2. On demand hierarchy view: Display a small subset of data on the page using visualforce native or HTML components initially and query database (using visualforce remoting) once we want to traverse deep in the hierarchy.

Complexity:- I think it would be more complicated to adjust nodes on the page after every traversal . It needs more of javascript ,css ,HTML skills expertise.

Here is the visualforce Page code for same


<apex:page controller="ProductHierarchyController">
<head>
//Load Google Visulization API
<script type='text/javascript' src='https://www.google.com/jsapi'></script>
<script type='text/javascript'>
//Load Google orgChart Package
google.load('visualization', '1', {packages:['orgchart']});
google.setOnLoadCallback(drawChart);

function drawChart() {
//Javascript remoting call to get all the products.
Visualforce.remoting.Manager.invokeAction(
'{!$RemoteAction.ProductHierarchyController.loadProducts}',
function(result, event){
if (event.status){
// Initilize DataTable object
var data = new google.visualization.DataTable();
data.addColumn('string', 'Chocolate');
data.addColumn('string', 'Parent Chocolate');
data.addColumn('string', 'ToolTip');

// add rows from the remoting results
//alert(JSON.stringify(result));
for(var i =0; i<result.length;i++){
var r = result[i];
//Adding Data to DataTable object
//v:r.id ---->Id of a node
//f:r.name ---->Name displayed on the node
//r.Parent_Product_c__c ----> Id of parent node
//r.Name ------>Tooltip text.....displays text on node hover
data.addRow([{v:r.Id, f:r.Name},r.Parent_Product_c__c,r.Name]);
}

var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
chart.draw(data, {allowHtml:true});

}
},
{escape: true}
);

}
</script>
</head>
<body>
<div id='chart_div'></div>
</body>

</apex:page>

Here is the Controller for same

public with sharing class ProductHierarchyController{
//Load all the products
@RemoteAction
public static List<Product__c> loadProducts(){
return [select Id,Name,Parent_Product_c__r.Id from Product__c order by CreatedDate DESC];
}
}

Note:- I have not considered Force.com Governor limits in above code. Apply those in your use cases.

Recomended Links:
1. Google Visulization API OrgChart
2.Easy Visualforce Charts With Google Visulization API

Thanks
Ashish
Happy Independence Day!!!