Howto search with query related to accounts and opportunities?

[andreasbalzer2]

Hi,
I'd like to see all opportunities from my database whose related accounts have a specific field set to a specific value and contain another specific value in their opportunity title.
e.g.:
Show all opportunities with clubbing in the name that are related to accounts from France.

How is this possible?

Andreas

[andopes]

Hi Andreas

It is easily possible in phpMyAdmin

SELECT a.name AS account_name, o.name AS opportunity_name, o.amount, o.sales_stage
FROM accounts AS a
INNER JOIN accounts_opportunity AS ao ON a.id = ao.account_id
INNER JOIN opportunities AS o ON ao.opportunity_id = o.id
WHERE a.<some_field> = '<some_value>' AND o.name LIKE '%<some_value>%'

Replace the string into tags <> by the appropriate value.

Cheers

[andreasbalzer2]

Aem.. I'd like to see these information within sugarcrm of course I guess sales employees are not able to enter SQL queries yet

[michamamo2]

Hi,

if you have ZuckerReports you can create report for that (I did such things during the last week) ... ... and the report can be accessed by your users.

Micha