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
Niraj Kumar 9Niraj Kumar 9 

trigger to count the task of Account ,contact, opportunity and update in the Account count field

Hi Guys,

I need a trigger to count the task of Account, Contact, opportunity and update in the Account count field with sum of Account task, Contact task, Opportunity task.

EX - Create a Account, create 2 task on Account, create a contact related to this account and create 1 task, create a opportunity related to this account  create 2 task. In Account Count Field it shoud be 5 taks.
Srinivas SSrinivas S
Trigger TaskTrigger on Task(after insert, after delete, after undelete) {
    Set<Id> accountIds = new Set<Id>();
    for(Task tsk : trigger.isDelete ? trigger.old : trigger.new) {
        accountIds.add(tsk.AccountId);
    }
    AggregateResult[] groupedResults = [SELECT Count(Id), AccountId FROM Task group by AccountId having AccountId in: accountIds];
    Map<Id,Integer> taksCountMap = new Map<Id,Integer>();
    for (AggregateResult ar : groupedResults)  {
        taksCountMap.put((Id)ar.get('AccountId'),Integer.valueOf(ar.get('expr0')));
    }
    
    List<Account> accUpdLst = new List<Account>();
    //Updating the count field
    for(Task tsk: trigger.new) {   
        if(taksCountMap.containsKey(tsk.AccountId))
            accUpdLst.add(new Account(Id = tsk.AccountId,count__c = taksCountMap.get(tsk.AccountId)));
    }
    
    if(accUpdLst.size() > 0) {
        update accUpdLst;
    }
}

I have tested the above code and it is working. All the tasks related to accounts, contacts and cases will be linked to account automatically by salesforce.

Thanks,
Srinivas
- Please mark as solution if your problem is resolved.
veda Hebbarveda Hebbar
Hi Niraj,

You can create trigger on task. Following are the steps need to be done in trigger
In trigger:(After Insert/update/Delete of task)
1. Create below variables
Set<Id> setAccountId;
Set<Id> setContactId;
Set<Id> setOpportunityId;
map<Id, Integer> mapAccoutIdwithTaskCount.

2. iterate over Task

if(task is related to Account)
    setAccountId.add(AccountId);
else if(task is related to Opportunity )
    setOpportunityId.add(OpportunityId);
else if(task is related to Contact )
    setContactId.add(ContactId);
    
3. Query Account with task 
for(Account objAccount: [Select Id, (Select Id From Tasks) From Account])
{
    if(!objAccount.Tasks.isEmpty())
    {
        mapAccoutIdwithTaskCount.put(objAccount.Id,objAccount.Tasks.size())
    }
    else
    {
        mapAccoutIdwithTaskCount.put(objAccount.Id,0)
    }
}


4. Query Account with task 
for(Contact objContact: [Select Id, (Select Id From Tasks) From Contact])
{
    if(objContact.AccountId != null)
    {
        Integer intCount = 0;
        if(mapAccoutIdwithTaskCount.containsKey(objContact.AccountId))
            intCount = mapAccoutIdwithTaskCount.get(objContact.AccountId);
            
        if(!objContact.Tasks.isEmpty() )
        {
            mapAccoutIdwithTaskCount.put(objContact.AccountId,intCount + objContact.Tasks.size())
        }
        else
        {
            mapAccoutIdwithTaskCount.put(objContact.AccountId,intCount)
        }
    }
}

5. *repeat the 4th step for opportunity*

6. List<Account> lstAccountToUpdate = new list<Account>();
for(Id idAccount: mapAccoutIdwithTaskCount.keySet())
{    
    Account objAccount = new Account(Id = idAccount, Count = mapAccoutIdwithTaskCount.get(idAccount) );
    lstAccountToUpdate.add(objAccount);
}

if(!lstAccountToUpdate.isEmpty())
    update lstAccountToUpdate;
    
Thanks,
Vedashri
 
Dushyant SonwarDushyant Sonwar

Hey Niraj,

This is standard Behaviour . You can check out below url for better understanding

https://help.salesforce.com/apex/HTViewSolution?id=000003747&language=en_US
Niraj Kumar 9Niraj Kumar 9
Hi Srini,

Its not working in our sandbox. Can u please modify the code?

Thanks.
Niraj Kumar 9Niraj Kumar 9
HI Srini,
Your code is working fine in other org but here (in our org) need to be call future method and pass the query in future method and update the count Because I am getting error like :::: 
Apex trigger AccountTaskcount caused an unexpected exception, contact your administrator: AccountTaskcount: execution of AfterInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.AccountTaskcount: line 9, column 1


Thanks.
 
Niraj Kumar 9Niraj Kumar 9
Hi,

line 9, column 1 means soql query

Thanks.
Niraj Kumar 9Niraj Kumar 9
HI Hebbar,:
Here is code with your step. Error::::   
Trigger NewAccountCountTask on Task(After Insert, After Delete)
{
Set<Id> accountIds = new Set<Id>();

Set<Id> ContactIds = new Set<Id>();

Set<Id> OpportunityIds = new Set<Id>();

map<Id, Integer> mapAccoutIdwithTaskCount = new map<Id, Integer> ();

for (Task tsk1 : trigger.new){
 if(tsk1.WhatId <> NULL && tsk1.WhatId.getSobjectType() == Account.getSObjectType()){

    accountIds.add(tsk1.WhatId);
       }
 if (tsk1.WhatId <> NULL && tsk1.WhatId.getSobjectType() == Opportunity.getSObjectType()){
   
  OpportunityIds.add(tsk1.WhatId);
    }

if (tsk1.WhoId <> NULL && tsk1.WhoId.getSobjectType() == Contact.getSObjectType()){
     ContactIds.add(tsk1.WhoId);
  }


for(Account objAccount: [Select Id, (Select Id From Tasks) From Account])
{
    if(!objAccount.Tasks.isEmpty())
    {
        mapAccoutIdwithTaskCount.put(objAccount.Id,objAccount.Tasks.size());
    }
    else
    {
        mapAccoutIdwithTaskCount.put(objAccount.Id,0);
    }
}
for(Contact objContact: [Select Id, (Select Id From Tasks) From Contact])
{
    if(objContact.AccountId != null)
    {
        Integer intCount = 0;
        if(mapAccoutIdwithTaskCount.containsKey(objContact.AccountId)) <------------
            intCount = mapAccoutIdwithTaskCount.get(objContact.AccountId);
            
        if(!objContact.Tasks.isEmpty() )
        {
            mapAccoutIdwithTaskCount.put(objContact.AccountId,intCount + objContact.Tasks.size())
        }
        else
        {
            mapAccoutIdwithTaskCount.put(objContact.AccountId,intCount)
        }
    }
}
for(Opportunity objOpp: [Select Id, (Select Id From Tasks) From Opportunity  ])
{
    if(objOpp.AccountId != null)
    {
        Integer intCount = 0;
        if(mapAccoutIdwithTaskCount.containsKey(objOpp.AccountId))   <-----------
            intCount = mapAccoutIdwithTaskCount.get(objOpp.AccountId);
            
        if(!objOpp.Tasks.isEmpty() )
        {
            mapAccoutIdwithTaskCount.put(objOpp.AccountId,intCount + objOpp.Tasks.size());
        }
        else
        {
            mapAccoutIdwithTaskCount.put(objOpp.AccountId,intCount);
        }
    }
 }
}

List<Account> lstAccountToUpdate = new list<Account>();
for(Id idAccount: mapAccoutIdwithTaskCount.keySet())
{    
    Account objAccount = new Account(Id = idAccount, Count_of_Activity__c = mapAccoutIdwithTaskCount.get(idAccount) );
    lstAccountToUpdate.add(objAccount);
}

if(!lstAccountToUpdate.isEmpty())
    update lstAccountToUpdate;
}

Apex trigger NewAccountCountTask caused an unexpected exception, contact your administrator: NewAccountCountTask: execution of AfterInsert caused by: System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Contact.AccountId: Trigger.NewAccountCountTask: line 42, column 1 getting error while creating task on Account, contact, opportunity.

Please help me .
 
veda Hebbarveda Hebbar
Hi Niraj,

Sorry for late reply. In Line number 37 you have queried Contact but not added AccountId field in it. But AccountId you are using in Line number 42 that is you are gettig this issue.

Please chnage Line 37 fo loop fro below code 
for(Contact objContact: [Select Id,AccountId (Select Id From Tasks) From Contact])
If you got solution please mark best answer. Or let me know if you need more information.

Thanks,
Vedashri