+ Start a Discussion
sfdcFanBoysfdcFanBoy 

Count of all relevant Events for an Account?

Hi All,

 

Below is the code for Counting the no of events for a particular Account.

 

for(Account Acc: [SELECT Id,(SELECT Id,WhatId FROM Events) FROM Account WHERE Id=:'xxxxxxxxxx']){
        intMgmtEngmtCount=0;        
        for(Event e:Acc.Events){
            intMgmtEngmtCount+=1;        
        }        
}

 

But this Count doesn't include all the relevant Events.  This count includes only those Events where 'Related To' field of Event is Customer or 'Name' field contact belongs to this customer.

 

Example:

I have an Account "ABC" and a contact under it - Mr.X. I have created an event for the customer 'ABC' using the 'New Event' button in open activities related list.

 

There are 2 fields of concern in Events. One is 'Related To' and other is 'Name'.

 

For this event, I have chosen, Related To as 'ABC' account and Name as 'Mr.X'. By doing this, this event appears in Customer page itself in the Open activites related list. So, the count is 1 (as per the code and also in activities related list), which is correct.

 

If I change, the 'Related To' an opportunity say, 'XYZ', the activity still appears in 'ABC' customer page because, Mr.X belongs to 'ABC' account. But through the above Code, the count is "0" even though it appears in customer page open activities related list.

 

How do I get the count of all these? So the code has to check for Related To and Name fields and see if the opportunity or Contact belongs to that customer and then count. How can I do it through inner join ?

Best Answer chosen by Admin (Salesforce Developers) 
hitesh90hitesh90

Hi,

 

In this situation for removing duplicate you can use set which is contains unique values.

see below example.

 

set<Id> sConId = new set<Id>();
set<Id> sEventId = new set<Id>();
intMgmtEngmtCount=0;
for(Account Acc: [SELECT Id,(SELECT Id,WhatId FROM Events),(select id from contacts) FROM Account WHERE Id=:'xxxxxxxxxx']){
	for(Event ev: Acc.Events){
		sEventId.add(ev.id);	
	}
	for(Contact con: Acc.contacts){
		sConId.add(con.id);	
	}
}
for(Contact con: [SELECT Id,(SELECT Id,WhatId FROM Events) FROM Contact WHERE Id IN: sConId]){	
	for(Event ev: con.Events){
		sEventId.add(ev.id);	
	}
}
intMgmtEngmtCount += sEventId.size();

 

Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.

Thank You,
Hitesh Patel
SFDC Certified Developer & Administrator
My Blog:- http://mrjavascript.blogspot.in/

All Answers

hitesh90hitesh90

Hi Manish,

 

Try to use Following code as per your requirement.

 

for(Account Acc: [SELECT Id,(SELECT Id,WhatId FROM Events),(select id from contacts) FROM Account WHERE Id=:'xxxxxxxxxx']){
	intMgmtEngmtCount=0;
	intMgmtEngmtCount += Acc.Events.size();
	for(Contact con: Acc.contacts){
		sConId.add(con.id);	
	}
}
for(Contact con: [SELECT Id,(SELECT Id,WhatId FROM Events) FROM Contact WHERE Id IN: sConId]){	
	intMgmtEngmtCount += con.Events.size();
}

 

Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.

Thank You,
Hitesh Patel
SFDC Certified Developer & Administrator
My Blog:- http://mrjavascript.blogspot.in/

sfdcFanBoysfdcFanBoy
Hi Hitesh,

Thanks for the reply. Yes I am trying on the similar lines.

The concern with this is that an Event is being counted twice. Because the same Event exists in Customer Activity History as well as at Contact Activity History. (The event Id is same at both the places)

How do I avoid that counting the same event twice?
hitesh90hitesh90

Hi,

 

In this situation for removing duplicate you can use set which is contains unique values.

see below example.

 

set<Id> sConId = new set<Id>();
set<Id> sEventId = new set<Id>();
intMgmtEngmtCount=0;
for(Account Acc: [SELECT Id,(SELECT Id,WhatId FROM Events),(select id from contacts) FROM Account WHERE Id=:'xxxxxxxxxx']){
	for(Event ev: Acc.Events){
		sEventId.add(ev.id);	
	}
	for(Contact con: Acc.contacts){
		sConId.add(con.id);	
	}
}
for(Contact con: [SELECT Id,(SELECT Id,WhatId FROM Events) FROM Contact WHERE Id IN: sConId]){	
	for(Event ev: con.Events){
		sEventId.add(ev.id);	
	}
}
intMgmtEngmtCount += sEventId.size();

 

Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.

Thank You,
Hitesh Patel
SFDC Certified Developer & Administrator
My Blog:- http://mrjavascript.blogspot.in/

This was selected as the best answer
sfdcFanBoysfdcFanBoy

Perfecto!  Thanks a lot

 

I included the 2nd Contact for loop inside the Account for loop coz I need to store that count within the Account record itself.

 

Also, I had to check for Projects and Opportunities activity history too along with Contacts'.  Too many for loops within for loop.

 

No issues as of now. 

 

If any alternatives do let me know.  Thanks.

 

Cheers!!