CRM Advanced Find to Show Customer that I Have Tried to ‘Send Email To’, but ‘Still Pending’ or ‘Failed’.

I have a requirement and also found in the Dynamics Community Form, how to see the list of Customer + their email address that have been sent email to them, but they still have not received yet.
This can be done using SQL Query, but this time I’d show you using Advanced Find, and you cannot use Email entity + Regarding field as reference because it is possible that the Email regarding field can be other that Contact/Account, but can be related Case, but you send the Email to the Customers, not to the Case, right



And here is the Advanced Find Query to List all Customer + Email Address that in the list of attempted sending Email, but still Failed or Pending.
*Luckily, we have the relationship, so-called Activities Parties that we can use to do query.



*In the Participation Type, you can also include those who you are CC-ed or even the Sender, not only to list those you tried to Send to.
*You can change also the Status that you want to see.
And here is the result..
You can see that one of the Contact you attempted to send, even does not have email address.
Or you can see the Nancy Anderson email address is invalid, someone_c@example.com, how come you can send email to this dummy email ? , please ask your AM or Salesperson to update this

And you also can use this Advanced Find method to query about Phone Call (Call with whom), Appointment, and another Type, just change the ‘Activity (Email)’ to any Activity Type you want.

Hope this can help you!
Thank you and good night!

How to Update Activity Entity Record When Completed/Canceled Using CRM SDK C#

As we know, in the activity entity, after you completed or close/canceled an activity record, you cannot modify any value of this activity record. Well, it happened to any entity and you might need to re-open it first. But, sometimes, you need to update some fields when you cancel or complete activities, such as you want to give additional description, you want to save field based on the regarding field, or you want to add some calculation inside when you close any activities.

So, here we are, this is the code to update field when you close the activity, you need a plugin.

public void Execute(IServiceProvider serviceProvider)
        {
            #region must to have

            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

            // Create service with context of current user
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

            //create tracing service
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            #endregion

            string strStatus = string.Empty;

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {

                //create entity context
                Entity entity = (Entity)context.InputParameters["Target"];

                if (entity.LogicalName != TFP.Xrm.Contoso.Task.EntityLogicalName)

                    return;

                try
                {
                    if (entity.Contains("statecode"))
                    {
                        //Update the description
                        strStatus = GetStateCodeText(service, entity.LogicalName, "statecode", 
                            ((OptionSetValue)entity["statecode"]).Value);
                        
                        //update the description field
                        entity["description"] = String.Format("{0} on {1}", strStatus, DateTime.Now.ToString("dd/MM/yyyy"));
                    }
                }
                catch (Exception ex)
                {
                    throw new InvalidPluginExecutionException(ex.Message);

                }

            }
        }

The most notable is this line:

if (entity.Contains("statecode"))
{


}

You can have your own logic, in my scenario is I want to update the description field in Task entity to capture the status (in text) + the time, I am sure that you guys have your own scenario , for example update another field based on the status and so one.

And the most important thing is you need to register this plugin onPreUpdate

Result:

See the updated Description field below.

Hope this helps!

Thanks.

All About Activity Entity Query Full Guidance in CRM 2011/2013 : Basic–Complex (ActivityPointer, ActivityParty) Queries in One Post

Are you in charge to create reports involving Activity entity?

Or you also have requirement to show Activities with participated Customer or Potential Customer? As far as Activity entity in CRM is concerned, we know that this will not be easy to do query to retrieve Activities List plus the participated Customer since they are not stored in the same entity and in the Activity entity, it has no direct relationship to Account or Contact, and when you take a look deeper, you will be understanding that this is a complex relationship.

We know that activity entity is stored in the FilteredActivityPointer, but from that view, we know that no contact nor account is having relationship with that view. The fact is, the relationship between Activity and Contact/Account/Lead/User are actually involving another party, that is ActivityParty entity.

What is ActivityParty?

If we see the Activity and ActivityParty relationship description in CRM, it was written:

“Unique identifier of the activity associated with the activity party. (A "party" is any person who is associated with an activity.)”

So, I know that we need to link the Activity with the ActivityParty to get the participated related Customer record, or CRM prefers to call it “Party”.

In My Previous Post, I was talking about relationship between Activity entity and the Activity Party as well, so you might refer to that post.

Now in this post, I will talk about SQL Query to retrieve data from Activity and plus the participated parties.

Let’s start with simple query first..Better you follow the steps from beginning until the end to understand by sequence.

*I am recommending to use FilteredView to your reports.

So, enjoying read this long story…

Retrieve All Activity List (All Columns)

It is a simple query to retrieve all activities, we can use FilteredActivityPointer

select * from FilteredActivityPointer

Result:

Retrieve All Activity List (Specific Columns)

select activitytypecodename, subject, statecodename, statuscodename 
from FilteredActivityPointer

Result:

Count All Activities (by Activity Type)

select COUNT(*) as TotalRecord, activitytypecodename  as ActivityType
from FilteredActivityPointer
group by activitytypecodename

Result:

Retrieve All Activities for each Activity Type

You can also retrieve activity from individual activity type table, for example: PhoneCall, Task, Appointment, ServiceAppointment, Email.

Phone Call

select Subject, ActualEnd, ActualStart from filteredPhoneCall

Result:

Task

select Subject, ActualEnd, ActualStart from FilteredTask

Result:

Appointment

select Subject, scheduledstart, scheduledend from FilteredAppointment

Result:

Service Appointment

select Subject, scheduledstart, scheduledend from FilteredServiceAppointment

Result:

Email

select Subject, actualstart, actualend from FilteredEmail

Result:

Retrieve All Activities for each Activity Type (Custom Activity Entity)

I have created a custom activity entity, so-called: SMS

Result:

So now we know that CRM has separated tables as well to store each individual activity type, the problem is how to show all activities from all activity type (combined together) with showing fields that exists only for specific table, let’s follow this section.

Retrieve All Activities with Specific Field from Activity Type Entity

If we see in the SMS entity,

we have two custom fields: Message and Purpose.

Those two fields are only exist in the SMS entity.

We know that each activity record will be stored in the ActivityPointer entity, so to show all activities from all types we should query against this entity and get all of the standard fields, but this is not for custom fields.

but we cannot query using the FilteredActivityPointer

Instead, we should use:

select Subject, new_message, new_purposesname from FilteredNew_Sms

So, what if I want to show the message and purpose field of SMS together will all activities?

This is the query:

select ActPointer.activityid, ActPointer.Subject, 
ActPointer.activitytypecodename,
SMS.new_message, SMS.new_purposesname
from FilteredActivityPointer as ActPointer
left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid
order by ActPointer.activitytypecode 

The key is:

left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid

Result:

You can also join to another activity type:

Just add another Join

left join FilteredPhoneCall as phonecall
ON ActPointer.activityid = phonecall.activityid

Complete query:

select ActPointer.activityid, ActPointer.Subject, 
ActPointer.activitytypecodename,
SMS.new_message, SMS.new_purposesname, phonenumber
from FilteredActivityPointer as ActPointer
left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid
left join FilteredPhoneCall as phonecall
ON ActPointer.activityid = phonecall.activityid
order by ActPointer.activitytypecode 

Result:

Until know, we understand how to combine all of the activities across all activity type and showing the specific field from each Activity type, we understand that in SMS entity, the message was stored in the Message field (I am purposely not using description field), but how about email? Email, by default has description field to store the body content, and it happened to Appointment, Phone Call, Task, etc.

Now, how to combine them together into one field, let’s call the it: “Content”.

Retrieve All Activities combined Specific Fields from Activity Type Entity

SELECT actpointer.subject, actpointer.activitytypecodename,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description, SMS.new_message, ServiceAppointment.description, CampaignResponse.description) 
         as Content
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has 
--(each joint table is Optional, 
--if you need specific field from each Entity, such as Task, Phone Call, SMS, etc)

LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

order by actpointer.activitytypecodename

Result:

So, we’ve known how to retrieve al activities, so how to link to the Contact?

First, let’s we take a look about ActivityParty

Retrieve All Customers Who Participated in Any Activity

select activityid, activitypartyid, partyidname, participationtypemaskname 
from FilteredActivityParty

Result:

We have known about the ActivityPointer and ActivityPointer.

Next question: “I want to show all activities + all of the related customers, how can I achieve that?”

The answer is in this below section…

Retrieve All Activities and Related Participated Customers

The key is adding join to ActivityParty

SELECT actpointer.subject, actpointer.activitytypecodename,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 

Result:

We know that the ‘participationtypemaskname’ is the field to store the ‘To Recipient’, ‘Sender’, etc.

If you have read my previous post, it has been stated about the virtualization or masking to store the ‘From’ and ‘To’, in fact, the record value was stored in different rows for each participation type in this field: ‘participationtypemaskname’, but in CRM UI, it seems to be shown as 1 record which the participation type becomes the field name (From, To, Organizer, etc.)

Retrieve All Activities of a Specific Party

SELECT actpointer.subject, actpointer.activitytypecodename,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.partyid = @partyid

Replace ‘@partyid’ with the party id (remember, party it can be Account, Contact, Lead, SystemUser)

As we know, the SystemUser can be Resources in Service Activity or Service Appointment

For example: Replace by AccountId

SELECT actpointer.subject, actpointer.activitytypecodename, ActParty.partyid,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.partyid = '3B4CDE29-1037-E411-9454-001CC4EECDD6' --replace with accountid
--where ActParty.partyidname = 'Aileen Gusni Corp'

Result:

Retrieve All Activities of Specific Participation Type

We can also query by participation type:

SELECT actpointer.subject, actpointer.activitytypecodename, ActParty.partyid,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.participationtypemaskname in ('Sender', 'To Recipient') 

Result:

Because of its design, by joining two entity view, ActivityPointer and ActivityParty, now we can produce a report to show activities and their related parties, but we also know that Parties can be Customer, Prospect, or Resource, so how to differentiate it?

Retrieve All Activities + All Related Customers + Party Type
(Customer <Contact/Account>, Prospect <Lead>, or
Resource/Employee <CRM User>)

SELECT ActPointer.subject, ActPointer.activitytypecodename as ActivityTypeCode,
ActParty.partyidname as PartyName, 
ActParty.participationtypemaskname as ParticipationType,
EntityView.Name as PartyType
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode

Result:

*Owner and Regarding are not always limited to ActivityParty relationship since Owner can be User or Team (Team is not defined in the ActivityParty) and Regarding are related to any entity that can have Activity, so you might found the PartyName is NULL.

So, we’ve known that ActivityParty is basically an intersection table to store the relationship between ActivityPointer and Account/Contact/Lead/SystemUser. (Lead and System Users are usually people don’t care about it, but we should know that the ActivityParty can link to those entities as well).

Now, next issue, what if I want to produce report to show all activities, all related parties, and customer details, such as customer name, customer surname, and customer address.

Retrieve All Activities + All Related Customer + Customer Fields

They key is we have to join table to all of the ActivityParty Type.

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
--where partyidname = 'Aileen Gusni Corp'

Result:

Remember this join:

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid) as d

Well, we have learned how to show activities + customer + customer detail, now we can also go to next section with new challenge, filter by specific customer criteria.

Retrieve All Activities + All Related Customer + Customer Fields
by Specific Customer Criteria

select * from 
(SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid) as joinedtable
where joinedtable.CustomerCountry ='Malaysia'

The ‘where’ clause:

where joinedtable.CustomerCountry ='Malaysia'

Result:

As we know that Activity is not only involving external Party or Customer, some activities such as Phone Call, Email, Service Activity can also involve our resources, so what if we want to show All Activities by Company Resources or Employee?

Retrieve All Activities of All Company Resources

SELECT ActPointer.subject, ActPointer.activitytypecodename as ActivityTypeCode,

ActParty.partyidname as PartyName, 
ActParty.participationtypemaskname as ParticipationType,
EntityView.Name as PartyType, partyobjecttypecode
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode
where partyobjecttypecode = '8' -- 8 it is system user code

Result:

Next section, I will explain about how to retrieve specific activity list with the linked customers.

Retrieve Specific Activity Type + Related Customer

*Email or Phone Call (How to Get ‘From’ and ‘To’)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActPointer.activitytypecodename,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
and ActPointer.activitytypecode in ('4202', '4210') --Email and Phone Call

Result:

*Service Activity (Customer, Resource)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActPointer.activitytypecode ='4214' --Service Activity

Result:

*SMS – a Custom Entity (‘From’ and ‘To’ and Custom Field)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActPointer.activitytypecodename,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
and ActPointer.activitytypecodename = 'SMS'

Result:

So, guys, I believe that we have done about activities and activity party, so I am giving complex queries to summary.

There are two complex queries that you can use to show activities and of course link them to the Contact or Account or Lead or System User.

Complex Query (I)

This is the completed query by joining activitypointer, activityparty, each individual activitytype, and each individual activityparty.

SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description, SMS.new_message, ServiceAppointment.description, CampaignResponse.description) 
         as Content
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, 
--if you need specific field from each Entity, 
--such as Task, Phone Call, etc, can be used to get custom fields as well.)

LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode

Result:

You can also utilize this query to display other columns and to define selection to show by specific filter criteria, I give you two examples:

Set only to show Malaysian Customer Activities

select * from 

(SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description
         --, SMS.new_message --is a custom entity
         , ServiceAppointment.description, CampaignResponse.description) 
         as Content,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry  
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
--left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid --custom activity
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode) joinedtable

where joinedtable.CustomerCountry ='Malaysia'

Result:

Set only to show Activities to Open Lead

select * from 

(SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description
         --, SMS.new_message --is a custom entity
         , ServiceAppointment.description, CampaignResponse.description) 
         as Content,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry  ,
Coalesce(Account.statecodename, Contact.statecodename, SystemUser.isdisabledname, Lead.statecodename) as PartyStatus
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
--left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid --custom activity
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode) joinedtable

where joinedtable.PartyType ='Lead'
and joinedtable.PartyStatus = 'Open'

Result:



*Remember:

left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid

SMS is a custom entity, you might not need it.

We have done the complex query joining all of the tables that you might need, now the problem is for each participation type, will be displayed in separated rows:

How to merge the rows into columns to construct 1 record only, instead of displaying in two records or rows (1 row for ‘From’ and 1 row for ‘To’)?

Here is the answer, yes, we can use ‘Pivot’

Complex Query (II) – With Pivot

select  *
from(
SELECT 
ActPointer.ActivityId,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
participationtypemaskname in
(
 'Customer',
 'Optional attendee',
 'Organizer',
 'Owner',
 'Regarding',
 'Required attendee',
 'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)
--and actPointer.activityid = '54C8B7A1-2637-E411-9454-001CC4EECDD6' 

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer,
 [Optional attendee],
 Organizer,
 Owner,
 Regarding,
 [Required attendee],
 Resource,
 Sender,
 [To Recipient]
  )
) piv;

Result:

*Show all Roles/Participation Types

*Remember, here I give you trick to perform query based on your requirement, if you only want to show by specific activity or by specific customer then you can filter  using where ‘clause’

where partyid = '54C8B7A1-2637-E411-9454-001CC4EECDD6'

You can also filter the participation type.

To simplify the result, I will pick one activity and only to show From and To fields.

Query:

select  *
from(
SELECT 
ActPointer.ActivityId,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
participationtypemaskname in
(
 --'Customer',
 --'Optional attendee',
 --'Organizer',
 --'Owner',
 --'Regarding',
 --'Required attendee',
 --'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)
and actPointer.activityid = '54C8B7A1-2637-E411-9454-001CC4EECDD6' 

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 --Customer,
 --[Optional attendee],
 --Organizer,
 --Owner,
 --Regarding,
 --[Required attendee],
 --Resource,
 Sender,
 [To Recipient]
  )
) piv;

Result:

To achieve that, Adding these lines is the key:

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer,
 [Optional attendee],
 Organizer,
 Owner,
 Regarding,
 [Required attendee],
 Resource,
 Sender,
 [To Recipient]
  )
) piv;

You can also utilize the query based on your requirement.

If you want to filter by customer name as recipient:

select * from (
select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 --'Customer',
 --'Optional attendee',
 --'Organizer',
 --'Owner',
 --'Regarding',
 --'Required attendee',
 --'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)


) act


pivot
(
  max(CustomerName)
  for participationtype 
  in (
 --Customer,
 --[Optional attendee],
 --Organizer,
 --Owner,
 --Regarding,
 --[Required attendee],
 --Resource,
 Sender,
 [To Recipient]
  )
) piv
) as pivottable
where pivottable.[To Recipient] = 'Aileen Gusni Corp' 

Result:



I give you two other examples:

Complex Query special for Service Activity  (Service Appointment) to get the related Customer

Query:

select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 'Customer'
   -- you can filter the participation type
)

and actPointer.activitytypecode = '4214'

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer
  )
) piv

Result:



*Service Activity is equal to Service Appointment.
*Service Activity just the Activity Type, but the entity name is ServiceAppointment

Complex Query special for Phone Call and Email to get the ‘From’ and ‘To’ fields


Query:

select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 'Sender', 'To Recipient'
   -- you can filter the participation type
)

and actPointer.activitytypecode 
in 
 ('4202', 
 '4210')

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Sender, [To Recipient]
  )
) piv

Result:

Important Key:

You can add or remove each individual activity type from this joining table SQL Query:

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

*You can add FilteredNew_SMS for new custom activity entity, FilteredFax for system activity, etc..

And this for the ActivityParty

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

*You can also remove the Lead or System User if you don’t want to include them.

Now, we’re reaching the End and you have learned the secret relationship to link Activity and Customer in detail.

You can also do your own complex query and just contact me if you have any question about basic or advance query related to this post or anything.

I hope my post can help you to finish your report development and to answer all of questions about how to link Activity List with Account or Contact (or Lead or System User) relationship.

Thank you and see you!

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…