Creating target lists based on account data

[aigreenawalt]

I'm trying to create target lists for companies with more than $100M in sales. I can see how to create a report of the accounts, but not to get the contacts associated with the accounts. I'm sure I'm overlooking something . If someone could point me in the right direction it would be great.

Thanks

[Shon]

I have a similar need. I want to create a target list that is populated by industry. Should be simple. Include all contacts from accounts in industry X. Basically exactly what you need to do, just with a different account field.

However, as far as I can tell, this is not possible with SugarCRM as it exists today. There may be a plugin, which I am trying to find now. There was a plugin called Power Prospecting that looked like it may do this but I think it's old.. we've installed it in the past and still couldn't get this simple functionality.

We've also explored options in Sugar Studio to handle this. However, the problem seems to be that this type of functionality requires the joining of data from two or more tables which is not allowed in studio as far as I can tell.

If anyone knows how to do this, please post it. I'm at the point where this is severely limiting my use of Sugar.

-Shon

[Shon]

Just as an update, I've been able to solve my problem by querying the Sugar Database directly but not through Sugar. Also, there is a new version of Power Prospecting (http://www.sugarforge.org/projects/powerprospect/) that works with 4.5.1e. I can't install it at the moment because we have a modified sugar install. My hunch is that it will not help you though because of the account and contact info being in separate tables.

However, if you can access your database directly or via PHPmyAdmin, you can just export the data from a SQL query and import to target list. Here's the query that works for me:

select contacts.email1 FROM contacts, accounts, accounts_contacts WHERE accounts_contacts.contact_id=contacts.id and accounts_contacts.account_id=accounts.id and accounts.industry like '%web%';

This gives me a list of all contacts from accounts in the web industry. You'd have to change it to get the results you want but it's not too difficult.

We may get around to writing a plugin for this specific function. If we do I'll post it.

-Shon
The Forbin Group

[marketa]

Hi guys,
if you want to create target list based on account data,( i.e. you have an account which is an insurance company and this company is a member of i.e. "Insurance Customers" account and you would like to create a target list consisting of all the companies belonging to the "Insurance Customers" account), here's what you need to do first:
go to Account module and click on the account you want to edit. In our case it is "Insurance Customers" account.Click on "Select contacts" and then just select all those contacts according to the companies that belong to the Insurance customers" account.Then click "save"
After that you can create target based on industry, etc.
I know it's a bit complicated but it can save you lots of time later..
Especially when you are the beginning of creating your contact base..

[Markku]

Hi,

Yes, Power Prospecting works only with contacts, not accounts (at least at the moment).

Also, there is a new version of Power Prospecting (http://www.sugarforge.org/projects/powerprospect/) that works with 4.5.1e. I can't install it at the moment because we have a modified sugar install. My hunch is that it will not help you though because of the account and contact info being in separate tables.

True, that is one approach to create new target list. However this new target list and its items are not linked to your existing contact records thus there will be no history info on contacts (if and when campaign email has been sent to this particular person).

However, if you can access your database directly or via PHPmyAdmin, you can just export the data from a SQL query and import to target list. Here's the query that works for me:

To solve this issue, we've usually added new search fields to contact module. Normally (in contacts) you can search by Account name, so often we add company type and industry. Sure you can add other search fields as well.

Using new search fields we'll get list of contacts who's companies are in industry X and use Power Prospecting to add these contacts to target list in one go.

[audaciousgloop]

Hi,

To solve this issue, we've usually added new search fields to contact module. Normally (in contacts) you can search by Account name, so often we add company type and industry. Sure you can add other search fields as well.

Using new search fields we'll get list of contacts who's companies are in industry X and use Power Prospecting to add these contacts to target list in one go.

How do you do this? Can this be done in the "Studio" module? It would certainly solve a lot of problems for me. Is it also possible to search by account type, and so be able to select all "customers" and add them automatically to a target list?

I'm finding it strange that this basic CRM functionality - segmentation - is not a normal part of SugarCRM.

Would appreciate your advice.

Also - does PowerProspecting work with SugarCRM 5.0b?

Simon

[mnair]

Originally Posted by Markku
Hi,

To solve this issue, we've usually added new search fields to contact module. Normally (in contacts) you can search by Account name, so often we add company type and industry. Sure you can add other search fields as well.

Using new search fields we'll get list of contacts who's companies are in industry X and use Power Prospecting to add these contacts to target list in one go.

How do you do this? Can this be done in the "Studio" module? It would certainly solve a lot of problems for me. Is it also possible to search by account type, and so be able to select all "customers" and add them automatically to a target list?

I too have been trying to find a way to do this. Couldnt find a solution in the forums so far. Can anyone please help?

Restating the problem. "How can we search for contacts based on a particular type of account?"

Thanks in advance.

PS: I am using SugarCRM 5.1. I have tried the following:
1. Add a relate field in Contacts and add it to Advanced Search. But this gives me the list of all accounts, from which I can select just ONE account and view the contacts related to that.
2. Add an "array" value in SearchFields.php of Contacts.Nothing happened. Maybe I dint do it right.

[southwestcrm]

Hi,

To solve this issue, we've usually added new search fields to contact module. Normally (in contacts) you can search by Account name, so often we add company type and industry. Sure you can add other search fields as well.

Hi Markku - can you enlighten the community as to how you add account-level fields to the contact-level search layout.

[webnom]

I would indeed find this very useful as well. Please post some steps to follow.
Many thanks,
/Brad

[jwells5]

I answered a similar problem in another thread, but since this was the thread that got me started I thought I'd post my solution here too. Note, I haven't figured out how to add this to the Target List Contacts pop-up. Instead, the end-user is forced to find contacts using the Contacts Advanced Search and then use the features of the Power Prospecting module to add the contacts list to the desired Target List. Also, the workaround for the contacts list selection bug (Sugar bug #19107) was critical for me as well. Anyways here goes:

It took me quite a bit of trial and error, and endless hours stepping through the debugger, to figure out, but I found vardef settings that seem to work for searching Contacts using a custom field in the Accounts module. I created a new php file custom/Extentions/modules/Contacts/Ext/Vardefs/ContactsAccountSearch.php, but you could just directly put this directly in modules/Contacts/vardefs.php instead. Here's what I added:

$dictionary['Contact']['fields']['account_status_c'] = array (
	'name' => 'account_status_c',
	'rname' => 'account_status_c',
	'id_name' => 'id_c',
	'vname' => 'LBL_ACCOUNT_STATUS_C',
	'join_name'=>'accounts_cstm',
	'type' => 'relate',
	'link' => 'accounts_cstm',
	'table' => 'accounts_cstm',
	'isnull' => 'true',
	'module' => 'Accounts',
	'dbType' => 'varchar',
	'len' => '255',
	'source' => 'non-db',
	'unified_search' => false,
);	

$dictionary['Contact']['fields']['accounts_cstm'] = array (
	'name' => 'accounts_cstm',
	'type' => 'link',
	'relationship' => 'accounts_accounts_cstm',
	'link_type' => 'one',
	'source' => 'non-db',
	'vname' => 'LBL_ACCOUNT_CSTM',
	'duplicate_merge'=> 'disabled',
);

$dictionary['Contact']['relationships']['accounts_accounts_cstm'] = array (
	'lhs_module' => 'accounts_cstm', 
	'lhs_table' => 'accounts_cstm', 
	'lhs_key' => 'id_c',
	'rhs_module' => 'Accounts', 
	'rhs_table' => 'accounts', 
	'rhs_key' => 'id', 
	'relationship_type' => 'one-to-one',
);

I'm still trying to figure out how to make the search field additions completely upgrade safe (i.e. in a Extension file like above), but I just added the field to to the end of the 'advanced_search' declaration in custom/modules/Contacts/metadata/searchdefs.php :

'account_status_c' => 
    array (
          'name' => 'account_status_c',
          'default_value' => '',
          'type'=>'varchar',   // to avoid having the regular Select button for the Accounts module popup appear
),

The query building code in Sugar expects tables to have a deleted column. Rather than hack this code it seemed easier to just add a deleted field to the accounts_cstm table. From your favorite mysql client:

ALTER TABLE accounts_cstm ADD COLUMN deleted tinyint(1) DEFAULT 0;

Hope this helps someone else out!