function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Joshua Graham 13Joshua Graham 13 

How to use a sum function in a trigger

What I am trying to do is look at the asset related list under the account object.  I want to sum up the Price field (MRR) for each asset that shows a status of "Active / Live".  If it sums up to <$1000 I want it to set a field called "Customer Type" to Bronze <1000, and if the sum of the assets with that Active / Live status is greater than $1000 but less than $2000, I want the Customer Type field to set to Silver....and so on.  I am really new to coding and am not sure how to proceed.  I was able to get the following code to count the number of assets and populate a custom field I made with that number.  I figured I could some how make this sum instead of count and then use if statements to update the Customer Type field based on the sum of that field.  Please advise what the best way of accomplishing this? 

trigger trgr_Customer_Tier_AutoUpdate on Account (before update) {

    Map <Id,integer> assetCount = new Map<Id,integer>();
   
    for(Account a:[SELECT Id,(select Id from Assets) from Account where Id IN:Trigger.New]){
   
        integer count = 0;
        for(Asset asset:a.Assets){
            count++;
        }
        assetCount.put(a.Id, count);
    }
   
    for(Account account:Trigger.New){
       
        account.Asset_Count__c=assetCount.get(account.Id);
        }
    }
Best Answer chosen by Joshua Graham 13
Pavan Kumar KajaPavan Kumar Kaja
Finally Working code

trigger TriggerTest on Asset(after insert, after update, after delete, after undelete) {

    List<Asset> lstAsset = new List<Asset>();

    set<Id> setAccId = new set<Id>();
   
    if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){

        if(!trigger.isdelete){

            lstAsset = trigger.new;

        }

        else{

            lstAsset = trigger.old;

        }

        for(Asset a : lstAsset){

            if(a.AccountId != null){

                setAccId.add(a.AccountId);

            }
        }
    }

    else{

        if(trigger.isUpdate){

            for(Asset a : Trigger.new){

                Asset oldAsset = trigger.oldMap.get(a.Id);


				if(a.AccountId != null){

					setAccId.add(a.AccountId);

				}

				if(oldAsset.AccountId != null){

					setAccId.add(oldAsset.AccountId);

				}
            }
        }
    }

       
    if(!setAccId.isEmpty()){

        Account acc;

        list<Account> lstAccount = new list<Account>();


        List<AggregateResult> lstAggr = [SELECT SUM(Price) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId,Status];

     

            for(AggregateResult ag : lstAggr){

                string accId = (string) ag.get('AccountId');

                decimal amount = (decimal) ag.get('amount');

                 

                if(amount <= 1000){

                    acc = new Account(Id = accId , Customer_Tier__C = 'Bronze');

                }

                else if(amount > 1000 && amount <= 2000){

                    acc = new Account(Id = accId , Customer_Tier__c = 'Silver');                  

                }
                else if(amount > 2000){
                
                acc = new Account(Id = accId , Customer_Tier__c = 'Gold');                  

                }
                
                lstAccount.add(acc);

            }

        update lstAccount;

    }

All Answers

Pavan Kumar KajaPavan Kumar Kaja

Hi Joshua Graham,

Try below code, this would work for you. if not let me know.

trigger trgr_Customer_Tier_AutoUpdate on Asset(after insert, after update, after delete, after undelete) {
	List<Asset> lstAsset = new List<Asset>();
    set<Id> setAccId = new set<Id>();
	
	if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){
        if(!trigger.isdelete){
            lstAsset = trigger.new;
        }
        else{
            lstAsset = trigger.old;
        }
        for(Asset a : lstAsset){
            if(a.AccountId != null){
                setAccId.add(a.AccountId);
            }
        }
    }
    else{
        if(trigger.isUpdate){
            for(Asset a : Trigger.new){
                Asset oldAsset = trigger.oldMap.get(a.Id);
                if(a.Price != oldAsset.Price){
                    if(a.AccountId != null){
                        setAccId.add(a.AccountId);
                    }
                    if(oldAsset.AccountId != null){
                        setAccId.add(oldContact.AccountId);
                    }
                }
            }
        }
    }
	
	
	if(!setAccId.isEmpty()){
		Account acc;
		list<Account> lstAccount = new list<Account>();
		List<AggregateResult> lstAggr = [SELECT SUM(Amount__c) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId];
    
			for(AggregateResult ag : vLstAggr){
				string accId = (string) ag.get('AccountId');
				decimal amount = (decimal) ag.get('amount');
				
				if(amount <= 1000){
					acc = new Account(Id = accId , Customer Type = 'Bronze');
				}
				else if(amount > 1000 && amount <= 2000){
					acc = new Account(Id = accId , Customer Type = 'Silver');
				}
				lstAccount.add(acc);
			}
		update lstAccount;
	}
}
Joshua Graham 13Joshua Graham 13
Hi Ashi - Thank you so much for responding.  I put the code in my trigger and I had a few errors.  I changed the code highlighted in yellow.  I changed (Amount__c) to (Price) after an error said invalid field Amount__c.  I changed Customer Type to Customer_Type__c which is the API name of the Account picklist field  I want to update.  And then when I tried to save a third time I got an error that Variable does not exist: oldContact.AccountId which I highlighted in green below.  Please advise! trigger trgr_Customer_Tier_AutoUpdate on Asset(after insert, after update, after delete, after undelete) {     List lstAsset = new List();     set setAccId = new set();         if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){         if(!trigger.isdelete){             lstAsset = trigger.new;         }         else{             lstAsset = trigger.old;         }         for(Asset a : lstAsset){             if(a.AccountId != null){                 setAccId.add(a.AccountId);             }         }     }     else{         if(trigger.isUpdate){             for(Asset a : Trigger.new){                 Asset oldAsset = trigger.oldMap.get(a.Id);                 if(a.Price != oldAsset.Price){                     if(a.AccountId != null){                         setAccId.add(a.AccountId);                     }                     if(oldAsset.AccountId != null){                         setAccId.add(oldContact.AccountId);                     }                 }             }         }     }             if(!setAccId.isEmpty()){         Account acc;         list lstAccount = new list();         List lstAggr = [SELECT SUM(Price) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId];                 for(AggregateResult ag : vLstAggr){                 string accId = (string) ag.get('AccountId');                 decimal amount = (decimal) ag.get('amount');                                 if(amount 1000 && amount
Pavan Kumar KajaPavan Kumar Kaja
Try below code,

change api names according to ur api names


trigger trgr_Customer_Tier_AutoUpdate on Asset(after insert, after update, after delete, after undelete) {
	List<Asset> lstAsset = new List<Asset>();
    set<Id> setAccId = new set<Id>();
	
	if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){
        if(!trigger.isdelete){
            lstAsset = trigger.new;
        }
        else{
            lstAsset = trigger.old;
        }
        for(Asset a : lstAsset){
            if(a.AccountId != null){
                setAccId.add(a.AccountId);
            }
        }
    }
    else{
        if(trigger.isUpdate){
            for(Asset a : Trigger.new){
                Asset oldAsset = trigger.oldMap.get(a.Id);
                if(a.Price != oldAsset.Price){
                    if(a.AccountId != null){
                        setAccId.add(a.AccountId);
                    }
                    if(oldAsset.AccountId != null){
                        setAccId.add(oldAsset.AccountId);
                    }
                }
            }
        }
    }
	
	
	if(!setAccId.isEmpty()){
		Account acc;
		list<Account> lstAccount = new list<Account>();
		List<AggregateResult> lstAggr = [SELECT SUM(Amount__c) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId];
    
			for(AggregateResult ag : vLstAggr){
				string accId = (string) ag.get('AccountId');
				decimal amount = (decimal) ag.get('amount');
				
				if(amount <= 1000){
					acc = new Account(Id = accId , Customer Type = 'Bronze');
				}
				else if(amount > 1000 && amount <= 2000){
					acc = new Account(Id = accId , Customer Type = 'Silver');
				}
				lstAccount.add(acc);
			}
		update lstAccount;
	}
}
Joshua Graham 13Joshua Graham 13
On line 40 above it is saying Error: Variable does not exist: vLstAggr.  If I change that to 1stAggr, not sure if that is what it is supposed to be?...the next error I get is Error: Field must be grouped or aggregated.  That error is on line 38 above.  Please advise!
Pavan Kumar KajaPavan Kumar Kaja
try below one
ping m eif u hav any issues.
@pavanthetech@gmail.com

trigger trgr_Customer_Tier_AutoUpdate on Asset(after insert, after update, after delete, after undelete) {
	List<Asset> lstAsset = new List<Asset>();
    set<Id> setAccId = new set<Id>();
	
	if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){
        if(!trigger.isdelete){
            lstAsset = trigger.new;
        }
        else{
            lstAsset = trigger.old;
        }
        for(Asset a : lstAsset){
            if(a.AccountId != null){
                setAccId.add(a.AccountId);
            }
        }
    }
    else{
        if(trigger.isUpdate){
            for(Asset a : Trigger.new){
                Asset oldAsset = trigger.oldMap.get(a.Id);
                if(a.Price != oldAsset.Price){
                    if(a.AccountId != null){
                        setAccId.add(a.AccountId);
                    }
                    if(oldAsset.AccountId != null){
                        setAccId.add(oldAsset.AccountId);
                    }
                }
            }
        }
    }
	
	
	if(!setAccId.isEmpty()){
		Account acc;
		list<Account> lstAccount = new list<Account>();
		List<AggregateResult> lstAggr = [SELECT SUM(Amount__c) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId];
    
			for(AggregateResult ag : lstAggr){
				string accId = (string) ag.get('AccountId');
				decimal amount = (decimal) ag.get('amount');
				
				if(amount <= 1000){
					acc = new Account(Id = accId , Customer Type = 'Bronze');
				}
				else if(amount > 1000 && amount <= 2000){
					acc = new Account(Id = accId , Customer Type = 'Silver');
				}
				lstAccount.add(acc);
			}
		update lstAccount;
	}
}
Pavan Kumar KajaPavan Kumar Kaja
Finally Working code

trigger TriggerTest on Asset(after insert, after update, after delete, after undelete) {

    List<Asset> lstAsset = new List<Asset>();

    set<Id> setAccId = new set<Id>();
   
    if(trigger.isInsert || trigger.isUndelete || trigger.isdelete){

        if(!trigger.isdelete){

            lstAsset = trigger.new;

        }

        else{

            lstAsset = trigger.old;

        }

        for(Asset a : lstAsset){

            if(a.AccountId != null){

                setAccId.add(a.AccountId);

            }
        }
    }

    else{

        if(trigger.isUpdate){

            for(Asset a : Trigger.new){

                Asset oldAsset = trigger.oldMap.get(a.Id);


				if(a.AccountId != null){

					setAccId.add(a.AccountId);

				}

				if(oldAsset.AccountId != null){

					setAccId.add(oldAsset.AccountId);

				}
            }
        }
    }

       
    if(!setAccId.isEmpty()){

        Account acc;

        list<Account> lstAccount = new list<Account>();


        List<AggregateResult> lstAggr = [SELECT SUM(Price) amount, AccountId, Status  FROM Asset WHERE AccountId In :setAccId AND Status='Active / Live' GROUP BY AccountId,Status];

     

            for(AggregateResult ag : lstAggr){

                string accId = (string) ag.get('AccountId');

                decimal amount = (decimal) ag.get('amount');

                 

                if(amount <= 1000){

                    acc = new Account(Id = accId , Customer_Tier__C = 'Bronze');

                }

                else if(amount > 1000 && amount <= 2000){

                    acc = new Account(Id = accId , Customer_Tier__c = 'Silver');                  

                }
                else if(amount > 2000){
                
                acc = new Account(Id = accId , Customer_Tier__c = 'Gold');                  

                }
                
                lstAccount.add(acc);

            }

        update lstAccount;

    }


This was selected as the best answer