Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ed055Ed055 

Help with Custom object and relationships

I have created a custom object Allocation with fields Product name,Account(lookup),contact(master detail),balances(currency), cost bases(currency). I have added Allocation related list to contact page. This works fine.

Users would like to see related list on account page which totals all the balances,cost basis  by product for that account  for eg ..
PRODUT NAME  |      BALANCE |   COST BASIS

What additional object/relationship I have to create to achive this ?  Thanks for help/advise.



 
Best Answer chosen by Ed055
William LópezWilliam López
Hello Ed055,

Regarding  #1

You need to put a chart, acutally that feature its to show charts in a pagelayout, and you will be using that as a workarround and the only thing they are going to see in the pagelayout will be the chart, they have to click on it to see full data.

In the report summarize by account, all of then are going to be there, but when the report its added to the layout salesforce will filter that only for that account, and if they click over the chart the report will open filtered by the proper account.

More info here:
http://training.handsonconnect.org/m/customizing/l/145915-adding-charts-to-page-layouts

Regarding  #2

Yes the Iframe can be a good idea. But yes you will have to start playing with JS hacks, see for where the data on the report starts and try to show that info. But I would be carefull with that as Salesforce might change the names and tags on the report page without notice as we are not suppose to use it that way.

I think it would be easier to create an small controller and use a Databble to show the results.

Controller should be somthing like this (I cannot fully test as I dont have same structure, but it should be close to this)
  
public class myControllerExtension {

    private final Account acct;
    
    public myControllerExtension(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();
    }

    public List<SumaryDataWrapper> getSumarizedData {
        
         List<SumaryDataWrapper> dataWrapper = new List<SumaryDataWrapper>();
        
              public List<AggregateResult> sumarizedAllocations = 
                    [SELECT 
                         SUM(balances) balances_total,
                         SUM(bases) bases_total,
                         Product_name__c
                         FROM Allocation__c
                         WHERE Account__c.Id = acct.Id
                         GROUP BY  Product_name__c];
             
            for (AggregateResult res : summarizedData) {
                SumaryDataWrapper dataWrapper = new SumaryDataWrapper();
                dataWrapper.PRODUT_NAME =    Integer.valueOf(res.get('Product_name__c')),
                dataWrapper.BALANCE =    Integer.valueOf(res.get('balances_total')),
                dataWrapper.COST_BASIS =    Integer.valueOf(res.get('bases_total')),
                    
                 dataWrapper.add(dataWrapper);
            } 
    }
	
	
	public class SumaryDataWrapper{		
		public String PRODUT_NAME {get;set}
		public Double BALANCE {get;set}
		public Double COST_BASIS {get;set}		
	}
}
Page should be something like this
  
<apex:page standardController="Accout"  extensions="NewClassController">
<apex:pageBlock title="Sumarize Data">
       <apex:form id="theForm">
            
               <apex:dataTable value="{!SumarizedData}" var="sd" id="theTable" rowClasses="odd,even" styleClass="tableClass">
                     <apex:facet name="header">table header</apex:facet>
                   
                       <apex:column>            
                            <apex:facet name="header">Product</apex:facet>                 
                        	<apex:outputText value="{!sd.PRODUT_NAME}"/>         
                    </apex:column>
                   
                     <apex:column>            
                            <apex:facet name="header">BALANCE</apex:facet>                 
                        	<apex:outputText value="{!sd.BALANCE}"/>         
                    </apex:column>
                   
                     <apex:column>            
                            <apex:facet name="header">COST BASIS</apex:facet>                 
                        	<apex:outputText value="{!sd.COST_BASIS}"/>         
                    </apex:column>
                   
          		 </apex:dataTable>
                 
        </apex:form> 
    </apex:pageBlock>     
    
</apex:page>

Let me know how its goes,

Bill
 

All Answers

Mathew Andresen 5Mathew Andresen 5
I believe for regular summary rollup fields to work you have to have a master/detail relationship.  You could get around that with a custom controller and visualforce page, of course.
William LópezWilliam López
Hello Ed055,

I can see 2 options:

1) Report option

Maybe you can create a report, to sumarize that data. Then add the report in the layout.

+ Creae a Summary report to sumarize Products Blaanace and cost by account
+ Add a chart to the report. (This its important)
+ Save the report where users can have acess.
+ Update the account layout to add the chart.

User-added image

This way you dont have a related list with the totals but you can have a nice chart like this, and when they click the chart they will the full report with the chart + all the data, they can even export to excel.

User-added image

2) Code Option

In this option you need to crete a visual force page where standard controller its the account and it has an controller extention with a class that have to calculate the totals and display them in a table.

It should be a simple class, just to select all Allocations related to this account  and with a couple of agretations you will have the totals
http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_agg_functions.htm

Then just display the data in a Apex DataTable
http://www.salesforce.com/docs/developer/pages/Content/pages_compref_dataTable.htm

This option might be longer, just little bit more coding, But you will get the related list exacly as you want it. 

Let me know how it goes.

Bill,
Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
  
Ed055Ed055
Bill - Great suggestions . Thank you.
Regarding  #1  .. Is it possible to add a report without a chart to account layout page?  On the Account page,  I need to pull data only for contact Allocations that belong to that account. How am i going to pass account ID to the report filter?

Regarding #2  If I add VF page on layout it puts it in an iframe. If there are may rows it does not show the scroll bar. May be there is some kind of JS hack to display it correctly.

Thanks again.



 
William LópezWilliam López
Hello Ed055,

Regarding  #1

You need to put a chart, acutally that feature its to show charts in a pagelayout, and you will be using that as a workarround and the only thing they are going to see in the pagelayout will be the chart, they have to click on it to see full data.

In the report summarize by account, all of then are going to be there, but when the report its added to the layout salesforce will filter that only for that account, and if they click over the chart the report will open filtered by the proper account.

More info here:
http://training.handsonconnect.org/m/customizing/l/145915-adding-charts-to-page-layouts

Regarding  #2

Yes the Iframe can be a good idea. But yes you will have to start playing with JS hacks, see for where the data on the report starts and try to show that info. But I would be carefull with that as Salesforce might change the names and tags on the report page without notice as we are not suppose to use it that way.

I think it would be easier to create an small controller and use a Databble to show the results.

Controller should be somthing like this (I cannot fully test as I dont have same structure, but it should be close to this)
  
public class myControllerExtension {

    private final Account acct;
    
    public myControllerExtension(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();
    }

    public List<SumaryDataWrapper> getSumarizedData {
        
         List<SumaryDataWrapper> dataWrapper = new List<SumaryDataWrapper>();
        
              public List<AggregateResult> sumarizedAllocations = 
                    [SELECT 
                         SUM(balances) balances_total,
                         SUM(bases) bases_total,
                         Product_name__c
                         FROM Allocation__c
                         WHERE Account__c.Id = acct.Id
                         GROUP BY  Product_name__c];
             
            for (AggregateResult res : summarizedData) {
                SumaryDataWrapper dataWrapper = new SumaryDataWrapper();
                dataWrapper.PRODUT_NAME =    Integer.valueOf(res.get('Product_name__c')),
                dataWrapper.BALANCE =    Integer.valueOf(res.get('balances_total')),
                dataWrapper.COST_BASIS =    Integer.valueOf(res.get('bases_total')),
                    
                 dataWrapper.add(dataWrapper);
            } 
    }
	
	
	public class SumaryDataWrapper{		
		public String PRODUT_NAME {get;set}
		public Double BALANCE {get;set}
		public Double COST_BASIS {get;set}		
	}
}
Page should be something like this
  
<apex:page standardController="Accout"  extensions="NewClassController">
<apex:pageBlock title="Sumarize Data">
       <apex:form id="theForm">
            
               <apex:dataTable value="{!SumarizedData}" var="sd" id="theTable" rowClasses="odd,even" styleClass="tableClass">
                     <apex:facet name="header">table header</apex:facet>
                   
                       <apex:column>            
                            <apex:facet name="header">Product</apex:facet>                 
                        	<apex:outputText value="{!sd.PRODUT_NAME}"/>         
                    </apex:column>
                   
                     <apex:column>            
                            <apex:facet name="header">BALANCE</apex:facet>                 
                        	<apex:outputText value="{!sd.BALANCE}"/>         
                    </apex:column>
                   
                     <apex:column>            
                            <apex:facet name="header">COST BASIS</apex:facet>                 
                        	<apex:outputText value="{!sd.COST_BASIS}"/>         
                    </apex:column>
                   
          		 </apex:dataTable>
                 
        </apex:form> 
    </apex:pageBlock>     
    
</apex:page>

Let me know how its goes,

Bill
 
This was selected as the best answer
Ed055Ed055
Bill - Thank you for the code sample and the help. Your #2 solutions works great. The only thing I have to figure out is that if the list grows, the VF section does not show a scoll bar.
William LópezWilliam López
Hello Ed055,

Gald to hear it help you.

To add the scroll just edit the layout like this:

User-added image

Regards,

Bill