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 ).

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


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:

ActivityPointer:



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…

ActivityPointer



It becomes 193 records.

PhoneCall



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

‘'54C8B7A1-2637-E411-9454-001CC4EECDD6’

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:



And..

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:




Remember:

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…