+ Start a Discussion
Ricky SFDCRicky SFDC 

Trigger to update account field on the basis of two objects

Hi, I am new to salesforce and i am facing an issue in writing trigger.

The Requirement is like this:
I have 3 objects Account , Contract and Commodity(custom object).

If the account has a Contract or Commodity, the Status(custom field,picklist type) field of the  account must be set to Active.

Please help in resolving the issue.
Any help would be appreciated.
sowmya Inturi 9sowmya Inturi 9
HI Ricky,
You can write two triggers (One on Contract and another on Commodity) or a single batch class which queries all the accounts which has Contracts and Commodities and Schedule it frequently.

Let me know which method you prefer, Will help you out in writing that.

Thanks,
Sowmya.
Jolly_BirdiJolly_Birdi
Hello @Ricky


You can change the API's if somewhere it needs to and try this below code:
 
trigger AccountTrigger on Account (before update) {
    if(Trigger.isBefore && Trigger.isAfter){
		Set<Id> accountIds = new Set<Id>();
		for(Account acc : Trigger.new){
			accountIds.add(acc.Id);
		}
		
		Set<String> contractOrCommodityAccounts = new Set<String>();
		for(Contract con : [Select AccountId from Contract where AccountId In :accountIds]){
			contractOrCommodityAccounts.add((String)con.AccountId);
		}
		for(Commodity__c con : [Select Account__r.Id from Commodity__c where Account__cId In :accountIds]){
			contractOrCommodityAccounts.add((String)con.Account__r.Id);
		}
		
		for(Account acc : Trigger.new){
			if(contractOrCommodityAccounts.contains((String)acc.Id)){
				acc.Status__c = 'Active';
			}
		}
	}
}



Please like and mark this as best if you find it positive.

Thanks,
Jolly Birdi
Ricky SFDCRicky SFDC
Hi Sowmya,
thanks for replying , could you help me with the trigger first.

Thanks in advance.
Ricky SFDCRicky SFDC
Hi Jolly ,

Thanks for your efforts,
I tried your code but it is giving error as shown below, plz help me in resolving the error.


Error: Compile Error:
Account__r.Id from Commodity__c where Account__cId In :accountIds
^
ERROR at Row:1:Column:46
No such column 'Account__cId' on entity 'Commodity__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names. at line 12 column 32
Jolly_BirdiJolly_Birdi
Ricky, For this Replace Account__cId with Account__c. 

Hope so it would be helpful.

Thanks,
Jolly Birdi
Ricky SFDCRicky SFDC
Hi Jolly ,
Thanks for replying , now there is no error but it is still not working for commodity.
If there is commodity but no contract , it is not updating the field value.
If in the future the account does not have any commodity or contract in it then the status should be Inactive.

Please help me.

Thanks in advance.
Jolly_BirdiJolly_Birdi
Ricky,

For this you need to add the following 3 triggers and also update the account trigger as well.
 
trigger AccountTrigger on Account (before update) {
    if(Trigger.isBefore && Trigger.isUpdate){
		Set<Id> accountIds = new Set<Id>();
		for(Account acc : Trigger.new){
			accountIds.add(acc.Id);
		}
		
		Set<String> contractOrCommodityAccounts = new Set<String>();
		for(Contract con : [Select AccountId from Contract where AccountId In :accountIds]){
			contractOrCommodityAccounts.add((String)con.AccountId);
		}
		for(Commodity__c con : [Select Account__r.Id from Commodity__c where Account__c In :accountIds]){
			contractOrCommodityAccounts.add((String)con.Account__r.Id);
		}
		
		for(Account acc : Trigger.new){
			if(contractOrCommodityAccounts.contains((String)acc.Id)){
				acc.Status__c = 'Active';
			}
		}
	}
}
 
trigger ContactTrigger on Contact (After update) {
    if(Trigger.isUpdate && Trigger.isAfter){
		List<Account> accountList = new List<Account>();
		for(Contact Con : Trigger.new){
			if(Con.AccountId!=null){
				accountList.add(new Account(id=Con.AccountId));
			}
		}
		if(!accountList.isEmpty()){
			update accountList;
		}
	}
}
 
trigger CommodityTrigger on Commodity__c (After update) {
    if(Trigger.isUpdate && Trigger.isAfter){
		List<Account> accountList = new List<Account>();
		for(Commodity__c Com : Trigger.new){
			if(Com.Account__c!=null){
				accountList.add(new Account(id=Com.Account__c));
			}
		}
		if(!accountList.isEmpty()){
			update accountList;
		}
	}
}



Please like and mark this as best if you find it positive.

Thanks,
Jolly Birdi
sowmya Inturi 9sowmya Inturi 9
Hi Ricky,
Try using the below triggers:
1. On Contract
trigger AcctiveAccount on Contract (after insert,after delete)
{
        
    if(trigger.isInsert)
    {
    
         List<Account> ac=new List<Account>();
        
            for(Contract con:trigger.new)
            {
                if(con.accountid<>null)
                {
                    Account act=new Account();
                    act.Id=con.Accountid;
                    ac.add(act);
                }
            }
            
            List<Account> acout=[Select Id,Active__c,(Select id from Contracts),(Select id from CustomObj__r) from Account where id in: ac];
            
    
    
        List<Account> ac_Update=new List<Account>();
        for(Account act:acout)
        {
        
        act.Active__c ='Active';
        ac_Update.add(act);
    
        }
    
        update ac_Update;    
    }
    if(trigger.isDelete)
    {
        List<Account> ac_list=new List<Account>();
        
            for(Contract con:trigger.old)
            {
                if(con.accountid<>null)
                {
                    Account act=new Account();
                    act.Id=con.Accountid;
                    ac_list.add(act);
                }
            }
    
        List<Account> acout=[Select Id,Active__c,(Select id from Contracts),(Select id from CustomObj__r) from Account where id in: ac_list];
    
        List<Account> ac_Up=new List<Account>();
        for(Account ac:acout)
        {
            if(ac.contracts.size()==0 && ac.CustomObj__r.size()==0)
            {
                ac.Active__c='InActive'; // or null
                ac_Up.add(ac);
            }
        }
        update ac_Up;
    }
}

Similarly create another trigger on custom object also.

 By using these 2 triggers you can implement your requirement.
Let me know if you are facing any difficulties, I will help you out.

Thanks,
Sowmya.
Ricky SFDCRicky SFDC
Hi Sowmya,
Thanks for replying and for your efforts.
I tried to implement your code but it was not working.
Error:
Error: Compile Error:
from Contracts),(Select id from Commodity__r) from Account where
^
ERROR at Row:1:Column:64
Didn't understand relationship 'Commodity__r' in FROM part of query call. If you are attempting to use a custom relationshi
trigger ActiveAccount on Contract (after insert,after delete)
{
        
    if(trigger.isInsert)
    {
    
         List<Account> ac=new List<Account>();
        
            for(Contract con:trigger.new)
            {
                if(con.accountid<>null)
                {
                    Account act=new Account();
                    act.Id=con.Accountid;
                    ac.add(act);
                }
            }
            
            List<Account> acout=[Select Id,Status__c,(Select id from Contracts),(Select id from Commodity__r) from Account where id in: ac];
            
    
    
        List<Account> ac_Update=new List<Account>();
        for(Account act:acout)
        {
        
        act.Status__c ='Active';
        ac_Update.add(act);
    
        }
    
        update ac_Update;    
    }
    if(trigger.isDelete)
    {
        List<Account> ac_list=new List<Account>();
        
            for(Contract con:trigger.old)
            {
                if(con.accountid<>null)
                {
                    Account act=new Account();
                    act.Id=con.Accountid;
                    ac_list.add(act);
                }
            }
    
        List<Account> acout=[Select Id,Status__c,(Select id from Contracts),(Select id from Commodity__r) from Account where id in: ac_list];
    
        List<Account> ac_Up=new List<Account>();
        for(Account ac:acout)
        {
            if(ac.contracts.size()==0 && ac.Commodity__r.size()==0)
            {
                ac.Status__c='Inactive'; // or null
                ac_Up.add(ac);
            }
        }
        update ac_Up;
    }
}

p, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 19 column 33
Ricky SFDCRicky SFDC
Could you please help me in resolving the issue or could you provide me the second solution which u were talking about 'single batch class which queries all the accounts which has Contracts and Commodities and Schedule it frequently'.
As i have never used this method before.
Thanks in Advance.