Problem when trying to Filter by Related Module's Field

👨‍💻 Development Help
1

We are very keen to be able to filter by a related module’s field so were pleased to come across:

My colleague tried to add exactly this code to our our SuiteCRM installation and it didn’t work. I wonder if anybody could assist as to why.

We had tried searching for contacts whose account type was ‘Press’.

When we looked in the error log, it included the following:

…LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0 where ((contacts.id IN (SELECT accounts_contacts.contact_id FROM accounts_contacts INNER JOIN accounts ON accounts.id = accounts_contacts.account_id AND accounts.deleted = 0 WHERE accounts_contacts.deleted = 0 AND accounts.account_type IN (Press)))) AND contacts.deleted=0 ORDER BY contacts.date_entered DESC LIMIT 0,151: MySQL error 1054: Unknown column ‘Press’ in ‘where clause’

Re the bit

            accounts.account_type IN (Press))))

I would have expected it to say

            accounts.account_type IN ('Press'))))

and I wondered if that is why it is failing?

My question is, if this is the reason why it is failing, how can I add the quotes around the search text within:

‘account_type_search’ => array (
‘db_field’ =>
array (
0 => ‘id’,
),
‘query_type’ => ‘format’,
‘operator’ => ‘subquery’,
‘subquery’ => ‘SELECT accounts_contacts.contact_id FROM accounts_contacts
INNER JOIN accounts ON accounts.id = accounts_contacts.account_id AND accounts.deleted = 0
WHERE accounts_contacts.deleted = 0 AND accounts.account_type IN ({0})’,
),

We did just try adding single quotes surrounding the {0} but this broke the system.

Thank you for any help anybody could provide.

2

Have you tried like this?

'subquery' => 'SELECT accounts_contacts.contact_id FROM accounts_contacts
INNER JOIN accounts ON accounts.id = accounts_contacts.account_id AND accounts.deleted = 0
WHERE accounts_contacts.deleted = 0 AND accounts.account_type IN (\'{0}\')',
),
1 Like
3

Thank you so much for responding Cherub-chum. I’ll suggest this to my colleague and post back as soon as he’s given it a go.

4

Thank you so much Cherub-chum, my boss tried out your suggestion and it worked beautifully, you are star!