SQL Puzzle. Removing Contacts from a Prospect List when they have no email address.

[hkphooey]

Hi all.

I've been struggling with the labyrinthine database structure of SugarCRM all afternoon and I'm going SQL blind. I wondered if anyone else had tried to do this.

So, as the title suggests, a bunch of contacts were added to a Target List. Since then the data has been tidied up and I'd like to remove all the contacts on the target list which don't have email addresses. There are 4000 people on the list, so this is clearly not a manual process, especially as the Target list module will only show 20 names at a time.

So, while it was easy to add multiple names to a target list using Mass Update and Power Prospecting, it is hard to maintain those lists.

First stop was of course through the Web Interface. I can use Advanced search to get all the people on a target list. However.
a) I can't find all those who have an empty email address and
b) I can't sort by email address in the results and
c) Even if I could get all the empty email addresses selected, there is no way of doing a mass update to remove them from a Target List.

So, database it is then ....

First I got the id of the Target list, which by the time you're in the database is called a prospect list.

SELECT * FROM `prospect_lists

Which gave me the prospect list id. A quick check then ...

SELECT * 
FROM prospect_lists_prospects
WHERE prospect_list_id ="4d850c13-7da3-5aac-b39a-4ae7fb91d055"
   AND related_type = "Contacts"
   AND deleted=0

Yup, that's the list I want. But there's no easy path from here, so what I planned to do is get all the ids of contacts with an empty email address ...

SELECT contacts.id
FROM contacts 
  LEFT JOIN email_addr_bean_rel on contacts.id=email_addr_bean_rel.bean_id 
  LEFT JOIN email_addresses on email_addr_bean_rel.email_address_id=email_addresses.id 
WHERE email_addresses.email_address IS NULL

And then use that to delete the 'No Email Address' Contacts from the table which associates contacts with prospect lists eg.

DELETE FROM prospect_lists_prospects
WHERE prospect_lists_prospects.related_id IN 
("10770f41-c3fa-9e47-d386-4a4993d8a378", ...etc...."10a6006b-fbb7-ad2a-a4ff-4a498dabeb43")

Well that looked good, except it still leaves a bunch of contacts who used to have email addresses associated with them, and which was deleted. However, the pointer in email_addr_bean_rel is still there so the email address is picked up.

I've tried limiting the query with email_addresses.deleted =1 but that doesn't work -- email addresses are not marked with deleted=1 when they're deleted (! apparently ...) and
email_addr_bean_rel.deleted = 1 doesn't help either.

Where do I go from here ...

[salesagility]

you could try installing Enhanced Search from SugarForge and then searching for all email address NOT %@% which should return all the empty addresses.

[christianknoll]

What you are looking for is a not exists clause .. you can use our reporter ... http://www.sugarforge.org/projects/kinamureporter/ .. this will allow you to do exactly that srearch witha nonexists clause without the need to understand the slightes bit about SQL ... ....

regard, christian.

http://www.kinamu.com

Hi all.

I've been struggling with the labyrinthine database structure of SugarCRM all afternoon and I'm going SQL blind. I wondered if anyone else had tried to do this.

So, as the title suggests, a bunch of contacts were added to a Target List. Since then the data has been tidied up and I'd like to remove all the contacts on the target list which don't have email addresses. There are 4000 people on the list, so this is clearly not a manual process, especially as the Target list module will only show 20 names at a time.

So, while it was easy to add multiple names to a target list using Mass Update and Power Prospecting, it is hard to maintain those lists.

First stop was of course through the Web Interface. I can use Advanced search to get all the people on a target list. However.
a) I can't find all those who have an empty email address and
b) I can't sort by email address in the results and
c) Even if I could get all the empty email addresses selected, there is no way of doing a mass update to remove them from a Target List.

So, database it is then ....

First I got the id of the Target list, which by the time you're in the database is called a prospect list.

SELECT * FROM `prospect_lists

Which gave me the prospect list id. A quick check then ...

SELECT * 
FROM prospect_lists_prospects
WHERE prospect_list_id ="4d850c13-7da3-5aac-b39a-4ae7fb91d055"
   AND related_type = "Contacts"
   AND deleted=0

Yup, that's the list I want. But there's no easy path from here, so what I planned to do is get all the ids of contacts with an empty email address ...

SELECT contacts.id
FROM contacts 
  LEFT JOIN email_addr_bean_rel on contacts.id=email_addr_bean_rel.bean_id 
  LEFT JOIN email_addresses on email_addr_bean_rel.email_address_id=email_addresses.id 
WHERE email_addresses.email_address IS NULL

And then use that to delete the 'No Email Address' Contacts from the table which associates contacts with prospect lists eg.

DELETE FROM prospect_lists_prospects
WHERE prospect_lists_prospects.related_id IN 
("10770f41-c3fa-9e47-d386-4a4993d8a378", ...etc...."10a6006b-fbb7-ad2a-a4ff-4a498dabeb43")

Well that looked good, except it still leaves a bunch of contacts who used to have email addresses associated with them, and which was deleted. However, the pointer in email_addr_bean_rel is still there so the email address is picked up.

I've tried limiting the query with email_addresses.deleted =1 but that doesn't work -- email addresses are not marked with deleted=1 when they're deleted (! apparently ...) and
email_addr_bean_rel.deleted = 1 doesn't help either.

Where do I go from here ...

[hkphooey]

Actually, no, neither of those solutions will work for me. As I mentioned earlier:

c) Even if I could get all the empty email addresses selected, there is no way of doing a mass update to remove them from a Target List.

So I'm still looking for a solution here from someone who knows how the database works.

Or maybe this is a bug. Maybe email_addresses.deleted should be tagged in this instance, when an email address is removed from a contact, and it isn't happening?