Community
Showing results for 
Search instead for 
Do you mean 
Reply

How to find all CONTACTS without any ACTIVITIES via SQL

Accepted Solution Solved
New Member
Posts: 13
Country: United_Kingdom
Accepted Solution

How to find all CONTACTS without any ACTIVITIES via SQL

Hi,

 

I'm accessing Act! via MS Query/ODBC.

 

Goal: Find all CONTACT records that have no ACTIVITIES.

 

I can find the activities in TBL_ACTIVITY, but this lists all activities since the birth of the database.

 

What I need to do is identify which CONTACT records have no 'live' activities, in other words, no activities right now.

 

Note: Unlike in Act!, activities remain in the TBL_ACTIVITY and do not get moved to a HISTORY table. So there must be a way to identify, which activities have been completed, and which are still live. If I can do this, I can the sort out my problem in Excel. 

 

Thanks for reading and I hope you can help.

 

Best wishes,

 

Matthew.


Accepted Solutions
Solution
Accepted by topic author MatthewButterworth
‎06-08-2017 09:11 AM
Nickel Contributor
Posts: 249
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Hi Matthew,

Try this

select * from [dbo].[TBL_CONTACT]
where TBL_CONTACT.TYPENUM <> 2 and TBL_CONTACT.CONTACTID NOT IN (select TBL_CONTACT.CONTACTID from TBL_CONTACT
inner join [dbo].[TBL_CONTACT_ACTIVITY] on TBL_CONTACT.CONTACTID = TBL_CONTACT_ACTIVITY.CONTACTID
inner join TBL_ACTIVITY on TBL_CONTACT_ACTIVITY.ACTIVITYID = TBL_ACTIVITY.ACTIVITYID and ISDELETED <> 1
AND TBL_ACTIVITY.ACTIVITYID not in (select TBL_ACCESSOR_ACTIVITY_CLEARED.ACTIVITYID from TBL_ACCESSOR_ACTIVITY_CLEARED))
order by LASTNAME

 

it seems to work OK if a new contact is added with no activities and if then you create an activity for the new contact it is then removed from the list. When the activity is completed and goes into history then the contact goes back into the list.

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: How to find all CONTACTS without any ACTIVITIES via SQL

What you want to do can be done fairly simply using Lookup | Contact Activity in the ACT! program. Creating a query or a report to do that wouldn't be easy. The activities table in the database contains all the activities ever entered, unless erased. This includes the contact activities as well as the company, group and opportunities activities. Activities are open or closed based on the cleared field in the activities table. History table entries are made based on the activity when it's cleared but the activity isn't erased. To get the contacts that don't have an activity you would need to join the contact table with the contact activity table and the activity table looking for those contacts that don;t have an uncleared activity.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 13
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Hi Roy,

 

I really appreciate you taking the time to reply to my question.

 

The reason I had gone that ODBC route was because I couldn't find a way within Act! to perform this task.

 

You say I can use the Lookup > Contact Activity in Act!, but I can't see a way to list records with null activities. 

 

Could you help point me in the right direction, please?>

 

Best wishes,

 

Matthew

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: How to find all CONTACTS without any ACTIVITIES via SQL

I went back to you original post and I'm not sure if you want to know if they don't have a present activity scheduled or if they have ever had an activity scheduled. Either way the process is similar. Go to Lookup | Contact Activity. Use the Not Changed option. For ever had an activity choose a past date for Since Date and for presently no activities use Today. Uncheck all types except Activities and All activities. This should give you what you want.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 13
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Hi Roy,

 

Thank you so much for helping me, it's very much appreciated. Thanks for providing solutions to both scenarios. 

 

The scenario I want to solve is the first one - that they do not currently have any activities set. 

 

I've followed your instructions, like so:

 

  1. Lookup
  2. Contact Activity
  3. Selected - Not Changed
  4. Changed Since Date to - Today
  5. Unchecked all fields except - Activities > All Activities

But this returns a lot of records (nearly the entire database), which both include records with activities, and without. 

 

Are you able to give it a try at your end too, please? Sorry, and thanks for all your help.

 

Best wishes,

 

Matthew.

 

n.b. We only use Contacts in Act! and not Companies - maybe this info will help?

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: How to find all CONTACTS without any ACTIVITIES via SQL

[ Edited ]
Sorry. I thought that sequence would work but it appeaser tje underlying query doesn't go forward past today. There is another way to get the information you want but it's a bit roundabout. 1) Filter the task list for everyone that has on activity. 2) Use the create lookup option to create a list of all contact WITH an activity. 3) Use Edit | Replace Data to place an X ( or other marker) in an empty field. 4) Do a lookup on the marker field for Does Not Contain Data to get a lookup of the contacts without an activity scheduled. Not very elegant but it does work.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 13
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Hi Roy,

 

Please, no need to apologise! This is something that I assumed would be simple too, but it turns out it isn't. I have used the same workaround before too; like you say, not very elegant but it does work. This is the reason why I decided to go via ODBC, so I could easily run a report to keep on top of things.

 

Going back to my original idea, can you think of a way to use ODBC and the ACTIVITIES and maybe HISTORY folders to solve this? There must be a fairly simple way to do this! Smiley Happy I hope, anyway.

 

Huge thanks, again.

 

Matthew.

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Unfortunately I don't think there is an easy answer. It presents an interesting problem. I may try to design a report in Durkin Impact Reports that would probide the information you want. I'll let you know if I come up with anything.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 13
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

I'm glad it's not just me! Smiley Happy Any help you can afford me will be deeply appreciated, Roy.  Have a great day today. Thank you.

Nickel Contributor
Posts: 249
Country: United_Kingdom

Re: How to find all CONTACTS without any ACTIVITIES via SQL

Hi Matthew,

Not sure if this would help but using a query in  Microsoft SQL Server management studio using the following brings back all the contacts in the demo database with no activities including if it has been erased and it doesn't include any secondary contacts.

 

select * from [dbo].[TBL_CONTACT]
where TBL_CONTACT.TYPENUM <> 2 and TBL_CONTACT.CONTACTID NOT IN (select TBL_CONTACT.CONTACTID from TBL_CONTACT
inner join [dbo].[TBL_CONTACT_ACTIVITY] on TBL_CONTACT.CONTACTID = TBL_CONTACT_ACTIVITY.CONTACTID
inner join TBL_ACTIVITY on TBL_CONTACT_ACTIVITY.ACTIVITYID = TBL_ACTIVITY.ACTIVITYID and ISDELETED <> 1)
order by LASTNAME