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

image

image

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.

image

image

And let’s create Appointment record..
image

image

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


image


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


image

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

image


image

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:

image

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

Select from the ActivityPointer

image

Select from the specific Activity Type, PhoneCall

image

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:

ActivityPointer:

image

As we can see I have 192 records now.

What about Phone Call?

image

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:

image

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.

image

Then, please remember those numbers.

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

image

image

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

Now, let’s analyze the Query…

ActivityPointer

image

It becomes 193 records.

PhoneCall

image

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

image

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

image

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:

image

Okay, here we go with the Query.

image

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

image

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

image

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

image

and Account.

image

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

image

We have 1 Required Attendee and 3 Optional Attendees

Let’s get the ActivityId similar to above example.

image

And get the list of the ActivityParty records.

image

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.

image

And check again the ActivityParty..

image

Yes, the Organizer was changed..

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

image

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

image

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

image

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

image

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

image

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

image

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

image

Then, the conclusion of the relationship:

image

And..

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

image

image

image

image

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:


image

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…

3 comments:

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

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

    ReplyDelete
  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?

    ReplyDelete

My Name is..