+ Start a Discussion
sonam gupthasonam guptha 

Need a trigger to get a field value from a custom table based on a record type and print it on standard table account.

Hello everyone,

Can any one help me to reslove it.
I have a custom table(Member__c) it has 2 record types(rc1,rc2) and on member__c i have a column total_column__c,And on account, we created 2 custom columns(rc1,rc2),so in this columns now i wanted to print out that particular values,here member__c is lookup to account and opportunity,and member__c is a junction object,i can only select opportunity on member__c not account as per my flow.account is tagged on opportunity,so now i have to consider account from opportunity.and print those values on opportunities related account.

it looks  something like below :—
member__c.rc1.total_column__c(print this value on account custom column) = account.rc1(Custom field).(i.e; member__c.releted_account)
member__c.rc2. total_column__c( print this value on account custom column) = account.rc2(Custom field).(i.e; member__c.releted_account)
Here is my apex trigger but am printing the same values on all 2 columns :—
 
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete) {

IdITWINGId = [select Id,name from RecordType where name='ITWING' and SObjectType=‘member__c' limit 1].Id;
Id FinanceId = [select Id,name from RecordType where name=‘Finance' and SObjectType=‘member__c' limit 1].Id;
 
 List<Account> lst = new List<Account>();
 
List<RecordType>  ITWingId = [select Id,name from RecordType where name=‘ITWING' and SObjectType=‘Member__c' limit 1];
 Map<ID,RecordType>  FinanceId = new  Map<ID,RecordType>([select Id,name from RecordType where name=‘Finance' and SObjectType=‘Member__c' limit 1]); 

id ITWINGRT = Schema.SObjectType.member__c.getRecordTypeInfosByName().get('ITWING').getRecordTypeId();
id FinanceRT = Schema.SObjectTypemember__c.getRecordTypeInfosByName().get('Finance').getRecordTypeId();


 Map<id,member__c> ITWINGList =new Map<id,Asset__c>([SELECT RecordTypeid, total_column__c FROM member__c WHERE RecordTypeId =: ITWINGId]);

    for(Member__c asst : Trigger.new){
      
      for(Account temp : [select id,name from Account where id =: asst.lookupAccount__c])
    {
       if(asst.RecordTypeId == ITWINGRT)
       {   
      temp.ITWING__c = ITWINGList.get(total_column__c);
        }
       if(asst.RecordTypeId == FinanceRT){
        temp.Finance__c = asst.total_column__c;
         }
        
        lst.add(temp);

    }
   } 
    if(lst.size()>0){
     update lst; 
    }
}

Thaks in Advance!


 
 
LBKLBK
Try this trigger.
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete) {

	Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
	Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;

	Map<Id, Account> mapAccount = new Map<Id, Account>();

	for(Member__c objMember : Trigger.new){
		Account objAccount;
		if(mapAccount.containsKey(objMember.lookupAccount__r.Id)){
			objAccount = mapAccount.get(objMember.lookupAccount__r.Id);
		}
		else{
			objAccount = new Account();
			objAccount.Id = objMember.lookupAccount__r.Id;
		}
		if(objMember.RecordTypeId == recTypeIT){
			objAccount.ITWING__c = objMember.total_column__c;
		}
		else if(objMember.RecordTypeId == recTypeFinance){
			objAccount.Finance__c = objMember.total_column__c;
		}

		mapAccount.put(objAccount.Id, objAccount);

	} 
	if(mapAccount.size()>0){
		update mapAccount.values(); 
	}
}
If you have more than one member__c record per recordtype per account, and you want to sum up all the total_column__c into Account object, this trigger will not work.

As you can see, it will simply overwrite the value rather than summing it up.

Let me know how it goes.
 
sonam gupthasonam guptha

LBK,

Am getting below error,can you please have a look,but we mentioned are updating in map on line 24.

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger updatetotalAccount caused an unexpected exception, contact your administrator: updatetotalAccount: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0; first error: MISSING_ARGUMENT, Id not specified in an update call: []: Trigger.updatetotalAccount: line 28, column 1

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger updatetotalAccount caused an unexpected exception, contact your administrator: updatetotalAccount: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0; first error: MISSING_ARGUMENT, Id not specified in an update call: []: Trigger.updatetotalAccount: line 28, column 1
LBKLBK
hi Sonam,

Are there any Member__c records without Account attached to it?
Try this code instead.
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete) {

	Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
	Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;

	Map<Id, Account> mapAccount = new Map<Id, Account>();

	for(Member__c objMember : Trigger.new){
		if(objMember.lookupAccount__c != null){
			Account objAccount;
			if(mapAccount.containsKey(objMember.lookupAccount__r.Id)){
				objAccount = mapAccount.get(objMember.lookupAccount__r.Id);
			}
			else{
				objAccount = new Account();
			}
			objAccount.Id = objMember.lookupAccount__r.Id;
			if(objMember.RecordTypeId == recTypeIT){
				objAccount.ITWING__c = objMember.total_column__c;
			}
			else if(objMember.RecordTypeId == recTypeFinance){
				objAccount.Finance__c = objMember.total_column__c;
			}
			mapAccount.put(objMember.lookupAccount__r.Id, objAccount);
		}
	} 
	if(mapAccount.size()>0){
		update mapAccount.values(); 
	}
}
Let me know how it goes.

 
sonam gupthasonam guptha
LBK,

Am still seeing the same error,i have two lookups to member__c one is for Account & other is for opportunity,here i wanted to consider opportunity related account not the account lookup,and at the time of new insertion it will allocate the opporutnity automatically,but manually we need to fill account lookup on member__c.so please consider opporunity's(lookup) related account not account.And i checked with member records they are attached with account,for new member__c records we have to select manually.please check with the error once again?
LBKLBK
You can replace objMember.lookupAccount__r.Id with objMember.lookupOpportunity__r.AccountId.

Please make sure the Lookup field names are right.
sonam gupthasonam guptha
LBK,

I updated with objMember.lookupOpportunity__r.AccountId,now i cannot see error,but am not seeing any value updating on account.

Thanks!
sonam gupthasonam guptha
LBK,

I think there is something with the below lines,mapAccount never contains the lookupAccount__r value,Account dont have any relationship with member__c,so its always entering to else condition and we are not able to update,i hope we have to create a map on member__C?
 
if(mapAccount.containsKey(objMember.lookupAccount__r.Id)){
				objAccount = mapAccount.get(objMember.lookupAccount__r.Id);
			}
			else{
				objAccount = new Account();

 
LBKLBK
Haven't you replaced objMember.lookupAccount__r.Id with objMember.lookupOpportunity__r.AccountId in these cases?
sonam gupthasonam guptha
yes i replaced it on all the places where we used.but the value is not updating now.
LBKLBK
Can you post your final trigger code here?

Also to be sure about the relationship between Account, Opportunity and Member__c, Member__c is looking upto Opportunity which we use to get the Account relationship, rather than using the direct Member__c - Account relationship. Right?
sonam gupthasonam guptha
LBK,

Yes,we have account lookup and opportunity lookup on Member__c,but we have to consider lookupOpportunity__r.AccountId not accountlookup.
LBKLBK
Please post the trigger code, so that I can take a look at it.
sonam gupthasonam guptha
Hey Lbk,

Currently iam working on below code,can you please have a look its working only for one recordtype,i appreciate your quick reponse.
 
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete)
{
Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;
string ids;
for(Member__c objMember : Trigger.new){
if(objMember.lookupAccount__c != null)
ids= objMember.id;
    }
Member__c mem=[select Id,lookupAccount__c,total_column__c,RecordTypeId from Member__c where ID=:ids];
If(mem.RecordTypeId=recTypeIT)
{
opportunity opp=[Select Id,AccountId from Opportunity where Id=:mem.lookupOpportunity__c ];
Account acc=[Select Id,ITWING__c,Finance__c from Account where Id=:opp.AccountId];
for(Account aaa:acc){
aaa.ITWING__c=mem.total_column__c;
update aaa;
}
}
elseIf(mem.RecordTypeId=recTypeFinance)
{
opportunity opp1=[Select Id,AccountId from Opportunity where Id=:mem.lookupOpportunity__c ];
Account acc1=[Select Id,ITWING__c,Finance__c from Account where Id=:opp1.AccountId];
for(Account bbb:acc1){
bbb.ITWING__c=mem.total_column__c;
update bbb;
}
}

 
LBKLBK
Hi Sonam,

I have found couple of issues with your code above.

1. Between the line 6 to 9, you are picking up member__c record only if it has lookupaccount__c is not null, which is not right because you have said that it could be blank for most of the records.

2. Your string variable ids can carry only one record (probably the last record in the trigger.new list), hence your trigger will update only one account, even if the trigger is fired by a bulk update or insert operation. Your code is not bulkified.

3. adding an UPDATE statement inside a loop is not a good idea.

I have made some changes in the code I have shared earlier. I hope that this could help you.
 
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete) {

	Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
	Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;

	Map<Id, Account> mapAccount = new Map<Id, Account>();

	for(Member__c objMember : Trigger.new){
		if(objMember.lookupOpportunity__c != null){
			Account objAccount;
			if(mapAccount.containsKey(objMember.lookupOpportunity__r.AccountId)){
				objAccount = mapAccount.get(objMember.lookupOpportunity__r.AccountId);
			}
			else{
				objAccount = new Account();
			}
			objAccount.Id = objMember.lookupOpportunity__r.AccountId;
			if(objMember.RecordTypeId == recTypeIT){
				objAccount.ITWING__c = objMember.total_column__c;
			}
			else if(objMember.RecordTypeId == recTypeFinance){
				objAccount.Finance__c = objMember.total_column__c;
			}
			mapAccount.put(objMember.lookupOpportunity__r.AccountId, objAccount);
		}
	} 
	if(mapAccount.size()>0){
		update mapAccount.values(); 
	}
}
Try this code and let me know if it works for you.
 
sonam gupthasonam guptha
LBK,

Here is my current code,please have a look everything is working as expected,but after delete is not working can you please have a look?
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete)
{

		List<Account> recTypeITdeleteList = new List<Account>();
        List<Account> recTypeITUpdateList = new List<Account>();
        List<Account> recTypeFinanceList = new List<Account>();
	Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
    Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;
    
string ids;
if(Trigger.isInsert || Trigger.isUpdate)
{
for(Member__c objMember : Trigger.new){
if(objMember.lookupAccount__c != null)
ids= objMember.id;
    }
}
if(Trigger.isDelete){
for(Member__c objMember : Trigger.old){
if(objMember.lookupAccount__c != null)
ids= objMember.id;
If(mem.RecordTypeId=recTypeIT)
{
for(opportunity opp : [Select Id,AccountId from Opportunity where Id=:mem.lookupOpportunity__c ]){
for(Account acc:[Select Id,ITWING__c,Finance__c from Account where Id=:opp.AccountId]){
for(Account aaa:acc){
if(acc.ITWING__c == null){
aaa.ITWING__c=null;
}else 
aaa.ITWING__c-=mem.total_column__c;
}
}
recTypeITdeleteList.add(aaa);

    }}}}
    
for(Member__c mem :[select Id,lookupAccount__c,total_column__c,RecordTypeId from Member__c where ID=:ids]){
If(mem.RecordTypeId=recTypeIT)
{
for(opportunity opp : [Select Id,AccountId from Opportunity where Id=:mem.lookupOpportunity__c ]){
for(Account acc:[Select Id,ITWING__c,Finance__c from Account where Id=:opp.AccountId]){
for(Account aaa:acc){
if(acc.ITWING__c == null){
aaa.ITWING__c=mem.total_column__c;
}else 
aaa.ITWING__c+=mem.total_column__c;
}
}
recTypeITUpdateList.add(aaa);
}}
elseIf(mem.RecordTypeId=recTypeFinance)
{
for(opportunity opp : [Select Id,AccountId from Opportunity where Id=:mem.lookupOpportunity__c ]){
for(Account acc:[Select Id,ITWING__c,Finance__c from Account where Id=:opp.AccountId]){
for(Account bbb:acc1){
bbb.ITWING__c=mem.total_column__c;
if(acc.ITWING__c == null){
bbb.ITWING__c=mem.total_column__c;
}else 
bbb.ITWING__c+=mem.total_column__c;
}
}
recTypeITUpdateList.add(bbb);
}}

update recTypeITUpdateList;
update recTypeFinanceList;
update recTypeITdeleteList;

}}

 
sonam gupthasonam guptha
LBK,

This is what iam looking out for :--
i have a custom object member,here i need to sum-up each record type value total_column__c and print on account,everything is working as expected,but after delete is not working.
for example:—
i have account.ITWING__c = 1000
and member. total_column__c = 500
if i delete member record,account.ITWING__c = 500 should be 500.
but this is not working as expected,please help me out to resolve it.
 
LBKLBK
Hi Sonam,

When a DELETE happens, you need to reset the figure in Accounts to 0 and recalculate it for all the remaining Member__c records for that account.

Can you try this code and let me know if this helps?
 
trigger updatetotalAccount on Member__c (After Insert,After update,After Delete) {

	Id recTypeIT = [SELECT Id FROM RecordType WHERE name='ITWING' AND SObjectType='member__c' LIMIT 1].Id;
	Id recTypeFinance = [SELECT Id FROM RecordType WHERE name='Finance' AND SObjectType=‘member__c' LIMIT 1].Id;

	Map<Id, Account> mapAccount = new Map<Id, Account>();
	Set<Id> setAccountsToBeRefreshed = new Set<Id>();
	//Set<Id> setOpportunitiesToBeReferred = new Set<Id>();
	
	if(Trigger.isInsert || Trigger.isUpdate)
	{
		for(Member__c objMember : Trigger.new){
			if(objMember.lookupOpportunity__c != null){
				setAccountsToBeRefreshed.add(objMember.lookupOpportunity__r.AccountId);
				//setOpportunitiesToBeReferred.add(objMember.lookupOpportunity__c);
			}
	    }
	}
	
	if(Trigger.isDelete)
	{
		for(Member__c objMember : Trigger.old){
			if(objMember.lookupOpportunity__c != null){
				setAccountsToBeRefreshed.add(objMember.lookupOpportunity__r.AccountId);
				//setOpportunitiesToBeReferred.add(objMember.lookupOpportunity__c);
			}
	    }
	}

	List<Account> lstAccountsToBeUpdated = [SELECT Id, ITWING__c, Finance__c FROM Account WHERE ID =:setAccountsToBeRefreshed];
	List<Member__c> lstMembersToBeReferred = [SELECT Id, lookupOpportunity__c, total_column__c, RecordTypeId FROM Member__c WHERE lookupOpportunity__c IN (SELECT ID FROM Opportunity WHERE AccountId =: setAccountsToBeRefreshed)];
	
	for(Account objAccount : lstAccountsToBeUpdated){
		//Reset the numbers
		objAccount.ITWING__c = 0;
		objAccount.Finance__c = 0;
		
		for(Member__c objMember : lstMembersToBeReferred){
			if(objAccount.Id == objMember.lookupOpportunity__r.AccountId){
				if(objMember.RecordTypeId == recTypeIT){
					objAccount.ITWING__c = objAccount.ITWING__c + objMember.total_column__c;
				}
				else if(objMember.RecordTypeId == recTypeFinance){
					objAccount.Finance__c = objAccount.Finance__c + objMember.total_column__c;
				}
			}
		}
	} 
	if(lstAccountsToBeUpdated.size()>0){
		update lstAccountsToBeUpdated; 
	}
}

 
sonam gupthasonam guptha
LBK,

its not working for any event now
LBKLBK
Can you put debug statements on setAccountsToBeRefreshed, lstAccountsToBeUpdated and lstMembersToBeReferred to see what is being returned?

 
sonam gupthasonam guptha
it returns following


|USER_DEBUG|[34]|DEBUG|###lstAccountsToBeUpdated###()
[36]|DEBUG|###lstMembersToBeReferred###()
|###setAccountsToBeRefreshed###{006EXXXXXXIAS)
LBKLBK
Hi Sonam,

The id in setAccountsToBeRefreshed is an opprtunity id, not an account id (it starts with 006, which is opportunity).

Can you debug line 14 and 24 to make sure that they add Account ID not the Opp ID to the set?

Try to do system.debug on objMember.lookupOpportunity__r.AccountId before line 14 and 24.

If they do not print the Account Id, try objMember.lookupOpportunity__r.Account.Id instead of objMember.lookupOpportunity__r.AccountId.
sonam gupthasonam guptha
USER_DEBUG|[16]|DEBUG|#####objlookup########null,
i replaced with objMember.lookupOpportunity__r.Account.Id
this logs for after insert
LBKLBK
What about Update and Delete?

UPDATE  logic is  same as INSERT. And, only difference in DELETE is that it uses Trigger.old instead of trigger.new.

Insert more than one entries in Member__c, Update one of them and delete one of them to test the complete functionality,
sonam gupthasonam guptha
its not working
LBKLBK
Do you still have the debug statements on setAccountsToBeRefreshed, lstAccountsToBeUpdated and lstMembersToBeReferred?

What are the values you see in UPDATE and DELETE activities?
sonam gupthasonam guptha
Iam getting null values on 3 lists
LBKLBK
If *setAccountsToBeRefreshed* is null, all of them will be null. Is the trigger firing during updates you are making? Which field in Member__c are you updating to fire the trigger?