+ Start a Discussion
JCoppedgeJCoppedge 

easiest way total data from related list?

I plugged in the Inline Account Hierarchy and it works great!  Problem is that I need to get totals from 3 numeric fields on all of the child accounts.  How difficult would it be make the changes to the code to generate totals as well?  I am an Apex newbie...

 

OR, would it be possible to create a custom button that called a report that filtered based upon account ID and included these totals in the report?

How has everyone else resolved this need?

werewolfwerewolf
Have you tried making a Rollup Summary Field?
JCoppedgeJCoppedge
Roll up Summaries can only be created for a master-detail relationship, while the parent account field is a lookup relationship.  When I go to create a roll up field on the account the only object I have listed is opportunity... am I missing something?
werewolfwerewolf
Well, you could make a report like that using a custom summary field, sort of like this.
Pat McQueenPat McQueen

One way to do this is to use an inline s-control that runs on page load and calculates the total.    (Of course you should use a VisualForce page and not an s-control) This sample summarizies won opportunities of that specified account using the "IsClosed" field.   This is just a sample but to make it work, Add the Total Won Opportunties Custom field to the Account page layout where you want this field to be displayed. Be sure to make the field read only both in the page layout and in the custom field definition. Add the s control to the Account page layout where you want this field to be displayed. Be sure to make the width and height 0 so it will not affect the layout visually.

 

There are other ways to do this as well.  You can have the total be updated everytime a child is updated via a trigger (or maybe workflow)

 

Good Luck

 

 

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <script src="/soap/ajax/10.0/connection.js"></script> <link rel="stylesheet" type="text/css" href="/sCSS/Theme2/en/dStandard.css" /> <script type="text/javascript"> var id = "{!Account.Id}"; var query = "SELECT Id, Amount from Opportunity where IsWon = TRUE AND AccountID = '"+ id +"'"; try{ var FundItemsResult = sforce.connection.query(query); var FundItemsNum = FundItemsResult.getArray("size"); var FundItemsRecords = FundItemsResult.getArray("records"); var Total = 0; for (i=0; i<FundItemsNum; i++) { amt = Number(FundItemsRecords[i].Amount); Total = Total + amt; } Total = Number(Total); var dbvalue = "{!Account.Total_Won_Opportunities__c}"; dbvalue = convertIt(dbvalue); if(Total != dbvalue) { var FundToUpdate = new sforce.SObject("Account"); FundToUpdate.set("Id", id); FundToUpdate.set("Total_Won_Opportunities__c", Total); var Result = sforce.connection.update([FundToUpdate]); if(Result[0].getBoolean("success")) { function redirect() { parent.frames.location.replace("/{!Account.Id}"); } redirect(); } else { alert("Error"); } } } catch (e) { alert("Error searching for Opportunities: " + e); } function thousands( expr, separator){ var i, j = 0, s = expr, formatted = ""; if( s.indexOf('.') > -1 || s.indexOf(',') > -1){ return s;} for( i = s.length; i >= 0; i--){ j++; if (j % 3 == 0 && i > 0) formatted = separator + s.substring(i-1, i) + formatted; else formatted = s.substring(i-1, i) + formatted; } return formatted; } //var _a = "$4,003,543.34"; function convertIt(_a) { var _c = _a; var _x = _a.indexOf("$"); if (_x == -1 ) _x = _a.indexOf(","); if (_x != -1) { var _p1 = _a.substr(0,_x); var _p2 = _a.substr(_x+1,_a.length); _c = convertIt(_p1+_p2); } return (_c); } </script> </head> <body> </body> </html>

 

 

 

 

JCoppedgeJCoppedge

I found the easiest way to do this was to create a custom button that passes the parent account as a filter variable to a report.  It is actually pretty easy to do using a custom button.  For some reason i couldn't get it to work with a formula using HYPERLINK() b/c the fuction strips the {!} out of the variable and doesn't pass any information through (so you end up getting parent account = account.parent).

 

 

There is a good tutorial here and forum reference here.  Thanks for the help everyone!

bbradybbrady

I solved this problem by creating a trigger and a class (also using the Inline Account Hierarchy as luck would have it, but I modified the hierarchy to work with custom objects.) In the code below, "Area__c" is the parent and  "Component__c" is the child. Componet__c is synonymous with "Asset" (not the standard object). Since "Asset" is already the name of a Standard Object I had to find another name for it. I mention it only so that you don't get confused when you read the comments or code where I may use the term Component or Asset interchangably.

 

 

 

The trigger is on the child (i.e. the related list). The trigger fires everytime a child (one or more) is created/inserted/updated/deleted/undeleted. The class finds the parent in the hierarchy, figures out who the siblings are, calculates average vaues for some numeric fields across all siblings and then writes those averages to fields in the parent.


Some of the issues I've run into that this code doesn't address:

 

Doing mass-deletes or updates of the children (i.e using excel connector) will bump up against query limits. (I got a few cascading triggers that prevent me from updating more than about 15 child records at one time.)

 

The map datatype can only have 1000 elements in it, so if a child has more than 1000 siblings, you'll get a runtime exception.

 

The time spent processing 900 siblings is noticable. (When I have some time to revisit this code, I'm going to experiment with making it asynchronous.)

 

 

 

 

The Trigger:

 

 

trigger AreaUpdate on Component__c (after delete, after insert, after undelete, after update) {
//
// =============================================================================================
//
// Determine if the Component__c that fired the trigger, has a value in the lookup field Area__c.
// If so, add the Asset's Area to a Set<Area ID>. Then pass the set to the AreaUpdate class which
// will calculate averages for certain Asset fields and then update the corresponding Area record
// with those values
//
// Copyright (c) 2009, BLB Properties LLC dba WastewaterSaaS.com dba WAMSaaS.com, dba AAMSaaS.com.
// All rights reserved.
//
// created: vwb: 2/24/09
//
// =============================================================================================
//
// find Areas IDs for all Assets invoking this trigger.
//
Set<ID> areaIds = new Set<ID>();
areaIds = new Set<ID>();

system.debug ('WAM - ******* Trigger: AreaUpdate: Trigger.old = '+Trigger.old);
system.debug ('WAM - ******* Trigger: AreaUpdate: Trigger.new = '+Trigger.new);

if (Trigger.isDelete)
{
for ( Component__c i : Trigger.old ) if (i.Area__c != null )areaIds.add(i.Area__c);
}
else if (Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete)
{
for ( Component__c i : Trigger.new ) if (i.Area__c != null ) areaIds.add(i.Area__c);
}

system.debug('WAM - ******* Trigger: AreaUpdate: Set<ID>: areaIds = ' + areaIds);
AreaUpdate au = new AreaUpdate();
au.calcAndUpdateAverages( au.findAssetData(areaIds) );
}

 

 And the class:

 

 

public class AreaUpdate {
//
// ==============================================================================
//
// Used by: trigger AreaUpdate (whenever an Asset is inserted, updated,
// deleted or undeleted.
//
// Method: findAssetData - takes a Set<ID> of Area IDs as input, queries all Assets (i.e.Component__c)
// for a given Area and returns a Map <Area_ID, List<Component__c>>.
//
// Method: calcAndUpdateAverages - takes the Map returned by the previous method, iterates through
// each instance of the List<Component__c>, aggregate the values for Risk Score,Vulnerability, Criticality
// and others and uses the aggregates to calculate average values, then updates Area__c
// records with the most recent Risk Score and other data from the Asset record.
//
// Copyright (c) 2009, BLB Properties LLC, dba WastewaterSaaS.com, dba WAMSaaS.com, dba AAMSaaS.com.
// All rights reserved.
//
// Created: 2/27/09 - vwb
//
// ===============================================================================
//
public Map<ID,List<Component__c>> findAssetData (Set<ID> areaIds)
{
//
// takes a Set<ID> of Area IDs as input, queries all Assets (i.e.Component__c)
// for a given Area and returns a Map <Area_ID, List<Component__c>>.
//
Map<ID,List<Component__c>> areaAssetsMap = new Map<ID,List<Component__c>>();
List<Component__c> complist = new List<Component__c>();
for (ID i :areaIds)
{
try
{
complist = [select w.id, w.Area__c,
w.Current_Risk_Score__c,
w.Vulnerability__c,
w.Evaluated_Remaining_Useful_Life__c,
w.Economic_Remaining_Useful_Life__c,
w.Criticality__c,
w.Condition__c
from Component__c w
where w.Area__c = :i];


system.debug ('WAM - ******* Class: AreaUpdate: Method: findAssetData: Component__c = ' + complist);
areaAssetsMap.put(i, complist);
}
catch (system.QueryException e)
{
system.debug ('WAM - ******* Class: AreaUpdate: Method: findAssetData: Inside CATCH block ; Component__c = ' + complist);
areaAssetsMap.put(i,complist);
}
}

system.debug ('WAM - ******* Class: AreaUpdate: Method: findAssetData: areaUpdateMap = ' + areaAssetsMap);
return (areaAssetsMap);
}

public void calcAndUpdateAverages (Map<ID, List<Component__c>> areaAssetMap)
{
//
// Takes the Map returned by the previous method, iterates through
// each instance of the List<Component__c>, aggregate the values for Risk Score,Vulnerability, Criticality
// and others and uses the aggregates to calculate average values, then updates Area__c
// records with the most recent Risk Score and other data from the Asset record.
//
Double avgAssetRisk = 0;
Double avgAssetVulnerability = 0;
Double avgAssetCriticality = 0;
Double avgAssetCondition = 0;
Double avgAssetEvRUL = 0;
Double avgAssetERUL = 0;
List<Area__c> a = new List<Area__c>();

for (ID i :areaAssetMap.keyset())
{
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: Incrementing ID counter: i = ' + i);
if (areaAssetMap.get(i) == null)
{
a.add( new Area__c (id = i,
Average_Risk__c = null,
Average_Vulnerability__c = null,
Average_Criticality__c = null,
Average_Condition__c = null,
Average_Evaluated_Remaining_Useful_Life__c = null,
Average_Economic_Remaining_Useful_Life__c = null));
}
else
{
Integer j = 0;
for (Component__c c : areaAssetMap.get(i))
{
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: Incrementing Component counter: c = ' + c);
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: c.Current_Risk_Score__c = ' + c.Current_Risk_Score__c);
if (c.Current_Risk_Score__c != null) avgAssetRisk = avgAssetRisk + c.Current_Risk_Score__c;
if (c.Vulnerability__c != null) avgAssetVulnerability = avgAssetVulnerability + c.Vulnerability__c;
if (c.Criticality__c != null) avgAssetCriticality = avgAssetCriticality + c.Criticality__c;
if (c.Condition__c != null) avgAssetCondition = avgAssetCondition + c.Condition__c;
if (c.Evaluated_Remaining_Useful_Life__c != null) avgAssetEvRUL = avgAssetEvRUL + c.Evaluated_Remaining_Useful_Life__c;
if (c.Economic_Remaining_Useful_Life__c != null) avgAssetERUL = avgAssetERUL + c.Economic_Remaining_Useful_Life__c;
j++;
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: Incremented counter: j = ' + j);

}
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: Value: j = ' + j);
if (avgAssetRisk !=0) avgAssetRisk = avgAssetRisk / j;
if (avgAssetVulnerability !=0) avgAssetVulnerability = avgAssetVulnerability / j;
if (avgAssetCriticality !=0) avgAssetCriticality = avgAssetCriticality / j;
if (avgAssetCondition !=0) avgAssetCondition = avgAssetCondition / j;
if (avgAssetEvRUL !=0) avgAssetEvRUL = avgAssetEvRUL / j;
if (avgAssetERUL !=0) avgAssetERUL = avgAssetERUL / j;

a.add( new Area__c (id = i,
Average_Risk__c = avgAssetRisk,
Average_Vulnerability__c = avgAssetVulnerability,
Average_Criticality__c = avgAssetCriticality,
Average_Condition__c = avgAssetCondition,
Average_Evaluated_Remaining_Useful_Life__c = avgAssetEvRUL,
Average_Economic_Remaining_Useful_Life__c = avgAssetERUL));
}
}
system.debug ('WAM - ******* Class: AreaUpdate: Method: calcAndUpdateAverages: Updating Area__c = ' + a);
update a;
}
}