Apex Code Development You need to sign in to do that
Don't have an account?
Angel30 count of grandchild records(Opportunity Line Items) on parent(Account)
I have a requirement to count the number of opportunity line items and populate it to the account.
I was thinking to use Aggregate queries,but is it poosible to do it using this.
I am in process of creating this however i am stuck:
public with sharing class opportunityTriggerHandlerClass {
public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
Set<Id> oppIdSet = new Set<Id>();
Set<Id> accIdSet = new Set<Id>();
for(OpportunityLineItem oli: oliList){
oppIdSet.add(oli.OpportunityId);
}
for(Opportunity oppty : [SELECT ID,AccountId FROM Opportunity WHERE Id IN: oppIdSet]){
accIdSet.add(oppty.AccountId) ;
}
List<AggregateResult> aggr = [SELECT OpportunityId,Opportunity.AccountId,Opportunity.Account.Name,count(Id)oli FROM
OpportunityLineItem
WHERE Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
}
}
I face an error that "Field must be grouped or aggregated: OpportunityId".
Can anybody tell me how should we approach here.
Thanks in advance!
I was thinking to use Aggregate queries,but is it poosible to do it using this.
I am in process of creating this however i am stuck:
public with sharing class opportunityTriggerHandlerClass {
public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
Set<Id> oppIdSet = new Set<Id>();
Set<Id> accIdSet = new Set<Id>();
for(OpportunityLineItem oli: oliList){
oppIdSet.add(oli.OpportunityId);
}
for(Opportunity oppty : [SELECT ID,AccountId FROM Opportunity WHERE Id IN: oppIdSet]){
accIdSet.add(oppty.AccountId) ;
}
List<AggregateResult> aggr = [SELECT OpportunityId,Opportunity.AccountId,Opportunity.Account.Name,count(Id)oli FROM
OpportunityLineItem
WHERE Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
}
}
I face an error that "Field must be grouped or aggregated: OpportunityId".
Can anybody tell me how should we approach here.
Thanks in advance!
Try the below one
List<AggregateResult> aggr = [SELECT Opportunity.AccountId, count(Id)oli FROM OpportunityLineItem WHERE Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ; }Pls let me know if you any issues. If it helps select as Solved and best answer.You can simply get the count as follows:
Let me know if it works.
thanks!
Since we do data loading, will it be good if we store that in a variable. What i mean is if suppose there are 5 accounts ,so which count will this be stored.
Thanks!
I made that change!! Thanks.But still it throws an error :
Apex trigger opportunityLineItemTrigger caused an unexpected exception, contact your administrator: opportunityLineItemTrigger: execution of AfterInsert caused by: System.SObjectException: Invalid field Opportunity.AccountId for AggregateResult: Class.opportunityTriggerHandlerClass.countofOliItemsMethod: line 18, column 1
Below is my code which i wrote:
public with sharing class opportunityTriggerHandlerClass {
public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){
Set<Id> oppIdSet = new Set<Id>();
Set<Id> accIdSet = new Set<Id>();
List<Account> accToUpdate = new List<Account>();
for(OpportunityLineItem oli: oliList){
oppIdSet.add(oli.OpportunityId);
}
for(Opportunity oppty : [SELECT ID,AccountId
FROM Opportunity WHERE Id IN: oppIdSet]){
accIdSet.add(oppty.AccountId) ;
}
List<AggregateResult> aggrs = [SELECT Opportunity.AccountId, count(Id)oli FROM OpportunityLineItem
where Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ;
if(aggrs.size() > 0){
for(AggregateResult aggr : aggrs){
Account accObj = new Account();
accObj.Id = (id)aggr.get('Opportunity.AccountId');
accObj.Count_Of_Opportunity_Line_Items__c =(decimal)aggr.get('oli') ;
accToUpdate.add(accObj);
}
update accToUpdate;
}
}
}
Trigger:
trigger opportunityLineItemTrigger on OpportunityLineItem (after insert) {
if(trigger.isAfter){
if(trigger.isInsert){
opportunityTriggerHandlerClass.countofOliItemsMethod(trigger.new);
}
}
}
Is there something which i am missing out.
Thanks in advance to all!!
Please change the below piece oof code and let me know.
for(AggregateResult aggr : aggrs){ for(Id accId : accIdSet ){ Account accObj = new Account(); accObj.Id = accId; accObj.NumberofLocations__c =(decimal)aggr.get('oli') ; accToUpdate.add(accObj); } } update accToUpdate; }If it helps select as Solved and best answer.
Try this code:
public with sharing class opportunityTriggerHandlerClass { public static void countofOliItemsMethod(List<OpportunityLineItem> oliList){ Set<Id> oppIdSet = new Set<Id>(); Set<Id> accIdSet = new Set<Id>(); List<Account> accToUpdate = new List<Account>(); for(OpportunityLineItem oli: oliList){ oppIdSet.add(oli.OpportunityId); } for(Opportunity oppty : [SELECT ID,AccountId FROM Opportunity WHERE Id IN: oppIdSet]){ accIdSet.add(oppty.AccountId) ; } List<AggregateResult> aggrs = [SELECT Opportunity.AccountId, count(Id)oli FROM OpportunityLineItem where Opportunity.AccountId IN:accIdSet GROUP BY Opportunity.AccountId] ; if(aggrs.size() > 0){ for(AggregateResult aggr : aggrs){ Account accObj = new Account(); accObj.Id = (id)aggr.get('AccountId'); accObj.Count_Of_Opportunity_Line_Items__c =(decimal)aggr.get('oli') ; accToUpdate.add(accObj); } update accToUpdate; } } } Trigger: trigger opportunityLineItemTrigger on OpportunityLineItem (after insert) { if(trigger.isAfter){ if(trigger.isInsert){ opportunityTriggerHandlerClass.countofOliItemsMethod(trigger.new); } } }You were getting the error in
accObj.Id = (id)aggr.get('Opportunity.AccountId');
Correct version of this would be: accObj.Id = (id)aggr.get('AccountId');
Let me know if it helps.
Thanks!
Could you explain me .As i read , it tells that you need to put the alias name that we mention for the field right? Like the way we are mentioning for Oli count field.
Problem with this is that we would not follow the best practice as its not recommended to use Nested for.
Hi Deepshikha,
Please mark the answer as the best answer so anyone facing similar kind of issue can benefit from this.
And to answer your question,
Yes, you are right we have to mention the alias name to get the value.
Use system.debug(aggrs);
to see what result you are getting in your aggregate results. It will be clear once you see the debug logs.
Thanks!