How to create custom filter on contact with accounts_cstm.accounttype_c

Hi All,

I tried to custom filter on Contact.

I create custom by this way:

1. Create new vardef by creating this file custom/Extension/modules/Contacts/Ext/Vardefs/accountType.php with:

$dictionary['Contact']['fields']['account_type'] = array(
    'name' => 'account_type',
    'source' => 'non-db',
    'massupdate' => false,
    'type' => 'enum',
    'options' => 'account_type_dom'
);

2. Add below code to file custom/modules/Contacts/clients/base/filters/default/default.php

'account_type' => array(
              'dbFields' => array(
                  0 => 'accounts_cstm.accounttype_c',
              ),
              'type' => 'enum',
              'vname' => 'LBL_ACCOUNTTYPE',
      ),

After Rebuild, I got the below error(see image). I really want to filter on accounts_cstm.accounttype_c (I try with accounts.account_type, it's work)

Thanks

  • Hello Gabor Meszaros,

    Just remove the accounts.cstm from your filter file and it should be working well.

    We just need to put the #fieldname into search file no need to define the #table.

    Sugar get the table from the field definition.

    'dbFields' => array(
                      0 => 'accounttype_c',
          ),


    Thanks
    Prashant
    Email : Prashant13290@gmail.com

  • Hi Prashant Patel

    It doesn't show that error. But it does not seem to work now. I checked sugarcrm log, I saw no filter apply for accounttype_c.

    SELECT contacts.salutation, case when jt1_following_link.id IS NOT NULL then 1 else 0 end following, case when jt9_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite, contacts.first_name, contacts.last_name, contacts.title, jt2_accounts.id account_id, jt2_accounts.assigned_user_id account_id_owner, jt2_accounts.name account_name, jt2_accounts.assigned_user_id account_name_owner, contacts.phone_work, jt3_assigned_user_link.first_name rel_assigned_user_name_first_name, jt3_assigned_user_link.last_name rel_assigned_user_name_last_name, jt3_assigned_user_link.created_by assigned_user_name_owner, contacts_cstm.contact_status_c, contacts.date_entered, contacts.department, contacts.do_not_call, contacts.phone_home, contacts.phone_mobile, contacts.phone_other, contacts.phone_fax, contacts.primary_address_street, contacts.primary_address_city, contacts.primary_address_country, contacts.primary_address_state, contacts.primary_address_postalcode, contacts.alt_address_country, contacts.alt_address_street, contacts.alt_address_city, contacts.alt_address_state, contacts.alt_address_postalcode, jt4_created_by_link.first_name rel_created_by_name_first_name, jt4_created_by_link.last_name rel_created_by_name_last_name, jt4_created_by_link.created_by created_by_name_owner, contacts.team_set_id, LTRIM(RTRIM(CONCAT(IFNULL(jt5_team_link.name,''),' ',IFNULL(jt5_team_link.name_2,'')))) team_name, jt5_team_link.created_by team_name_owner, jt6_modified_user_link.first_name rel_modified_by_name_first_name, jt6_modified_user_link.last_name rel_modified_by_name_last_name, jt6_modified_user_link.created_by modified_by_name_owner, case when jt7_user_sync.id IS NOT NULL then 1 else 0 end sync_contact, contacts_cstm.mktg_subscriptions_c, jt8_email_addresses_primary.email_address email, contacts.assigned_user_id, contacts.created_by, contacts.team_id, contacts.modified_user_id, contacts.id, contacts.date_modified FROM contacts LEFT JOIN sugarfavorites sf_contacts ON (sf_contacts.module = ?) AND (sf_contacts.record_id = contacts.id) AND (sf_contacts.assigned_user_id = ?) AND (sf_contacts.deleted = ?) LEFT JOIN subscriptions contacts_following ON (contacts.id = contacts_following.parent_id) AND (contacts_following.deleted = ?) AND (contacts_following.parent_type = ?) AND (contacts_following.created_by = ?) LEFT JOIN users jt1_following_link ON (jt1_following_link.id = contacts_following.created_by) AND (jt1_following_link.deleted = ?) LEFT JOIN accounts_contacts accounts_contacts ON (contacts.id = accounts_contacts.contact_id) AND (accounts_contacts.deleted = ?) AND (accounts_contacts.primary_account = ?) LEFT JOIN accounts jt2_accounts ON (jt2_accounts.id = accounts_contacts.account_id) AND (jt2_accounts.deleted = ?) LEFT JOIN accounts_cstm jt2_accounts_cstm ON jt2_accounts_cstm.id_c = jt2_accounts.id LEFT JOIN users jt3_assigned_user_link ON (contacts.assigned_user_id = jt3_assigned_user_link.id) AND (jt3_assigned_user_link.deleted = ?) LEFT JOIN users jt4_created_by_link ON (contacts.created_by = jt4_created_by_link.id) AND (jt4_created_by_link.deleted = ?) LEFT JOIN teams jt5_team_link ON (contacts.team_id = jt5_team_link.id) AND (jt5_team_link.deleted = ?) LEFT JOIN users jt6_modified_user_link ON (contacts.modified_user_id = jt6_modified_user_link.id) AND (jt6_modified_user_link.deleted = ?) LEFT JOIN contacts_users contacts_users ON (contacts.id = contacts_users.contact_id) AND (contacts_users.deleted = ?) AND (contacts_users.user_id = ?) LEFT JOIN users jt7_user_sync ON (jt7_user_sync.id = contacts_users.user_id) AND (jt7_user_sync.deleted = ?) LEFT JOIN email_addr_bean_rel contacts_email_addresses_primary ON (contacts.id = contacts_email_addresses_primary.bean_id) AND (contacts_email_addresses_primary.deleted = ?) AND (contacts_email_addresses_primary.bean_module = ?) AND (contacts_email_addresses_primary.primary_address = ?) LEFT JOIN email_addresses jt8_email_addresses_primary ON (jt8_email_addresses_primary.id = contacts_email_addresses_primary.email_address_id) AND (jt8_email_addresses_primary.deleted = ?) LEFT JOIN sugarfavorites contacts_favorite ON (contacts.id = contacts_favorite.record_id) AND (contacts_favorite.deleted = ?) AND (contacts_favorite.module = ?) AND (contacts_favorite.created_by = ?) LEFT JOIN users jt9_favorite_link ON (jt9_favorite_link.id = contacts_favorite.modified_user_id) AND (jt9_favorite_link.deleted = ?) LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id WHERE contacts.deleted = ? ORDER BY jt3_assigned_user_link.last_name ASC, contacts.id ASC LIMIT 31 OFFSET 0
    Params: {"1":"Contacts","2":"1","3":0,"4":"0","5":"Contacts","6":"1","7":0,"8":0,"9":1,"10":0,"11":0,"12":0,"13":0,"14":0,"15":0,"16":"1","17":0,"18":0,"19":"Contacts","20":1,"21":0,"22":"0","23":"Contacts","24":"1","25":0,"26":0}
    Types: {"1":2,"2":2,"3":2,"4":2,"5":2,"6":2,"7":5,"8":5,"9":5,"10":5,"11":5,"12":5,"13":5,"14":5,"15":5,"16":2,"17":5,"18":5,"19":2,"20":5,"21":5,"22":2,"23":2,"24":2,"25":5,"26":5}

    Thanks

  • Hello Gabor Meszaros,

    Can you please do repair rebuild and then clear your browser cache and check again ?

    As it seems to be working for me perfectly. Also which sugarcrm version and flavor you are using now?

    Thanks

  • Hi All,

    Prashant Patel: I tried to do your suggest. But it does not seem to work.

    Which steps should I do to custom contact filter search on accounts_cstm.accounttype_c?

    I tried to custom like this:  Filter records based on parent module's dropdown field in SugarCRM7

    (Maybe I missed some steps here. )

    Many Thanks

  • Hello Gabor Meszaros,

    There should be something wrong with filter or fields as I have checked on my side and removing the table from your code seems to be working fine.

    Please update us what went wrong if you found any solution on your side.

    Thanks

  • Hi All,

    I found the solution for this issue.

    Just change this:

    'account_type' => array(
                  'dbFields' => array(
                      0 => 'accounts_cstm.accounttype_c',
                  ),
                  'type' => 'enum',
                  'vname' => 'LBL_ACCOUNTTYPE',
          ),

    TO:

    'account_type' => array(
                  'dbFields' => array(
                      0 => 'accounts.accounttype_c',
                  ),
                  'type' => 'enum',
                  'vname' => 'LBL_ACCOUNTTYPE',
          ),

    (I guess accounts is module name)

    Cheers,