+ Start a Discussion
girbotgirbot 

Count Related Records using Apex Trigger

Hi all,

I have two Parent records that share the same Child record. I would like to be able to count the number of related Child records. Neither are master-detail records, hence the requirement for an Apex Trigger.

I have a working count trigger for the Parent records (included below for referece), but I suspect I need another trigger on the Child records in order for the Count trigger to fire?

The other question: An attendee (Child record) can be removed from an Event or Group without the record being deleted. A user will edit the Attendee and clear the lookup field. Is this possible to include in the same Child trigger to fire the Count Trigger on the Parent?

 

I'm fairly new to triggers :)

 

Event - Parent
Group - Parent

Attendee - Child

 

trigger CountRelatedAttendees on event_c__c (before insert, before update) {
    event_c__c[] s = Trigger.new;
    String sid = null;    
    Sid = s[0].id;

    	Integer i = [select count() from attendees__c where Event_no__c = :sid];

    s[0].Places_used__c = i;
    }

 

 

AmitSahuAmitSahu

Can the below be done on attendees ?

 

trigger UpdateRelatedAttendees on attendees__c (after insert,after update) {
    attendees__c attendeeRecord = Trigger.old;
    String sid = attendeeRecord.<Event ID>;   

    	Integer i = [select count() from attendees__c where Event_no__c = :sid];

List<event_c__c> EventList = New List<event_c__c>([Select id,Places_used__c from event_c__c where id=:sid]);

EventList[0].Places_used__c = i;
update EventList; 
    }

 This should update the Parent record..

 

Regards,

girbotgirbot

Thanks for the input!

 

I am getting the error

 

Error: Compile Error: Illegal assignment from LIST<attendees__c> to SOBJECT:attendees__c at line 2 column 5 



Apologies this may be a silly question but what is the <Event ID> part in your code? I presume this is not the Id of the event record but something APEX specific?

girbotgirbot

Actually I am getting there, the code below works now. However if the Attendee is removed from the event (so the lookup field is cleared but the Attendee record is not deleted), I get the error below:

 

UpdateRelatedAttendees: execution of AfterUpdate

 

caused by: System.ListException: List index out of bounds: 0

 

Trigger.UpdateRelatedAttendees: line 15, column 7

 

trigger UpdateRelatedAttendees on attendees__c (after insert, after update, after delete, after undelete) {

    attendees[] cy = Trigger.new;
    String Sid = null;
    Sid = cy[0].id;
    String xyid = null;
    xyid= cy[0].Event__c;


         //Count attached Delegates
         Integer i = [select count() from attendees__c where Event__c = :xyid];

         //update Parent Record
         Event__c [] s = [SELECT id, Places_used__c FROM Event__c WHERE id = :xyid];
         s[0].Places_used__c = i;

         //write to datebase
         update s[0];
}

 



mngmng

Is this close to something that you're looking for? If it's unreadable, PM me and we can talk this over, but it should be fairly readable....

 

trigger UpdateRelatedAttendeeParents on Attendees__c ( after insert, after update, after delete, after undelete )
{
	Set<Id> eventIds = new Set<Id>();
	Set<Id> groupIds = new Set<Id>();

	if( trigger.isDelete )
	{
		for( Attendees__c deletedRecord : trigger.old )
		{
			eventIds.add( deletedRecord.Event__c );
			groupIds.add( deletedRecord.Group__c );
		}
	}
	else
	{
		for( Attendees__c newRecord : trigger.new )
		{
			eventIds.add( newRecord.Event__c );
			groupIds.add( newRecord.Group__c );
		}
	}
	
	if( !eventIds.isEmpty() )
	{
		List<Event__c> eventsToUpdate = [ SELECT Id, ( SELECT Id FROM Attendees__r ) FROM Event__c WHERE Id IN :eventIds ];
		for( Event__c anEvent : eventsToUpdate )
		{
			List<Attendees__c> relatedRecords = anEvent.getSObjects( 'Attendees__r' );
			anEvent.Places_used__c = relatedRecords.size();
		}
		try
		{
			update eventsToUpdate;
		}
		catch( System.DmlException ex )
		{
			// add errors to specific new or old records here
		}
	}
	
	if( !groupIds.isEmpty() )
	{
		List<Group__c> groupsToUpdate = [ SELECT Id, ( SELECT Id FROM Attendees__r ) FROM Group__c WHERE Id IN :groupIds ];
		for( Group__c anEvent : groupsToUpdate )
		{
			List<Attendees__c> relatedRecords = anEvent.getSObjects( 'Attendees__r' );
			anEvent.Places_used__c = relatedRecords.size();
		}
		try
		{
			update groupsToUpdate;
		}
		catch( System.DmlException ex )
		{
			// add errors to specific new or old records here
		}
	}
}

 There's plenty of optimization to be done here but....without talking in more detail about the actual requirements...I don't even know if this is what you need!

girbotgirbot

Apologies for taking a couple of days to reply, but your trigger works as intended! I had to swap some object/relationship names, but otherwise I did not have to edit any of the actual code.

 

The part I could not get working was updating the Parent records when the Child was removed or deleted - but your solution has fixed that for me. Now I just have to write the Apex test code...

 

Thanks for you help, greatly appreciated :)

girbotgirbot

Ah, apologies it appears I missed something...the places used is not updating on either Parent when the Child has the related lookup fields updated to null.

mngmng

In that case you need to do some extra checks in a before trigger, getting all the ids of the parents from records that are getting their lookups set to null, and populating the sets with those as well. The counting logic is still the same.