Sign Up ›

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

HELP! Calculate the Sum of a RelatedList

Hello guys,

 

I need help here (my Sales guys are killing me softly) and they would require the Sum of  all the investment amount .

Now i know normally Roll-up Summary is used for this; but the Custom Object Adviser is not a Master Relationshipship

 

In the Adviser record view they will like to see the total sum of all investment associated with the RelatedList ManagedFund__c

 

Please guys; what is the best way to achieve this and how?

 

 

Thanks in advance.

Best Answer chosen by Admin (Salesforce Developers) 
Jerun JoseJerun Jose
An apex trigger which simulates a roll up summary has been done a million times.

You can check my post at
http://boards.developerforce.com/t5/Apex-Code-Development/Rollup-summary-on-Contact/td-p/408625

It is a more comprehensive trigger which handles multiple scenarios.

All Answers

PremanathPremanath

Write a trigger on ManagedFund__c .  Like this

create one field on Adviser object. Total_amount__c.

in below code Insted of Adviser i have taken account.

 And instead of ManagedFund__c  i have taken opportunity.

You can replace and worked out.



trigger AmountTask on Opportunity (after insert,after update){
if(Trigger.isinsert){
Map<id,decimal> acctotal = new Map<id,decimal>();
for(opportunity opp :trigger.new){
if(opp.amount!=null){
if(acctotal.get(opp.accountid)==null){
acctotal.put(opp.accountid,opp.amount);
}
else
{
decimal amt = 0;
amt = acctotal.get(opp.accountid)+opp.amount;
acctotal.put(opp.accountid,amt);
}
}
}
list<account> alist= new list<account>();
list<account> accList = [select total_amount__c from account where id in:acctotal.keyset()];
for(account a:acclist){
a.total_amount__c+=acctotal.get(a.id);
alist.add(a);
}
update alist;
}

}
jungleeejungleee

Hi There,

 

I think this can be done using a trigger. my Assumption is Advisor__c is a look up on the ManagedFund__c object. I am also assuming the sum_total_investement__c field on the advisor object and total_investment on ManagedFund__c object.

 

Trigger calcSumTotalInvestment on ManagedFund__c (after insert, after update){
	
	//calc the sum of the total investment, assuming that the total_investement__c is a field of type number.
	AggregateResult[] groupedResults = [select SUM(total_investment__c) totInvest from managedFund__c where Advisor__r.id =: trigger.new[0].Advisor__c.id];
	
	//fetch the results from the aggregate result and conver it to string.
	Object sumTotalInvestment = groupedResults[0].get('totInvest');
	String sumTotalInvest = String.valueOf(sumTotalInvestment);
	
	//fetch the record from advisor and update that reocrd.
	Advisor__c advise = [select sum_total_investment__c from Advisor__c where id=: trigger.new[0].Advisor__c.id ];
	advise.sum_total_investment__c = sumTotalInvest;
	update advise;
}

 Hope this helps!!

 

Regards

Sam

 
Jerun JoseJerun Jose
An apex trigger which simulates a roll up summary has been done a million times.

You can check my post at
http://boards.developerforce.com/t5/Apex-Code-Development/Rollup-summary-on-Contact/td-p/408625

It is a more comprehensive trigger which handles multiple scenarios.
This was selected as the best answer
TeddyAbleTeddyAble

Thanks guys for your help

 

Here is the final code (for those who need this) based Jose's code which is modified to fit my Org requirement

 

 

trigger RollUp on Managed_Fund__c (after insert, after update, before delete) {

    // Set to hold the IDs of the Contacts of the Subscriptions
    Set<ID> AdviserIds= new Set<ID>();
    // Set to hold the IDs of the Subscriptions
    Set<ID> SubIDs = new Set<ID>();
    // Set to hold the IDs of the Contacts where the values need to be reset
    Set<ID> AdviserIdssForReset = new Set<ID>();
    Set<ID> AdviserIdss=New Set<ID>();
     // The list of Contacts where the update has to take place
List<Adviser__c> AdvisersToUpdate = new List<Adviser__c>(); 
    if(trigger.isInsert)
    {
        for(Managed_Fund__c rsSub : trigger.new )
        {
            // If the contact of a subscription has changed, then the old contact also needs to be updated 
            if( rsSub.Adviser__c != trigger.newmap.get(rsSub.ID).Adviser__c)
                if( trigger.oldmap.get(rsSub.ID).Adviser__c!= null )
                    AdviserIdss.add(trigger.newmap.get(rsSub.ID).Adviser__c);
            if(rsSub.Adviser__c!=null)
            {
                AdviserIds.add(rsSub.Adviser__c);
            }
        }
        AdviserIdssForReset.addAll(AdviserIds);

          

        // Querying the database to get the Number of Subscriptions under an contact and the sum of the deal values of the Subscriptions under an contact
        AggregateResult[] fuMSum = [select sum(Total_Unit_Balance__c) aggr, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds group by Adviser__c ];
        
        
        for( AggregateResult AdviserDetail  : fuMSum )
        {
            Adviser__c AdviserToUpdate5 = new Adviser__c(ID = (ID)(AdviserDetail .get('AdviserIds')));
            AdviserToUpdate5.FUM__c ='$ '+String.valueOf(AdviserDetail.get('aggr'));
            AdvisersToUpdate.add(AdviserToUpdate5);
            // The contact being handled should not be reset. Hence removing it from the ResetSet
            AdviserIdssForReset.remove((ID)(AdviserDetail .get('AdviserIds')));
            System.debug('###'+String.valueOf(AdviserDetail.get('aggr')));
        }
        
        for( ID i : AdviserIdssForReset )
        {
            // Resetting the summed up values to 0 if the contact no longer has a .V.
            Adviser__c AdviserToUpdate4 = new Adviser__c( ID = i );
            AdviserToUpdate4.FUM__c = '0';
            AdvisersToUpdate.add(AdviserToUpdate4);
        }
        
        if(AdvisersToUpdate.size() > 0)
        {
           update AdvisersToUpdate;
         }   
        AdvisersToUpdate.clear();
    }

    if(trigger.isupdate)
    {
        for(Managed_Fund__c rsSub : trigger.new )
        {
            // If the contact of a subscription has changed, then the old contact also needs to be updated 
            if( rsSub.Adviser__c != trigger.oldmap.get(rsSub.ID).Adviser__c)
                if( trigger.oldmap.get(rsSub.ID).Adviser__c!= null )
                    AdviserIdss.add(trigger.oldmap.get(rsSub.ID).Adviser__c);
            if(rsSub.Adviser__c!=null)
            {
                AdviserIds.add(rsSub.Adviser__c);
            }
        }
        AdviserIdssForReset.addAll(AdviserIds);

          

        // Querying the database to get the Number of Subscriptions under an contact and the sum of the deal values of the Subscriptions under an contact
        AggregateResult[] fuMSum = [select sum(Total_Unit_Balance__c) aggr, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds group by Adviser__c ];
        
        for( AggregateResult AdviserDetail  : fuMSum )
        {
            Adviser__c AdviserToUpdate5 = new Adviser__c(ID = (ID)(AdviserDetail .get('AdviserIds')));
            AdviserToUpdate5.FUM__c = '$ '+String.Valueof(AdviserDetail.get('aggr'));
            AdvisersToUpdate.add(AdviserToUpdate5);
            // The contact being handled should not be reset. Hence removing it from the ResetSet
            AdviserIdssForReset.remove((ID)(AdviserDetail .get('AdviserIds')));
        }
        
        for( ID i : AdviserIdssForReset )
        {
            // Resetting the summed up values to 0 if the contact no longer has a .V.
            Adviser__c AdviserToUpdate4 = new Adviser__c( ID = i );
            AdviserToUpdate4.FUM__c = '0';
            AdvisersToUpdate.add(AdviserToUpdate4);
        }
        
        if(AdvisersToUpdate.size() > 0)
        {
           update AdvisersToUpdate;
         }   
        AdvisersToUpdate.clear();
    }

    if(trigger.isdelete)
    {
        for(Managed_Fund__c rsSub : trigger.old )
        {
            if( rsSub.Adviser__c != null)
                AdviserIdss.add(rsSub.Adviser__c);
            SubIDs.add(rsSub.ID);
        }
        AdviserIdssForReset.addAll(AdviserIdss);

        // The list of Contacts where the update has to take place
        List<Adviser__c> AdviserToUpdate = new List<Adviser__C>();    
        
        // Querying the database to get the Number of Subscriptions under an contact excluding the Subscriptions being deleted
        AggregateResult[] fuMSum = null;
        if(AdviserIdss.size() > 0)
        {
            fuMSum = [select count(ID) Fum__c, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds and ID not in: SubIDs group by Adviser__c ];

            for( AggregateResult ContactDetail : fuMSum)
            {
                Adviser__c AdviserToUpdate2 = new Adviser__c(ID = (ID)(ContactDetail.get('AdviserIds')));
                AdviserToUpdate2.Fum__c = '$ '+String.Valueof(ContactDetail.get('Total_Unit_Balance__c'));
                AdviserToUpdate.add(AdviserToUpdate2);
                // The contact being handled should not be reset. Hence removing it from the ResetSet
                AdviserIdssForReset.remove((ID)(ContactDetail.get('AdviserIds')));
            }

            for( ID i : AdviserIdssForReset )
            {
                // Resetting the summed up values to 0 if the contact no longer has a subscription.
                Adviser__c AdviserToUpdate3 = new Adviser__c( ID = i );
                AdviserToUpdate3.FUM__c = '0';
               AdviserToUpdate.add(AdviserToUpdate3);
            }
        }
        
        if(AdviserToUpdate.size() > 0)
            update AdviserToUpdate;

        AdviserToUpdate.clear();
    }
}

 

 

 

Thanks guys
Jerun JoseJerun Jose

I guess you will have a problem with inserts because of the usage of trigger.oldmap during insert

 

If so use the code below

 

trigger RollUp on Managed_Fund__c (after insert, after update, before delete) {

    // Set to hold the IDs of the Contacts of the Subscriptions
    Set<ID> AdviserIds= new Set<ID>();
    // Set to hold the IDs of the Subscriptions
    Set<ID> SubIDs = new Set<ID>();
    // Set to hold the IDs of the Contacts where the values need to be reset
    Set<ID> AdviserIdssForReset = new Set<ID>();
    Set<ID> AdviserIdss=New Set<ID>();
     // The list of Contacts where the update has to take place
	List<Adviser__c> AdvisersToUpdate = new List<Adviser__c>(); 
    if(trigger.isInsert)
    {
        for(Managed_Fund__c rsSub : trigger.new )
        {
            // If the contact of a subscription has changed, then the old contact also needs to be updated if( rsSub.Adviser__c != trigger.newmap.get(rsSub.ID).Adviser__c) if( trigger.oldmap.get(rsSub.ID).Adviser__c!= null ) AdviserIdss.add(trigger.newmap.get(rsSub.ID).Adviser__c);
            if(rsSub.Adviser__c!=null)
            {
                AdviserIds.add(rsSub.Adviser__c);
            }
        }
        AdviserIdssForReset.addAll(AdviserIds);

          

        // Querying the database to get the Number of Subscriptions under an contact and the sum of the deal values of the Subscriptions under an contact
        AggregateResult[] fuMSum = [select sum(Total_Unit_Balance__c) aggr, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds group by Adviser__c ];
        
        
        for( AggregateResult AdviserDetail  : fuMSum )
        {
            Adviser__c AdviserToUpdate5 = new Adviser__c(ID = (ID)(AdviserDetail .get('AdviserIds')));
            AdviserToUpdate5.FUM__c ='$ '+String.valueOf(AdviserDetail.get('aggr'));
            AdvisersToUpdate.add(AdviserToUpdate5);
            // The contact being handled should not be reset. Hence removing it from the ResetSet
            AdviserIdssForReset.remove((ID)(AdviserDetail .get('AdviserIds')));
            System.debug('###'+String.valueOf(AdviserDetail.get('aggr')));
        }
        
        for( ID i : AdviserIdssForReset )
        {
            // Resetting the summed up values to 0 if the contact no longer has a .V.
            Adviser__c AdviserToUpdate4 = new Adviser__c( ID = i );
            AdviserToUpdate4.FUM__c = '0';
            AdvisersToUpdate.add(AdviserToUpdate4);
        }
        
        if(AdvisersToUpdate.size() > 0)
        {
           update AdvisersToUpdate;
         }   
        AdvisersToUpdate.clear();
    }

    if(trigger.isupdate)
    {
        for(Managed_Fund__c rsSub : trigger.new )
        {
            // If the contact of a subscription has changed, then the old contact also needs to be updated 
            if( rsSub.Adviser__c != trigger.oldmap.get(rsSub.ID).Adviser__c)
                if( trigger.oldmap.get(rsSub.ID).Adviser__c!= null )
                    AdviserIdss.add(trigger.oldmap.get(rsSub.ID).Adviser__c);
            if(rsSub.Adviser__c!=null)
            {
                AdviserIds.add(rsSub.Adviser__c);
            }
        }
        AdviserIdssForReset.addAll(AdviserIds);

          

        // Querying the database to get the Number of Subscriptions under an contact and the sum of the deal values of the Subscriptions under an contact
        AggregateResult[] fuMSum = [select sum(Total_Unit_Balance__c) aggr, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds group by Adviser__c ];
        
        for( AggregateResult AdviserDetail  : fuMSum )
        {
            Adviser__c AdviserToUpdate5 = new Adviser__c(ID = (ID)(AdviserDetail .get('AdviserIds')));
            AdviserToUpdate5.FUM__c = '$ '+String.Valueof(AdviserDetail.get('aggr'));
            AdvisersToUpdate.add(AdviserToUpdate5);
            // The contact being handled should not be reset. Hence removing it from the ResetSet
            AdviserIdssForReset.remove((ID)(AdviserDetail .get('AdviserIds')));
        }
        
        for( ID i : AdviserIdssForReset )
        {
            // Resetting the summed up values to 0 if the contact no longer has a .V.
            Adviser__c AdviserToUpdate4 = new Adviser__c( ID = i );
            AdviserToUpdate4.FUM__c = '0';
            AdvisersToUpdate.add(AdviserToUpdate4);
        }
        
        if(AdvisersToUpdate.size() > 0)
        {
           update AdvisersToUpdate;
         }   
        AdvisersToUpdate.clear();
    }

    if(trigger.isdelete)
    {
        for(Managed_Fund__c rsSub : trigger.old )
        {
            if( rsSub.Adviser__c != null)
                AdviserIdss.add(rsSub.Adviser__c);
            SubIDs.add(rsSub.ID);
        }
        AdviserIdssForReset.addAll(AdviserIdss);

        // The list of Contacts where the update has to take place
        List<Adviser__c> AdviserToUpdate = new List<Adviser__C>();    
        
        // Querying the database to get the Number of Subscriptions under an contact excluding the Subscriptions being deleted
        AggregateResult[] fuMSum = null;
        if(AdviserIdss.size() > 0)
        {
            fuMSum = [select count(ID) Fum__c, Adviser__c AdviserIds from Managed_Fund__c where Adviser__c in: AdviserIds and ID not in: SubIDs group by Adviser__c ];

            for( AggregateResult ContactDetail : fuMSum)
            {
                Adviser__c AdviserToUpdate2 = new Adviser__c(ID = (ID)(ContactDetail.get('AdviserIds')));
                AdviserToUpdate2.Fum__c = '$ '+String.Valueof(ContactDetail.get('Total_Unit_Balance__c'));
                AdviserToUpdate.add(AdviserToUpdate2);
                // The contact being handled should not be reset. Hence removing it from the ResetSet
                AdviserIdssForReset.remove((ID)(ContactDetail.get('AdviserIds')));
            }

            for( ID i : AdviserIdssForReset )
            {
                // Resetting the summed up values to 0 if the contact no longer has a subscription.
                Adviser__c AdviserToUpdate3 = new Adviser__c( ID = i );
                AdviserToUpdate3.FUM__c = '0';
               AdviserToUpdate.add(AdviserToUpdate3);
            }
        }
        
        if(AdviserToUpdate.size() > 0)
            update AdviserToUpdate;

        AdviserToUpdate.clear();
    }
}

 
TeddyAbleTeddyAble

Done!

Thanks,