How to query the account .result from the contacts search screen

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

I am trying to add the accounts.result field to the contacts search screen.

I Googled everywhere and have made the following custom module changes:

Searchdefs.php

  array (
    'name' => 'RATING',
    'label' => 'Rating',
	'type' => 'name',
    'default' => true,
    'width' => '10%',
  ), 	  
),

Seems to work well, the rating appears on the search screen!

Searchfields.php

‘rating’=> array(
‘query_type’=>‘default’,
‘operator’=>‘subquery’,
‘subquery’=>‘SELECT AC.contact_id FROM accounts_contacts AC INNER JOIN accounts A ON (contacts.id = AC.contact_id AND A.id = AC.account_id) WHERE A.rating LIKE’,
‘db_field’ => array(
‘id’,
),

Bummer - doesnt work, gives an error when loading the contacts module.

Any idea what is gling wrong?

Cheers
David

#2

Hi David,

Can you summarise what you are trying to achieve, as it’s not 100% clear?

Thanks,

Will.

#3

Oops,. I want to search for contacts by Account rating field.

Cheers
David

#4

Does anyone have an answer for this, it is getting quote urgent.
Cheers
David

#5

you query is wrong as I don’t see where you are getting ‘contacts.id’ from, potentially you should just omit ‘contacts.id = AC.contact_id’

#6

Hi Matt,

I fixed the query thank you.

The problem now is that the query for the field is not being called at all.

I have:
Searchdefs.php

  'rating' => 
  array (
    'name' => 'rating',
    'label' => 'Rating',
    'type' => 'name',
    'default' => true,
    'width' => '10%',
  ),

SearchFields.php

‘rating’=>
array(
‘query_type’=>‘default’,
‘operator’=>‘subquery’,
‘subquery’=>‘SELECT AC.contact_id FROM accounts_contacts AC JOIN accounts A ON A.id = AC.account_id WHERE A.rating LIKE’,
‘db_field’ =>
array(
‘id’,
),

I have set logging to debug.
If I do a search for my field plus another, I can find the query in the Sugar Log but can not see any bit for my field, only the other field.

Also, in the search screen, typing in a value to my field and search blanks out the value. Sort of like its ignoring the field.

Any ideas?

Cheers
David