how to query phone calls in crm db?

(0) Share

Report

Posted on by Community Member

Hello - I'm trying to find out how many phone calls in my company's CRM implementation are missing Sender and Recipient info. I queried the PhoneCall table/view in the CRM database but I don't see any columns that appear to indicate Sender/Recipient. Is this info stored in a different table?

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

jlattimer 24,562 on at

Like (0)

Report

RE: how to query phone calls in crm db?

Look at the ActivityParty table - joined by the ActivityId field

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: how to query phone calls in crm db?

Thanks J - I took a look at ActivityParty but I'm not gaining any insight from it. I took a single activity ID from the PhoneCall table and queried ActivityParty which returned 2 rows. The diff between the rows are:

PartyId

PartyObjectTypeCode

ParticipationTypeMask

VersionNumber

Does this info appear to reflect an update to an activity? Also, is there a supertype indicator in ActivityPointerBase that indicates that the activity is a phone call without having to join to the PhoneCall table by ActivityId?

Was this reply helpful? Yes No
jlattimer 24,562 on at

Like (0)

Report
RE: how to query phone calls in crm db?

I don't believe there is - just because with an activities recipient field - there can be more than 1. Most of the pre-created views are joined in such a way where only a single logical record is returned.

Maybe this SQL will help get you in the right direction:

SELECT p.Subject, c.FullName FROM PhoneCall p INNER JOIN ActivityParty ap ON p.ActivityId = ap.ActivityId LEFT JOIN Contact c ON c.ContactId = ap.PartyId --2 is the entity type code for contact AND ap.PartyObjectTypeCode = 2 --OR SELECT p.Subject, ap.PartyIdName FROM PhoneCall p INNER JOIN ActivityParty ap ON p.ActivityId = ap.ActivityId

Was this reply helpful? Yes No
Suggested answer

Muhammad Adeel Javaid 5,580 on at

Like (0)

Report

RE: how to query phone calls in crm db?

I can get you started with this:

SELECT fc.firstname, FC.lastname, FAP2.activitypartyid, fap.*

FROM FilteredActivityPointer AS fap

LEFT JOIN FilteredActivityParty AS FAP2 ON fap.activityid = FAP2.activityid

RIGHT JOIN FilteredContact AS FC ON FAP2.partyid = FC.contactid

WHERE FAP.activitytypecodename = 'Phone Call'

AND FAP2.participationtypemask = 2

It's probably not what you're looking for, but it's a start. Otherwise there is a standard "neglected accounts" reports which you can download and see if you can get some useful out of

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: how to query phone calls in crm db?

what is the crm version ?

well how your company record phone calls , automatically or just users create records to log the phone call.

because standard CRM does not have automate process isn't it?

so do you have any integration done or call recording?

otherwise if user create phone call records with the details of recipient related activitypointer record hold the details. so read records from phonecall and activitypointer and get the regardingobjectid . hope this might help you..

Was this reply helpful? Yes No