+ Start a Discussion
sonam gupthasonam guptha 

help on a trigger to update standard object with the record type related values

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.

Here is the code for 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){
		if(objMember.lookupAccount__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(); 
	}
}

 
Maharajan CMaharajan C
Hi Sonam,

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];
acc.ITWING__c=mem.total_column__c;
update acc;
}
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];
acc1.Finance__c=mem.total_column__c;
update acc1;
}
}

Can you please Let me know if it works or not!!!

If it works mark this as a best answer!!!

Thanks,
​Raj
 
sonam gupthasonam guptha
maharaja,

iam getting following error :--
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 AfterInsert caused by: System.QueryException: List has no rows for assignment to SObject: Trigger. updatetotalAccount: line 30, column 1
Member__c mem=[select Id,lookupAccount__c,total_column__c,RecordTypeId from Member__c where ID=:ids];
its refering the member has to be in list i hope?can you please have a look?

 
sonam gupthasonam guptha
maharaja,

i used for loop and i cannot see the error now,but we are not able to update the values.
Maharajan CMaharajan C
Try like below with for loop:

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;
}
}
}

Let me if u face any problem!!!
sonam gupthasonam guptha
maharaja,

I made few changes to it,and its working fine,but its not wrking for after delete,can you please have a look?
sonam gupthasonam guptha
Maharaja,

And it has to rollup all the values of single recordtype ,that should work on insert,update and delete is it possible?for example if :--

ITWING = 100
ITWING = 200
Result woud bw like:--
total_column__c = 300
if we delete 100
total_column__c = 200
sonam gupthasonam guptha
hey,

here is my current working code.
 
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;
}
}

 
sonam gupthasonam guptha
Will it also taken care of summing up the values on a single recordtype?
sonam gupthasonam guptha
for example if my member__c have the different values with same recordtype,i have to count those values and place it on account.ITWING__c.thats nothing but rollup or summingup,that should work on all our events like insert,delete,update : -- please check below for more info.

member__c.ITWING(recordtype).total_column__c = 100
member__c.ITWING(recordtype).total_column__c = 200
Result woud be like:--

on account we have to sum up those values with same record type.

aaa.ITWING__c = 300
if we delete 100
aaa.ITWING__c = 200

Hope iam clear now?
Maharajan CMaharajan C
Hi Sonam,

Try the below one:

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;
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;
    }}
AggregateResult mem=[select Id,lookupAccount__c,RecordTypeId,SUM(total_column__c)sumtc from Member__c where ID=:ids GROUP BY RecordTypeId];
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=(Decimal)mem.get('sumtc');
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=(Decimal)mem.get('sumtc');
update bbb;
}
}

Let me if u face any problem!!!

If it works mark this as a best answer!!!

Thanks,
​Raj
sonam gupthasonam guptha
now am getting below compile error and afterdelete is not working at the same time.
Error: Compile Error: Field must be grouped or aggregated: Id

 
Maharajan CMaharajan C
Change Aggregateresult line like below:

AggregateResult mem=[select SUM(total_column__c)sumtc from Member__c where ID=:ids GROUP BY RecordTypeId];

The error is due to:
If you are using aggregate function in SOQL query then we have to follow two procedure
1. Either you have keep the aggregate function only in the select statement SUM(total_column__c)
2. If we including other fields as well in the select statement then we have to group by those fields in the SOQL

Let me if u face any problem!!!

If it works mark this as a best answer!!!

Thanks,
​Raj
sonam gupthasonam guptha
hey,

i tried the same yesterday itself ,its not able to find out opportunity lookup on the next line,i tried group by in the following way,but still iam seeing th same error Error: Compile Error: Invalid field lookupOpportunity__c for SObject AggregateResul
AggregateResult mem=[select Id,lookupOpportunity__c,RecordTypeId,SUM(total_column__c)sumtc from Member__c where ID=:ids GROUP BY RecordTypeId];
Please check once again!
sonam gupthasonam guptha
AggregateResult mem=[select Id,lookupOpportunity__c,RecordTypeId,SUM(total_column__c)sumtc from Member__c where ID=:ids GROUP BY RecordTypeId,lookupOpportunity__c];

 
sonam gupthasonam guptha
i tried even without id even but iam getiing the error :-- Error: Compile Error: Invalid field lookup,if i group by it with lookupOpportunity__c, iam getting an error like :-- there is no variable like mem.lookupOpportunity__c​.
 
AggregateResult mem=[select lookupOpportunity__c,RecordTypeId,SUM(total_column__c)sumtc from Member__c where ID=:ids GROUP BY RecordTypeId,lookupOpportunity__c];

sonam gupthasonam guptha
Any clues?where we are lagging?
sonam gupthasonam guptha
maharaj,

got any chance to look at my issue,i really appreciate your quick response on this,thanks
sonam gupthasonam guptha
maharaja,

i fixed out it,but on delete the trigger is not working,can you please have a look.
sonam gupthasonam guptha
please check with my current code,and iam not able to connect to your skype
 
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
maharaja,

Any clues why after delete is not working?