Monday, 8 September 2014

CRM 2011/2013 Deciphering the Relationship Between Activity (Activity Pointer) Activity Type, and Activity Party

Many times, clients are using Activity entity to store their real interaction with the clients, such as Phone Call, Email, Letter, Fax, etc. Task and Appointment are also part of Activity.
Now, I  am interested to do more research related to this Nice Post
(credit to my friend, Betlejuice Smile).

Let’s get started..

Now, let’s create a Phone Call record..



We have Call From and Call To fields and also Regarding Field.

If you create Phone Call record, it will also create an activity record with Activity Type = Phone Call.



And let’s create Appointment record..


Unlike Phone Call, Appointment doesn’t have Call From and Call To fields, instead, it has Required and Optional.

Similar to Phone Call, it will also create an activity record with Activity Type = Appointment


And now, let’s create a Task record..


Task is only having Regarding field.
But, once again, it will create another Activity record with Activity Type = Task



So, basically, every time the users create an activity entity record, for example: Phone Call, Appointment, Task, and also custom entity, the system will create at least two new record in the Entity/Table, those records are created to the Activity entity and Phone Call/Appointment/Task/Custom Activity Entity.

If we take a look in the database, now everyone knows how to do query to get the all Activities, we have to do Query against the ActivityPointer table/view.

select * from ActivityPointer

And we can also query against the individual Activity Type entity, such as PhoneCall, Task, Appointment, etc.

select * from PhoneCall

select * from Appointment

select * from Task

But, how to get the participation list? Such as Call From, Call To, Required/Optional Attendees, Organizer, etc.

The only thing we can get from those queries is ‘Regarding Object’ which you can only get the Regarding field, in fact, record stored in the Phone Call, Appointment are not merely about ‘Regarding field’, right?

So, where actually the fields are stored in…

We know from this entity fields, call from and call to:


But, if we do query, we cannot find in the database.

Select from the ActivityPointer


Select from the specific Activity Type, PhoneCall


Neither of those queries can work properly.

SO, how do you find the ‘From’ and ‘To’ fields? 

How CRM stores those fields?

–> Those are our big questions that you might find the answer after you read this post and my next post Smile

They are actually ‘Virtual fields’ that we cannot find in the database..

If you want to track deeper, CRM is actually having another table to store, that is  ActivityParty.

To strengthen our theory, let’s get a proof, let’s do a practical research.
Now, I am querying to count records:



As we can see I have 192 records now.

What about Phone Call?


Yes, we are only having 118 records now.

So, can conclude that the other 74 (192-118) records are not ‘Phone Call’ Type.

Okay, I don’t like to say without any proof in practice, so this is the query result:


We can get 74 records that are not ‘Phone Call’ Type.

So, we can prove that when CRM create Activity records, it will create records in two tables, that is ActivityPointerBase and PhoneCallBase.

Now, we get query from ActivityParty view.


Then, please remember those numbers.

Next, I am creating new Phone Call record under the same Account.



Now, we have 4 activity records under Aileen Gusni Corp.

Now, let’s analyze the Query…



It becomes 193 records.



It becomes 119 records.

So, it is once again proving that the records are created into two table: ActivityPointerBase and PhoneCallBase

Now, how about the ActivityParty?

I am assuming that they will add new record as well, but the question is, add +1 or add +x records?

Let’s prove it..


Wow it becomes 567 from 563.

It added 4 records, let’s investigate what are those 4 records?

ActivityParty view or FilteredActivityParty view doesn’t have modifiedon field, so to check, I am narrowing the search through the ActivityPointer that newly created..


Okay, as per our expectation, we have 4 activity records in the database.

And we can get the ActivityId of the newly created Phone Call record


So, let’s we combine with Activity Party.

Okay, basically the relationship between Activity and Activity Party is 1 to Many (1:N) Relationship:


Okay, here we go with the Query.


And yes, it gives us 4 records.

*I will give detailed query later in the next post.

And if we check, every time we create a Phone Call Record, it will create:

1 ActivityPointer record,

1 PhoneCall record, and..

4 ActivityParty records…

Let’s investigate the 4 records, why the CRM does create 4 records, instead of just creating 1 record or 2 records?

Okay, it is actually related to our big questions..

How to get the From and To fields and how CRM handles to store those fields?? And how about other fields in Appointment, Task, etc..

Let’s do some query checking..


They key field to store the From, To, Owner, etc are 'participationtypemaskname' field.

So, it will create 4 records with 4 different participation types..

But, we have the partyidname = NULL for 3 records.

Let’s we check the PartyId


Well, it is actually having value for partyid.

I believe that the NULL is because cannot get the partyidname if type = Owner and Regarding, I believe the reason is since Owner is can be User or Team and Regarding can be all entities having activities enabled, which is ActivityParty is only limited to Account, Contact, Lead, and System User.

For example, let’s see the User..


and Account.


Okay, now let’s create an Appointment and check how many records will be created in the Activity Party..


We have 1 Required Attendee and 3 Optional Attendees

Let’s get the ActivityId similar to above example.


And get the list of the ActivityParty records.


As you can see that the CRM created 7 records, as we know that we have total 5 attendees (1 required and 3 Optional), but CRM created 7, instead of only creating 5 records.

Now, let’s add the Organizer field into the form and change to another User.


And check again the ActivityParty..


Yes, the Organizer was changed..

Okay, next research, let’s add new Required Attendee..


And check in the database (I bet the it will add new record, so it will be 8 records of ActivityParty created from this Activity)..


Yes, correct..

Until now, we believe that each participation will create a new record and plus the Owner and Regarding participation as the default participation..

Now, let’s wrap this research by creating new Task (Task will not have any additional participation, right?)


And check to the database with the same method..

Based on the pattern, I believe that CRM will create records to store Owner and Regarding as well..


Because Task doesn’t have any other participation, it will only create for Owner and Regarding.

So, congratulation, we have found the origin of those multiple records stored in the ActivityParty and we have found how to get the From and To fields..

Regardless the type, every activity record will have Owner and Regarding fields and there is no way an entity record doesn’t have owner because the entity defined as Activity must have ownership type = User or Team..


Based on this research, we can find that CRM is using masking to special fields, such as Call From, Call To, Appointment Attendees, etc. What an amazing and a complex design.

You can see the list of some Participation Types:

CRM is actually storing those fields as the records stored in the ActivityParty. Each participation type (including call from, call to, required attendee, optional attendee, organizer, email from email to, etc) for each participant will reserve one record in the ActivityParty and CRM will create new record for each participation. CRM will also create record to store Owner and Regarding participation types by default.

As we know that talking about Activity-type entity in CRM is not an easy thing since it involves more than 1 table, in fact they are creating many records from many entities, and in fact they are related each other.

Phone Call, Appointment, Task, and other activity type entity are actually having N:1 Relationship to the Activity


And the Activity is having 1:N Relationship to the ActivityParty.


Then, the conclusion of the relationship:



ActivityParty has relationship with Account, Contact, Lead, System User





The related entity record will be stored in the partyid column. If the participation type of the records stored in the partyid column in the ActivityParty columns is Owner or Regarding, it will not show the partyidname unless you join to another entity.

To get the From and To fields in the database (not from UI), you need to query combining ActivityPointer and ActivityParty, you will not find those queries in the PhoneCall or ActivityPointer entities only without joining to ActivityParty.

Here is the full relationship:



Every time users create an activity record, by default CRM will create 2 records in ActivityParty, those are for Regarding and Owner, and any additional participation, such as Phone Call ‘Call From’ and Phone Call ‘Call To’ are also considered as 2 participation and so that CRM will create two additional records for each participation and participant. CRM will save the value in the ActivityParty entity and will show as virtual fields in the User Interface through the Web.

In the next post, I will give detail about ‘All about Activity type query’

Hope this article can be informative to you, friends!

Thanks and have a nice day…


  1. Thanks, this is very nice explanation how it works, great :)

  2. This article should included into SDK ! Very well thought and simplified way of explaining. Couldn't find any better article on this most complex topic of CRM. Well done and thanks for all this hard work.

  3. very good article. Can I ask a question on this, is it possible to represent this in dimensional model and if yes what will be dimension and what will be facts?

  4. This comment has been removed by the author.

  5. This comment has been removed by the author.

  6. I felt very happy while reading this site. This was really very informative site for me. I really liked it. This was really a cordial post. Thanks a lot!.

  7. I found your blog site on google and verify a couple of of your early posts. Proceed to keep up the very good operate. I just further up your RSS feed to my MSN News Reader. Looking for forward to reading more from you afterward!… online casino real money

  8. very good article. Can I ask a question on this, is it possible to represent this in dimensional model and if yes what will be dimension and what will be facts
    type C phones

  9. what a nice explanation with all these screenshots; I like your way of thinking and drilling down to the details :)


My Name is..