How can I retrieve a full list of account activities with the Dynamics CRM Views?

Question

Using the CRM views, is there a way to retrieve a list of all of the activities linked to a specific account?

I want it to retrieve not only those associated with the account directly, but also those associated with the account's contacts, cases, etc. I am trying to replicate the list generated when you click the Activities option from within an account.

So far I have retrieved the contacts for the account and their activities. I also noticed that CRM doesn't seem to always return what I expect. Sometimes activities related to contacts of the account are not displayed. Other times, emails / appointments that are logically related to the account but have nothing in their regardingobjectid field are shown.

I am hoping this doesn't mean creating the mother of all joins or querying each activity type separately. Particularly because I need all of the related case activities, opportunity activities, etc.

Answer 1

I've used something like this. Effectively I build a table var with all the guids of the items I want to search (in my case accounts and contacts) then I query AcitivtyParty for all activities where they are a party on the activity - then over to Activity to get the details.

Declare @account_guid varchar(200)
Select @account_guid = 'insert some guid here'

Declare @GUIDS as Table(id varchar(200), fullname varchar(200), objecttype char(2)) 
Declare @ActivityIds as Table(id varchar(200))

--grab all guids we need activities for
    Insert Into @GUIDS
    Select contactid, fullname, 'C'
    From FilteredContact
    Where accountid = @account_guid
    UNION ALL
    Select accountid, [name], 'A'
    From FilteredAccount
    Where accountid = @account_guid 

--find all activities where the account/contact are referred to
Insert Into @ActivityIds
Select activityid
From FilteredActivityParty fap
Join @GUIDS g on g.id=fap.partyid
Group By activityid

Select *
From FilteredActivityPointer fap
Join @ActivityIds a on fap.activityid = a.id
Where statecode<>2 --hide canceled items

Answer 2

You should use Rollup request (if you're working using SDK web-service) to accomplish your task:

Rollup Action Microsoft Dynamics CRM has a powerful Rollup message that lets you open an entity instance, such as an account, and find information not only about the account's related records (opportunities, quotes, orders, invoices, contracts, and cases), but also about the account's subaccounts and the contacts' related records.

more:

TargetRollupActivityPointerByAccount Class (CrmService) Specifies the parameters needed to retrieve all activities related to the specified account.