How can I get all the activities for an account?

Question

I have a program that I have written that gets the data from Dynamics CRM 2013 online. But I am running into one issue where a query to get the activities for an account only returns a subset instead of all the activities. The query expression is this

private QueryExpression CreateActivityQuery(Guid id)
{
    QueryExpression query = new QueryExpression()
    {
        Distinct = true,
        EntityName = Cd2Sf.ActivityPointer.EntityLogicalName,
        ColumnSet = new ColumnSet(true)
    };
    query.Criteria = new FilterExpression();
    query.Criteria.AddCondition("regardingobjectid", ConditionOperator.Equal, id);
    return query;
}

Where the id is the account id. I had first tried using the activityparty where the party id was equal to the account id and then I tried using the regardingobjectid and lastly then tried the Rollup method with the extended related entities but all produce the same result.

#region Create RollupRequest
// Create RollupRequest
RollupRequest rollupRequest = new RollupRequest();
rollupRequest.Query = qexp;
rollupRequest.Target = new EntityReference("account", acct.Id);
rollupRequest.RollupType = RollupType.Extended;
#endregion Create RollupRequest

#region Execute RollupRequest
// Execute RollupRequest
RollupResponse rollupResponse = (RollupResponse)service.Execute(rollupRequest);
#endregion Execute RollupRequest

#region Show RollupResponse results
ShowActivities(rollupResponse.EntityCollection, percent);
#endregion Show RollupResponse results

Do I need to use contacts as well to get all the activities that are associated to the account, i.e. use the activityparty and match the to/from/sender/cc/bcc/etc with contacts for an account? I had tried to use the contact id as the regardingobjectid but that still does not account for all of the activities.

It appears that the web page for dynamics crm online when viewing the account and the activities that are on that page are more than what the above query gets alone.

How do I get the other activities, the ones that seem to be not directly related to the account?

score 0 · Answer 1 · edited Feb 17 '15 at 08:42

0

My original answer doesn't answer the question - I've abbreviated it here.

Sarah Champ's post on using outer joins in your fetchxml is really good: http://blogs.msdn.com/b/crminthefield/archive/2013/07/01/dynamic-activity-reporting-using-fetchxml.aspx

You have to use a FetchExpression rather than a QueryExpression, but you can use it the same way with a RetrieveMultipleRequest now.

FetchExpression example:

string fetch = "<fetch xml string>"
var query = new FetchExpression(fetch);
var request = new RetrieveMultipleRequest();
request.Query = query;
var entities = ((RetrieveMultipleResponse)service.Execute(request)).EntityCollection.Entities;
foreach (var entity in entities)
{
    Console.WriteLine(entity.GetAttributeValue<string>("subject"));
}

edited Feb 17 '15 at 08:42

shytikov

9,155
8
56
103

answered Jul 09 '14 at 05:32

Geoff

210
2
3
10

Ok I think I understand. I am missing the opportunity. Let me try it with the two outer joins to contact and opportunity using the account. – user1575248 Jul 09 '14 at 06:59
Firstly I have to say that I am completely new to dynamics. Though I can see now it's way easier to use the fetchxml in combination with the advanced find, especially the generate button on that page. So I am experimenting with the advanced find to see if can get the activities. – user1575248 Jul 09 '14 at 19:23
Update: I am now able to see where 6 out of 9 activities are coming from on my test account. Some came from the child account and it appears to rollup to the parent account. I am still left with three "opportunity close" activities. I tried following the opportunity close to the opportunity and finally to the account, but that does not seem to return the "opportunity close" activities for that test account. Also can anyone tell if the queryexpression "JoinOperator.Natural" is the same as outer on fetchxml. – user1575248 Jul 09 '14 at 21:25
JoinOperator.LeftOuter is the same one from the fetchxml. I haven't used Natural joins before but I believe they link two tables without specifying fields, any field that matches is returned so if you have a heap of records with the same timestamp... well - I'm sure there must be something they're good for. – Geoff Jul 09 '14 at 22:11
If you're only using the fetchxml in code, you can do multiple queries and add the results of both to a single List or EntityCollection. – Geoff Jul 09 '14 at 22:39
Found the last three via the potential customer field of the opportunity. Linking the opportunityclose with opportunity then with the potential customer id solved the last part to the missing activities from the test account. – user1575248 Jul 10 '14 at 08:53

How can I get all the activities for an account?

1 Answers1