Filtering contact records by details held against the associated account

[realtimedespatch]

Can anyone suggest a way to list sugar contacts by account type (accounts.account_type) ?

It's such a simple requirement that I suspect it's possible from within the Sugar screens but I can't figure it out.

My immediate requirement is to provide my users with a mechanism to export the email addresses for all Sugar contacts that are associated with a particular Account Type.

The statement below shows the kind of association I'm trying to make

SELECT
	a.name,
	a.account_type,
	c.first_name,
	c.last_name,
	em.email_address
FROM accounts a
JOIN accounts_contacts ac ON ac.account_id=a.id
JOIN contacts c ON c.id=ac.contact_id
JOIN email_addr_bean_rel xr ON xr.bean_id=c.id
JOIN email_addresses em ON em.id=xr.email_address_id
WHERE account_type LIKE 'SWHouse'
ORDER BY a.name

More generally, I'd like my users to be able to apply the same "Account Type" filter to the advanced search screen when searching through contacts.

Is this something that can be done within the standard UI ?

Thanks for any suggestions.

Charlie


Version 5.1.0b (Build 4905)
Debian Etch
MySQL 5.0.32

[eggsurplus]

Charlie,

I've created a custom field type that you can add to the advanced search to search on related module information such as account_type. I'll clean it up this weekend and will let you know when it's available.

[realtimedespatch]

Wonderful, thank you

[realtimedespatch]

Eggsurplus

Thank you again for such a polished solution, it's working beautifully.


Charlie