Finding Contacts w/o any Activity

(0) Share

Report

Posted on by Tom Waite

Hello,

I have a database with tens of thousands of contacts and I would like to pick the ones out that have never had any sort of activity associated with them. Is there a way to do this?

Thanks,
Tom

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Wes Weeks 530 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

It depends what you want to do with the list. There isn't a method I'm aware of from within CRM to get this list but you can create a report/query to get at the data.

Running this against your database should get you what you need:

select contactid, fullname
from contact c
where not exists
(select 'x' from activitypointer where regardingobjectid = c.contactid)

Was this reply helpful? Yes No
Tom Waite 80 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

Worked like a charm, thank you!

Would you be able to do something similar to find accounts that do not have any history associated with them?

Was this reply helpful? Yes No
Wes Weeks 530 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

You could do something similar but it would be a little trickier. The reason is that activities rollup from a contact to their parent account if one exists. So if I have a closed task with Bob that is in Bob's history, it will also show up in the history for the account bob is associated with.

Are you trying for only items with no history? What about open activities for an account?

Was this reply helpful? Yes No
Tom Waite 80 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

Ideally I would only want accounts that have no history or open activities but if that complicates things too much, finding accounts with no history regardless of open activities would certainly do.

Was this reply helpful? Yes No
Wes Weeks 530 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

That actually makes it a little easier. The following should do it:
select accountid, name
from account
where accountid not in
(
     select accountid
     from account a
     where exists
          (select 'x' from activityparty ap where ap.partyid = a.accountid)
)

Was this reply helpful? Yes No
Tom Waite 80 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

Hi Wes,

Thanks again for the help. This time it seems that it did not filter out many of the accounts that have closed activities in their history. This may have nothing to do with anything but we did alot of record merging with our database so maybe that is causing the problem? Any other suggestions?

Thanks,
T

Was this reply helpful? Yes No
allen1 60 on at

Like (0)

Report

Re: Finding Contacts w/o any Activity

Maintaine you database in a unique way
====================================================================
allen
[url="http://www.astro4all.com"]freeastro[/url]

Was this reply helpful? Yes No

Community site session details

Finding Contacts w/o any Activity

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

Finding Contacts w/o any Activity

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates