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!
button, it will open the Associated View and now you have 2 Associated Views, CRM will not ask you which one you want to go, relationship #1 or relationship #2? In fact, CRM will redirect you to one of the associated view by default.
button, it will open the Associated View and now you have 2 Associated Views, CRM will not ask you which one you want to go, relationship #1 or relationship #2? In fact, CRM will redirect you to one of the associated view by default.
