Cross module search on a related module

SuiteCRM Forum - English Language Developer Collaboration
#1

Hi, I’ve created a filter on the Contacts list view that filters contacts based on a text field in the Opportunities module, it works as expected. I now need to create a filter that is based on the Opportunities Sale Stage field, a multi-select, enum type field. I currently have the following. When I run the sub-query through phpMyAdmin -> SQL, (with values AFTER the IN statement) it returns a list of contact ID’s as expected. When I try the below in my SearchFields.php file I get NO returns. Can anyone see anything that could be in error here or point me in the direction of code examples please?

Thank you for your time and patience

'opportunity_status' => 
  array (
    'query_type' => 'default',
    'operator' => 'subquery',
    'subquery' => 'SELECT opportunities_contacts.contact_id FROM opportunities_contacts INNER JOIN contacts ON contacts.id = opportunities_contacts.contact_id INNER JOIN opportunities ON opportunities.id = opportunities_contacts.opportunity_id AND contacts.deleted = 0 WHERE opportunities_contacts.deleted = 0 AND opportunities.sales_stage IN',
    'db_field' => 
    array (
     0 => 'id',
     // 0 => 'opportunities.sales_stage',
    ),
  ),
#2

Hi,

you might want to consider using extended vardefs to solve this:

https://suitecrm.com/forum/developer-help/4598-grab-fields-from-a-related-record-without-sugar-logic#17675

#3

Hey, thanks for the feedback. I’ve had some further investigation, had a look at the query being generated and found the final IN statement was/is formatted in this manner:

Snippet from SuiteCRM log file - generates SQL Syntax error
opportunities.sales_stage IN ‘Commitment_Low,Commitment_Medium%’) opportunity_status_derived))

It generates an SQL Syntax error, I think because the 2 values are NOT contained within braces after the IN operator and they both should be within their own quotation marks. I have a vague memory (that’s an age thing) of some sort of subquery format that had something like {()} as part of the subquery? Has anyone seen anything like that?

‘opportunity_status’ =>
array (
‘query_type’ => ‘default’,
‘operator’ => ‘subquery’,
‘subquery’ => ‘SELECT opportunities_contacts.contact_id FROM opportunities_contacts INNER JOIN contacts ON contacts.id = opportunities_contacts.contact_id INNER JOIN opportunities ON opportunities.id = opportunities_contacts.opportunity_id AND contacts.deleted = 0 WHERE opportunities_contacts.deleted = 0 AND opportunities.sales_stage IN’,
‘db_field’ =>
array (
//0 => ‘id’,
0 => ‘opportunities_status’,
),
),

Thanks for any pointers/assistance

#4

So the query generated by SuiteCRM should be something like this

opportunities.sales_stage IN (‘Commitment_Low’,‘Commitment_Medium%’)) opportunity_status_derived))

Does anyone know where/how the IN query is created/generated?

#5

Hi mpeet

I’m also looking for same scenario. Did you solved this issue?