You need to sign in to do that
Don't have an account?
Irvine Delacroix
How to Sum List value
Hi Guys,
I'm a newbie with Apex coding and I'm trying to summarize the value of List and then have the output to a field in the Account.
My Trigger is on Account object and I'm trying to query all the Contacts and then Sum a number field from contact object. (Like Roll up summary field)
List<Contact> conList = [SELECT NumFieldFromContact__c FROM CONTACT WHERE ID IN: <AccountID>];
Account A has 3 Contacts, and the NumFieldFromContact__c contains 10 value. So when my trigger run, the field under Account Object should be updated with the 30 Value.
Here's what I'm testing in Developer Console.
String accID = '0019000001EnVzf';
List<Contact> conList = [SELECT NumFieldFromContact__c FROM Contact WHERE contact.Account.id = :accID];
Integer i=0;
Double x;
Double y;
Do {
x = conList[i].Formula_Number_Field__c;
y = x + x;
i++;
} WHILE (i<conList.size());
System.debug(y);
Just experimenting on how things works. Can anyone please give me a correct syntax and Explanation of the Line? :)
I'm a newbie with Apex coding and I'm trying to summarize the value of List and then have the output to a field in the Account.
My Trigger is on Account object and I'm trying to query all the Contacts and then Sum a number field from contact object. (Like Roll up summary field)
List<Contact> conList = [SELECT NumFieldFromContact__c FROM CONTACT WHERE ID IN: <AccountID>];
Account A has 3 Contacts, and the NumFieldFromContact__c contains 10 value. So when my trigger run, the field under Account Object should be updated with the 30 Value.
Here's what I'm testing in Developer Console.
String accID = '0019000001EnVzf';
List<Contact> conList = [SELECT NumFieldFromContact__c FROM Contact WHERE contact.Account.id = :accID];
Integer i=0;
Double x;
Double y;
Do {
x = conList[i].Formula_Number_Field__c;
y = x + x;
i++;
} WHILE (i<conList.size());
System.debug(y);
Just experimenting on how things works. Can anyone please give me a correct syntax and Explanation of the Line? :)
You can use AggregateResult to get the SUM,AVH,MIN,MAX
Set<Id> accIds = new Set<Id>();
//Populate the above set with Account Ids for which you want to calculate total
List<AggregateResult> lstagr = [SELECT AccountId,SUM(NumFieldFromContact__c )tot FROM Contact WHERE AccountId IN : accs Group By AccountId];
This Query will give you the total of all contacts related to a account
You can get Account Account Id by (Id)agr.get('AccountId') and Tot for that Account by (Decimal)agr.get('amt') and keep them in a map
Map<Id,Decimal> mapTot=new Map<Id,Decimal>();
for(AggregateResult agr :lstagr){
id ids = (Id)agr.get('AccountId');
Decimal amnt = (Decimal)agr.get('amt');
mapTot.put(ids,amnt);
}
This map will give you the total for a particular account
Regards,
Bhanu Mahesh
All Answers
Use Aggregate Query To Achieve your Result...
You can use AggregateResult to get the SUM,AVH,MIN,MAX
Set<Id> accIds = new Set<Id>();
//Populate the above set with Account Ids for which you want to calculate total
List<AggregateResult> lstagr = [SELECT AccountId,SUM(NumFieldFromContact__c )tot FROM Contact WHERE AccountId IN : accs Group By AccountId];
This Query will give you the total of all contacts related to a account
You can get Account Account Id by (Id)agr.get('AccountId') and Tot for that Account by (Decimal)agr.get('amt') and keep them in a map
Map<Id,Decimal> mapTot=new Map<Id,Decimal>();
for(AggregateResult agr :lstagr){
id ids = (Id)agr.get('AccountId');
Decimal amnt = (Decimal)agr.get('amt');
mapTot.put(ids,amnt);
}
This map will give you the total for a particular account
Regards,
Bhanu Mahesh
To update account using Aggregate query you need one more query to fetch account records to updates. If you are ok with it then use Aggregate result. Otherwise use the below code
I have taken Number field on Contact as testNumber__c and On account as Account_Test__c
trigger listOfContactsOnAccount on contact (after insert , after update,after delete){
set<id> accountIdSet = new set<id>();
List<Account> lstAccToUpdate = new List<Account>();
Map<Id,Account> mapAccWithId = new Map<Id,Account>();
if( trigger.isInsert){
for(contact c : trigger.new){
if(c.testNumber__c != null){
accountIdSet.add(c.AccountId);
}
}
}
if(trigger.isUpdate){
for(contact c : trigger.new){
if(c.testNumber__c != trigger.OldMap.get(c.Id).testNumber__c){
accountIdSet.add(c.AccountId);
}
}
}
if(trigger.isDelete){
for(contact c: trigger.old){
if(c.testNumber__c != null){
accountIdSet.add(c.AccountId);
}
}
}
if(!accountIdSet.isEmpty()){
for(Account acc :[SELECT Id,Account_Test__c,(SELECT Id,testNumber__c FROM Account.Contacts) FROM Account WHERE Id IN :accountIdSet]){
Decimal tot = 0;
for(Contact con :acc.Contacts){
tot = tot + con.testNumber__c;
}
acc.Account_Test__c = tot;
mapAccWithId.put(acc.Id,acc);
}
}
if(mapAccWithId != null && mapAccWithId.values().size() > 0){
update mapAccWithId.values();
}
}
Regards,
Bhanu Mahesh
I'll play with the codes that you suggested and I'll try to figure out how they really works :)
Is there any other resources I can check about these functions and things about apex that are easy to understand? (Like sfdc99.com) :)
Apex is really exciting and I want to learn more.. I hope you can send me some resources in my email :) vinzell999@gmail.com
Thanks a bunch! @Bhanu Mahesh and Dushyant Sonwar!
I noticed that this actually gives different result if the Field that I am referencing is a Formula Number field.
What's the fix for that?
Thanks again! :)