Monday, 14 January 2013

Filter by related module's field

At times we have clients asking for similar requirement, that they want to filter results by related module's field.

Lets take basic modules and achieve similar goal by coding. Here, one needs to have idea about creating basic mysql query.

We have Accounts and Contacts module in SugarCRM. Accounts has one-many relationship with Contacts. We will add Accounts module's type field in Contacts search.


Create non-db field in Contacts module named as account_type_search. Write following code into custom/Extensions/modules/Contacts/Ext/Vardefs/<anyname>.php

<?php
 $dictionary["Contact"]["fields"]["account_type_search"]= array(
   'name' => 'account_type_search',
   'vname' => 'LBL_ACCOUNT_TYPE_SEARCH',
   'query_type' => 'default',
   'source' => 'non-db',
   'type' => 'enum',
   'options' => 'account_type_dom',
   'studio' => array('searchview'=>true,'visible'=>false),
);

Write following code into search field definition. Copy modules/Contacts/metadata/SearchFields.php to custom/modules/Contacts/metadata/SearchFields.php if doesn't exist and write following code into it. If it exists at custom/modules/Contacts/metadata/SearchFields.php then add following code with <?php.

'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})',
        ),


Create custom/modules/Contacts/metadata/metafiles.php and write following code into it. If it exists at custom/modules/Contacts/metadata/metafiles.php, then do changes as per your needs.


<?php
$metafiles['Contacts']['searchfields']='custom/modules/Contacts/metadata/SearchFields.php';

Add account_type_search field for searching into custom/modules/Contacts/metadata/searchdefs.php through studio.

Do "Quick Repair And Rebuild" from admin.

And voila!

5 comments:

  1. Hi! It works great. Thank you very much, this is exactly what I've been looking for.

    Cheers,
    Timi

    ReplyDelete
  2. hi thanks for the post. this is great. Do you have an idea on how to implement the same methodology for a dashlet filter criteria

    ReplyDelete
    Replies
    1. Didn't had a chance to look in to it, But an idea is, you can have your own search tpl and then make a custom query to filter records

      Delete
    2. I have been avoiding the tpl and managing everything through the php on the server side. any good link to share to get started on sugcrm template? thx!

      Delete
    3. http://urdhva-tech.blogspot.in/2013/02/closed-opportunities.html This may help!

      Delete

Urdhva Tech Pvt. Ltd. Powered by Blogger.

Urdhva Tech

My photo
SugarCRM Consultants Contact us at contact@urdhva-tech.com Skype : urdhvatech

Search This Blog

Stay connected Socially

     

Total Pageviews

Popular Posts