Search filter in account for Many to Many relationship

SuiteCRM Forum - English Language
#1

Hello Developers,
I’m trying to filter records bases on Many to Many relationship.
I have create M-M relationship between Account and Custom Module.
and I want to filter accounts records bases on custome modules related records.

I tried below but I got 0 result everytime.

  1. vardef file : custom\Extension\modules\Accounts\Ext\Vardefs\custom_filter.php
    $dictionary[“Account”][“fields”][“financial_account_search”]= array(
    ‘name’ => ‘account_type_search’,
    ‘vname’ => ‘LBL_FINANCIAL_ACCOUNTS’,
    ‘query_type’ => ‘default’,
    ‘source’ => ‘non-db’,
    ‘type’ => ‘link’,
    ‘width’ => ‘10%’,
    ‘default’ => true,
    ‘studio’ => array(‘searchview’=>true,‘visible’=>false),
    );

  2. Searchfield.php : custom\modules\Accounts\metadata\SearchFields.php
    ‘financial_account_search’ =>
    array (
    ‘query_type’ => ‘format’,
    ‘operator’ => ‘subquery’,
    ‘subquery’ => 'SELECT fa.sbm_financialaccount_accounts_1accounts_idb, sbm_financialaccount.name FROM sbm_financialaccount_accounts_1_c fa
    INNER JOIN sbm_financialaccount ON sbm_financialaccount.id = fa.sbm_financialaccount_accounts_1sbm_financialaccount_ida
    AND sbm_financialaccount.deleted = 0
    WHERE fa.deleted = 0 AND sbm_financialaccount.name LIKE ',
    ‘db_field’ =>
    array (
    0 => ‘id’,
    ),
    ),

Is i’m doing anything wrong?

#2

Do you get any error in the Log? That will show if the ID is passed or not and if the query is failing.

#3

I have resolved this. error was with my query.