Apex Code Development You need to sign in to do that
Don't have an account?
bpol Count Related Records with Apex Trigger
This is my first APEX coding... and, I need a bit of help.
I am trying to count the # of related records...
I'd then, like to make a field on the current record equal to this count.
The following seems to work well... except I don't know how to limit the select statement to where the related_id = the id of the current record. How do I make the variable "sid" = the id on the current record on the object Students? Also, do I need a "loop" -- if so, how would this look?
Many thanks!
trigger CountRelatedCallLogs on Student__c (before insert, before update) { Student__c[] s = Trigger.new; String sid = null; Sid = s.id; LIST <Student_Call_Log__c> log = [SELECT Id, Related_Student__c, Date__c, Note__c FROM Student_Call_Log__c clog WHERE Student_Call_Log__c.Related_Student__c = :sid ORDER BY Related_Student__c, Id DESC LIMIT 1000]; s[0].Call_Count__c = log.size (); s[0].Most_Recent_Call_Note__c = sid(); }
Rather than retrieving all the records, you'd be better to use the soql count syntax:
Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid];
There's a few other issues in the code though - a hopefully fixed version is below:
trigger CountRelatedCallLogs on Student__c (before insert, before update) {
Student__c[] s = Trigger.new;
String sid = null;
Sid = s[0].id;
Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid];
s[0].Call_Count__c = i;
// get the latest entry from student_call_log__c - not sure which of ASC/DESC is required, so may need changing
s[0].Most_Recent_Call_Note__c = [select id, Date__c from Student_Call_Log__c order by Date__c DESC limit 1][0].id;
}
This will only work for triggers where one item is changed, as it only works on the first element of trigger.new.
Bit of a tricky one to bulkify, as I don't know how you can retrieve multiple counts via a single SOQL call.
All Answers
Rather than retrieving all the records, you'd be better to use the soql count syntax:
Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid];
There's a few other issues in the code though - a hopefully fixed version is below:
trigger CountRelatedCallLogs on Student__c (before insert, before update) {
Student__c[] s = Trigger.new;
String sid = null;
Sid = s[0].id;
Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid];
s[0].Call_Count__c = i;
// get the latest entry from student_call_log__c - not sure which of ASC/DESC is required, so may need changing
s[0].Most_Recent_Call_Note__c = [select id, Date__c from Student_Call_Log__c order by Date__c DESC limit 1][0].id;
}
This will only work for triggers where one item is changed, as it only works on the first element of trigger.new.
Bit of a tricky one to bulkify, as I don't know how you can retrieve multiple counts via a single SOQL call.
Thanks! This works great...
However, I realize that this works only when the "main" record is edited; it doesn't update the count with the related records are edited or related records are added -- only when the "main" record is edited somehow. How would I change the trigger to fire when there is an update to a related record?
Here's my take on this... (which doesn't work). Any thoughts?
trigger CountRelatedCallLogs_fromLog on Student_Call_Log__c (before insert, before update) { //Student_Call_Log are the related records //Student is the "main" record //Idea is to update a field on the main record whenever an edit is made to a related record Student_Call_Log__c [] scl = Trigger.new; String sid = null; sid = scl[0].Related_Student__c; Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid]; //Not sure how to select a "main" record and then assign a value to a particular field Student__c [] s =[select id from Student_Call_Log__c where Related_Student__c = :sid]; s[0].Call_Count__c = i; }
I've added code to update the master - however, as this is running from a before insert trigger, you may find that the value you are inserting is not included, as its not yet in the database. Probably better to change this to an after insert/update trigger, as you aren't changing the records making up the trigger, if that makes any sense!
trigger CountRelatedCallLogs_fromLog on Student_Call_Log__c (before insert, before update) { //Student_Call_Log are the related records //Student is the "main" record //Idea is to update a field on the main record whenever an edit is made to a related record Student_Call_Log__c [] scl = Trigger.new; String sid = null; sid = scl[0].Related_Student__c; Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid]; // update the master record Student__c [] s =[select id, Call_Count__c from Student__c where Related_Student__c = :sid]; s[0].Call_Count__c = i; // write to database update s[0]; }
Thanks!!
I changed one line... such that I pull the field "id"on the Student__c object since there is no "Related_Student__c" field on the Student__c object. Also changed it to after insert, after update.
Works great!!
// update the master record
Student__c [] s =[select id, Call_Count__c from Student__c where id = :sid];
Final version:
trigger CountRelatedCallLogs_fromLog on Student_Call_Log__c (after insert, after update) { //Student_Call_Log are the related records //Student is the "main" record //Idea is to update a field on the main record whenever an edit is made to a related record Student_Call_Log__c [] scl = Trigger.new; String sid = null; sid = scl[0].Related_Student__c; Integer i = [select count() from Student_Call_Log__c where Related_Student__c = :sid]; // update the master record Student__c [] s =[select id, Call_Count__c from Student__c where id = :sid]; s[0].Call_Count__c = i; // write to database update s[0]; }
Bpol
I am after a similar thing. Can you tell me the custom fields you have for this code?
I presume you have "Student_c", "Call_count_c" and "Related_Student_c"
Thanks
i observed that the code is working fine to count no.of related list records.but i am trying to enhance this trigger.because trigger we had doesnt work if we delete a record in related list.it means if the count is 3 after deleting one related list record the count is still showing 3 instead of 2.could any one give the logic for that..
manythanks
Here's my take on it...should be bulkified and work on deletes:
trigger CountRelatedCallLogs_fromLog on Student_Call_Log__c (after insert, after update, after delete) { Set<Id> ids = new Set<Id>(); if(trigger.isDelete){ ids.addAll(trigger.oldMap.keySet()); } else{ ids.addAll(trigger.newMap.keySet()); } AggregateResult[] counts = [SELECT Related_Student__c,count(id)calls FROM Student_Call_Log__c WHERE Related_Student__c IN :ids GROUP BY Related_Student__c]; List<Student__c> toUpdate = new List<Student__c>(); for(AggregateResult ar : counts){ Student__c tempStudent = new Student__c(Id=string.valueof(ar.get('Related_Student__c')),Call_Count__c=integer.valueof(ar.get('calls'))); toUpdate.add(tempStudent); } update toUpdate; }This is untested, so there may be typos...
trigger dealresponses on opportunity ( before delete,after update,after insert)
{
if((trigger.isupdate&&trigger.new[0].campaign__C!=null)||(trigger.isinsert&&trigger.new[0].campaign__c!=null)){
opportunity [] opp = Trigger.new;
String op = null;
op = opp[0].campaign__c;
Integer i = [select count() from opportunity where campaign__c = :op];
Campaign s =[select id, total_opp_responses__c,total_prospects__C from Campaign where id = :op];
s.total_opp_responses__c = i;
update s;
list <Opportunity> lstOpp = [select Id, StageName from Opportunity where campaign__c = : trigger.new[0].campaign__c];
integer k=0;
for(Integer j = 0; j < lstOpp.size(); j++)
{
if(lstOpp[j].StageName =='prospecting')
k++;
} s.total_prospects__c=k;
update s;
}
if(trigger.isupdate&&(trigger.old[0].campaign__C!=null||(trigger.new[0].campaign__C==null&&trigger.old[0].campaign__C!=null))){
opportunity [] oppold = Trigger.old;
String opold = null;
opold = oppold[0].campaign__c;
Integer i = [select count() from opportunity where campaign__c = :opold];
Campaign s =[select id, total_opp_responses__c from Campaign where id = :opold];
s.total_opp_responses__c = i;
update s;
list <Opportunity> lstOpp = [select Id, StageName from Opportunity where campaign__c = : trigger.old[0].campaign__c];
integer k=0;
for(Integer j = 0; j < lstOpp.size(); j++)
{
if(lstOpp[j].StageName =='prospecting')
k++;
} s.total_prospects__c=k;
update s;
}
if(trigger.isdelete&&trigger.old[0].campaign__c!=null)
{
Campaign s =[select id, total_opp_responses__c from Campaign where id = :trigger.old[0].campaign__C];
decimal i=s.total_opp_responses__c;
for(opportunity order :trigger.old)
{
i=i -1;
}
s.total_opp_responses__c=i;
update s;
list <Opportunity> lstOpp = [select Id, StageName from Opportunity where campaign__c = : trigger.old[0].campaign__c];
integer k=0;
for(Integer j = 0; j < lstOpp.size(); j++)
{
if(lstOpp[j].StageName =='prospecting')
k++;
} s.total_prospects__c=k;
update s;
}}
hi david thanks for reponding me...i solved the issue which i was posted ....by using above trigger.but i had one more issue i.e i need to aggregate or total of all the premium__c values in the related lists.ould you give me any code using aggregate sum() function.
note:premium__c is the custom field in related lists opportunity.
Can this apex trigger be adjusted to count the activities in an account or lead?
The short answer is yes - you could summarize the count of leads onto the related account by basically using the same "bulkified" code that David81 used.
The code can be adapted for Accounts and Leads since Leads are related to their parent account via the field "PartnerAccount" on the Lead Object. You may want to eventually add additional filters such that you only include the current leads, for example, where Status = 'Open'.
Thanks for the code. Works great for my purposes as well.
What is Related_Student__c????Is it the API name of the related list object or a field name????
Its the API name of a lookup field.
I am trying to use some code from this post. I am trying to write a trigger that allows for bulk processing of counting attachments associated to a record based on the record change(not addition of the attachment). Below is my code that compliles, but I get a runtime error "Apex trigger WasteProfileAttachmentCount caused an unexpected exception, contact your administrator: WasteProfileAttachmentCount: execution of BeforeUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id a0fa0000006j8IAAAY; first error: SELF_REFERENCE_FROM_TRIGGER, Object (id = a0fa0000006j8IA) is currently in trigger WasteProfileAttachmentCount, therefore it cannot recursively update itself: []: Trigger.WasteProfileAttachmentCount: line 30, column 1"
I understand that I am not supposed to explicitly call update, on the same record. But how can I update in bulk....below is the code.
trigger WasteProfileAttachmentCount on ProfileDocument__c (before update) {
Set<Id> ids = new Set<Id>();
ids.addAll(trigger.newMap.keySet());
AggregateResult[] counts = [SELECT ParentId,count(id)attachments FROM Attachment WHERE ContentType = 'application/pdf' AND ParentId IN :ids GROUP BY ParentId];
List<ProfileDocument__c> toUpdate = new List<ProfileDocument__c>();
for(AggregateResult ar : counts){
ProfileDocument__c tempDocs = new ProfileDocument__c(Id=string.valueof(ar.get('ParentId')),Attachments__c=integer.valueof(ar.get('attachments')));
toUpdate.add(tempDocs);
}
update toUpdate;
}
The way you update records in a before trigger is simply to change those in trigger.new - they haven't been written to the database yet, so you can simply change/add fields as you wish.
In this case I'd say you need to store the aggregate results in a map keyed by parent id, then iterate the ProfileDocument__c records and retrieve the map entry matching the record id.
set<id> ids= new set<id>();
if(trigger.isDelete){
}
else{
for(contact c: trigger.new){
//ids.addAll(trigger.newMap.accountid);
ids.add(c.accountid);
}
}
AggregateResult[] counts =[select accountid, count(id) calls
from contact
where accountid in : ids
group by accountid];//
list<Account> acclist= new list<Account>();
for(AggregateResult ar : counts){
account tempStudent = new account(Id=string.valueof(ar.get('accountId')),Total_jobs__c=integer.valueof(ar.get('calls')));
acclist.add(tempStudent);
}
update acclist;
}