[SalesForce] How to display an SOQL query in apex Datatable

I'm having a difficult time finding what (I think) should be a relatively simple procedure. I would like to create a class controller that returns a SOQL query and then display the results in an apex datatable on a visualforce page. I would then like to display that visualforce page on a dashboard.

In my org I have Contacts and Opportunities related with contact roles. Opportunities have a custom child object that defines what program an opportunity belongs to. An opportunity can belong to multiple programs. This object is called 'targeting allocation'. I'm trying to display the [opportunity.account|contactrole.name|role|opportunity.principal_balance] in a datatable.

I know my SOQL query works:

[select opportunity.account.name, contact.name, role, opportunity.name, >>opportunity.current_principal_balance__c
from opportunitycontactrole where opportunityid in
(select allocation_opportunity__c from targeting_allocation__c
where program_type__c = 'Green Initiative')
and opportunity.current_principal_balance__c > 0 and isprimary = true]

I simply don't know how to put it into a class and then display. Its seemingly impossible to find good simple tutorials on classes and apex pages.

EDIT: Thank you crmprogdev and Jagular for your links. Through your links I found this page: What is wrapper class?? on success.salesforce.com.

I followed the example there and came up with this:

Wrapper Class

public class GreenInvestorWrapper {
    public class TableRow {
        public string AccountName   {get; set;}
        public string ContactName   {get; set;}
        public string ContactRole   {get; set;}
        public string oppName       {get; set;}
        public decimal oppValue     {get; set;}
    }
    public list  RowList {get; set;}
    public GreenInvestorWrapper () {
        RowList = new List();
        TableRow tr;
        For (OpportunityContactRole x: 
            [SELECT opportunity.account.name, contact.name, role, opportunity.name,opportunity.current_principal_balance__c FROM opportunitycontactrole WHERE opportunityid in (SELECT allocation_opportunity__c FROM targeting_allocation__c >WHERE program_type__c = 'Green Initiative') AND >opportunity.current_principal_balance__c > 0 AND isprimary = true])
            {
                tr = new TableRow();
                tr.AccountName = x.opportunity.account.name;
                tr.contactName = x.contact.name;
                tr.contactRole = x.role;
                tr.oppName = x.opportunity.name;
                tr.oppValue = x.opportunity.current_principal_balance__c;
                RowList.add(tr);
            }
    }
}

Apex Page

<apex:page controller="GreenInvestorWrapper">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!RowList}" var="row">
            <apex:column value="{!row.AccountName}"/>
            <apex:column value="{!row.ContactName}"/>
            <apex:column value="{!row.ContactRole}"/>
            <apex:column value="{!row.oppName}"/>
            <apex:column value="{!row.oppValue}"/>
        </apex:pageBlockTable>

    </apex:pageBlock>
</apex:page>

Sorry for formatting!

My next goal is to make this a list that is filterable by program_type__c, which is a field on the targeting allocation.

Thank you for all the help!

Best Answer

I recommend the following resources Visualforce Workbook and the Visualforce Developer’s Guide. You should find what you need in the above. Once you've written some code, come back, edit your message to insert your code and tell us exactly where you're having problems. If the resources above don't provide sufficient tutorials for you, try using Find SFInfo to search for others.

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] Test Class fail

Here's a commented rewrite of that trigger which does not execute the aggregateResult query within the loop context of the Trigger.new collection.

Additionally, there's some code in both of your triggers which don't actually do anything as written.

trigger fulfillmentcontactcreatedopp on Opportunity (before insert, before update) {

    Map<Id, Opportunity> oppsToCheckMap = new Map<Id, Opportunity>();

    // collect the ID values of the opportunities with a stage calc value of 5 or more
    // if it is an update, the stage name must have changed
    for (Opportunity opp : trigger.new) {

        if (opp.Stage_Calc__c >= 5 ) { 

            if (trigger.isInsert || 
                trigger.isUpdate && trigger.oldMap.get(opp.Id).StageName != opp.StageName) {
                    oppsToCheckMap.put(opp.Id, opp);
            }
        }
    }

    // get an aggregate count of contact roles per opportunity in the fulfillment role
    List<AggregateResult> result = [SELECT OpportunityId
                                        , count(Id)
                                    FROM OpportunityContactRole
                                    WHERE (Role = 'Fulfillment')
                                        AND OpportunityId IN :oppsToCheckMap.keySet()
                                    GROUP BY OpportunityId];

    // iterate the results and set the number of fulfillment contacts on the opportunity
    for (AggregateResult aggCount : result) {

        /* this block of code isn't actually doing anything */
        if (aggCount.get('expr0') == 0) {

            Id oppId = (Id)aggCount.get('OpportunityId');
            Opportunity oppo = oppsToCheckMap.get(oppId);

        }
        /* this block of code isn't actually doing anything */

        // get the opportunity from the incoming map and update the value of the fulfillment contacts field
        Id oppId = (Id)aggCount.get('OpportunityId');
        Opportunity opp = Trigger.newMap.get(oppId);

        if (opp != null) {
            Integer fulcount = Integer.valueOf(aggCount.get('expr0'));
            opp.Number_of_Fulfillment_Contacts__c = fulcount;
        }
    }
}

Update

Here is the updated trigger code for the NetSuite_Sales_Order__c object with the Opportunity update statements removed from the loops. I also reformatted it for my own sanity and changed some of the odd checks for Trigger.new and Trigger.old in the if statements.

trigger celigoUpdateSalesOrder on NetSuite_Sales_Order__c (before insert, before update, before delete) {

    List<Opportunity> opportunitiesToUpdate = new List<Opportunity>();

    if (Trigger.isInsert || Trigger.isUpdate) {
        List<Id> opIds = new List<Id>();
        for (NetSuite_Sales_Order__c order : Trigger.new) {
            if (order.Opportunity__c == null) {
                continue;
            }
            opIds.add(order.Opportunity__c);
        }

        List<Opportunity> ops = [SELECT Id
                                        , StageName
                                        , StageNameState__c
                                    FROM Opportunity
                                    WHERE Id IN :opIds];

        celigo_connector__c cs = celigo_connector__c.getInstance('Opportunity Closed Stage');
        String closedStage = (cs!=null) ? cs.value__c : 'Closed Won';

        for (Opportunity o : ops) {
            if (Trigger.isInsert) {
                if (o.StageNameState__c == null) {
                    o.StageNameState__c = o.StageName;
                    o.StageName = closedStage;
                }
            }
            o.Celigo_Sync_Helper__c = true;

            // add to the list for update later
            opportunitiesToUpdate.add(o);

        }
    }

    if (Trigger.isDelete) {
        List<Id> opIds = new List<Id>();
        for (NetSuite_Sales_Order__c order : Trigger.old) {
            if (order.Opportunity__c == null) {
                continue;
            }
            opIds.add(order.Opportunity__c);
        }

        List<Opportunity> ops = [SELECT Id
                                        , StageName
                                        , StageNameState__c
                                    FROM Opportunity
                                    WHERE Id IN :opIds];

        List<NetSuite_Sales_Order__c> tOrders = [SELECT Id
                                                        , Opportunity__c
                                                    FROM NetSuite_Sales_Order__c
                                                    WHERE Opportunity__c IN :opIds];

        Map<Id, List<NetSuite_Sales_Order__c>> m = new Map<Id, List<NetSuite_Sales_Order__c>>();

        for (NetSuite_Sales_Order__c order : tOrders) {
            if (m.get(order.Opportunity__c) == null) {
                m.put(order.Opportunity__c, new NetSuite_Sales_Order__c[]{ order });
            }
            else {
                /* this code block doesn't do anything */
                List<NetSuite_Sales_Order__c> temp = m.get(order.Opportunity__c);
                temp.add(order);
                /* this code block doesn't do anything */
            }   
        }

        Set<Id> keys = m.keySet();

        for (Id i : keys) {
            List<NetSuite_Sales_Order__c> temp = m.get(i);
            Opportunity op;
            for (Opportunity tempOp : ops) {
                if (tempOp.Id == i) {
                    op = tempOp;
                    break;
                }
            }
            if (temp.size() > 1) {
                continue;
            }

            if (op.StageNameState__c == null) {
                continue;
            }

            op.StageName = op.StageNameState__c;
            op.StageNameState__c = null;

            // add to the list for update later
            opportunitiesToUpdate.add(op);
        }
    }

    // if we've got opportunities to update, do it
    if (!opportunitiesToUpdate.isEmpty()) {
        update opportunitiesToUpdate;
    }
}

Best Answer

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

[SalesForce] Test Class fail

Update

Related Topic