Nov 23, 2012

Page Block Table With Dynamic Columns In Salesforce

This is something extensively used, but every time I need to look for some easy code.

So here is the solution (apologies as I've not covered the negative cases in code, and I hope you all are smart enough to take care of that). Requirement is to build the dynamic page block table with dynamic columns, but this is not it. It should be so dynamic that user can select the object and it's fields by himself and nothing should be hard-coded. So first of all I've provided a picklist with all objects (not all objects... why? Take a look at it Please Remove The System Objects From My sObject List)

Visualforce Page Code:

<apex:page controller="DynamicTableController">
<apex:pageBlock >
    <apex:form >
        <apex:actionFunction name="ObjectFileds" action="{!ObjectFields}"/>
        
        <apex:commandButton  value="Show Table" action="{!ShowTable}"/>
        
        <apex:pageBlockSection >
            <apex:pageBlockSectionItem >
                <apex:outputLabel value="Select Object"/>
                <apex:selectList multiselect="false" size="1" value="{!SelectedObject}" onchange="ObjectFileds();">
                    <apex:selectOption itemLabel="--None--" itemValue="--None--"/>
                    <apex:selectoptions value="{!supportedObject}" />
                </apex:selectlist>
            </apex:pageBlockSectionItem>
            
            <apex:pageBlockSectionItem >
                <apex:outputLabel value="Select Field"/>
                <apex:selectList multiselect="true" size="5" value="{!SelectedFields}">
                    <apex:selectOption itemLabel="--None--" itemValue="--None--"/>
                    <apex:selectoptions value="{!fieldLableAPI}" />
                </apex:selectlist>
            </apex:pageBlockSectionItem>
            
            <apex:pageBlockTable rendered="{!IF(ObjectList.size > 0 , true , false)}" value="{!ObjectList}" var="rec">
                <apex:column value="{!rec.Id}" rendered="{!IF(SelectedFields.size == 0 , true, false)}"/>
                <apex:repeat value="{!SelectedFields}" var="FieldLable">
                    <apex:column value="{!rec[FieldLable]}" rendered="{!IF(FieldLable != '--None--' , true, false)}"/>
                </apex:repeat>
            </apex:pageBlockTable>
            
            <apex:outputPanel rendered="{!IF(ObjectList.size < 1 , true , false)}">
                <apex:pageMessage severity="ERROR" summary="No records to display"/>
            </apex:outputPanel>
            
        </apex:pageBlockSection>
        
    </apex:form>
</apex:pageBlock>
</apex:page>


Apex Code (Class):

public class DynamicTableController
{
    //List displayed on UI
    public List<selectoption> supportedObject {get; set;}
    
    //Selected Object
    public String SelectedObject {get; set;}
    
    //Global describe
    Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
    Set<String> objectKeys = gd.keySet();
    
    //Field Select List
    public List<SelectOption> fieldLableAPI {get; set;}
    
    //Selected fields to be displayed in table
    public List<String> SelectedFields {get; set;}
    
    //List to maintain dynamic query result
    public List<sObject> ObjectList {get; set;}
    
    
    //Constructor
    public DynamicTableController()
    {
        //Initialize
        supportedObject = new List<selectoption>() ;
        SelectedObject = '' ;
        fieldLableAPI = new List<SelectOption>() ;
        SelectedFields = new List<String>() ;
        ObjectList = new List<sObject>() ;
        
        //Get only reference to objects
        for(Schema.SObjectType item : ProcessInstance.TargetObjectId.getDescribe().getReferenceTo())
        {
            //Excluding custom setting objects
            if(!item.getDescribe().CustomSetting)
            {
                //Adding to list
                supportedObject.add(new SelectOption(item.getDescribe().getLocalName().toLowerCase() , item.getDescribe().getLabel() ));
            }
        }
        
    }
    
    //Get fields of selected object
    public void ObjectFields()
    {
        if(SelectedObject != '--None--')
        {
            //Creating sObject for dynamic selected object
            Schema.SObjectType systemObjectType = gd.get(SelectedObject);
            //Fetching field results
            Schema.DescribeSObjectResult r = systemObjectType.getDescribe();
                
            Map<String, Schema.SObjectField> M = r.fields.getMap();
            //Creating picklist of fields
            for(Schema.SObjectField fieldAPI : M.values())
            {
                fieldLableAPI.add(new SelectOption(fieldAPI.getDescribe().getName() , fieldAPI.getDescribe().getLabel())) ;
            }
        }
    }
    
    public void ShowTable()
    {
        //Creating dynamic query with selected field
        String myQuery = 'Select Id ' ;
        
        for(String field : SelectedFields)
        {
            if(field.toLowerCase() != 'id' && field.toLowerCase() != '--none--')
            myQuery += ','+ field + ' ' ;
        }
        
        //Limit is 100 for now you can change it according to need
        myQuery += ' from ' + SelectedObject + ' LIMIT 100' ;
        
        //Executing the query and fetching results
        ObjectList = Database.query(myQuery) ;
    }
}

Once user selects any object from the first picklist, filed picklist will be populated with all the field labels related to that object. Note, it's a multi-select picklist so user can select multiple fields. Once fields are selected and "Show Table" is clicked, page block table will display the field values (selected in field picklist) of all records (limit is 100 right now, you can change it according to your need).

There are some extra checks that needs to be implemented in code like "isQueryable" etc..

17 comments:

  1. Nice - I like the use of rendered="{!IF(ObjectList.size > 0 , true , false)}"

    ReplyDelete
  2. While selecting the object for first time it is displaying the corresponding fields, but the if a user changes the object the corresponding object field is not displayed.

    ReplyDelete
  3. What a concept Ankit.... Very Dynamic...

    ReplyDelete
  4. Nice.

    Should have
    fieldLableAPI.clear();
    at line 51, so the list of fields will changed after changing the selected object.

    ReplyDelete
  5. Add this line below line no :48
    fieldLableAPI = new List() ;

    ReplyDelete
  6. Thanks for your post, Information related to salesforce help are really helpful to resolve our queries.

    ReplyDelete
  7. Hi
    I want to store the selected data permanently but after refresh it going to original stage how to overcome this .please guide me

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi ForceGuru,

    Thankyou for the code and there is an error in apex code at line 32,
    If I kept in comments it runs and to output just a design output...

    a link of the screenshot
    https://drive.google.com/file/d/0B7KNbYxZdMfMOE4yQTQtb0otTms/edit?usp=sharing

    ReplyDelete
  10. HI getting following error for custom objects
    Visualforce Error
    Help for this Page

    System.NullPointerException: Attempt to de-reference a null object
    Error is in expression '{!ObjectFields}' in page yeruva_sudha:pbtabledyncolumns

    Class.yeruva_sudha.DynamicTableController.ObjectFields: line 56, column 1

    ReplyDelete
  11. hiii for the program mentioned, am getting some error like " Missing required attribute value in in atch at line 25 column 13"

    ReplyDelete
  12. I am getting insufficient privileges error where as i am the admin and have access to all objects and records. also i have used with Public. Still i am getting this error.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Unsupported type common.api.soap.wsdl.Address encountered.

    ReplyDelete