Sunday, 17 February 2013

Extending List View query

Greetings!

Today I came across a forum question which asked to hide "Closed Lost" opportunities from list view.

Simple steps and you are through!

Step 1: Create or edit custom/modules/Opportunities/views/view.list.php and add following code,


<?php

require_once('include/MVC/View/views/view.list.php');

class OpportunitiesViewList extends ViewList {

    function listViewProcess() {
        global $current_user;
        $this->processSearchForm();
        if(!$current_user->is_admin) // remove this condition if you dont want admin user to view the "Closed Lost" Opportunities.
            $this->params['custom_where'] = ' AND opportunities.sales_stage <> "Closed Lost" ';
      
        if (empty($_REQUEST['search_form_only']) || $_REQUEST['search_form_only'] == false) {
            $this->lv->setup($this->seed, 'include/ListView/ListViewGeneric.tpl', $this->where, $this->params);
            $savedSearchName = empty($_REQUEST['saved_search_select_name']) ? '' : (' - ' . $_REQUEST['saved_search_select_name']);
            echo $this->lv->display();
        }
    }

}

Step 2: Refresh list view!

P.S. Possible customizations in the query are:
> custom_select
> custom_from
> custom_where
> custom_order_by
Order by will be considered as second order by field when user clicks on other field to sort records.

16 comments:

  1. Awesome, thank you for your help. It really was useful!

    Respect,
    Timi

    ReplyDelete
  2. hello ....can we to do more funcionality the list view of sugar.

    example inline editing
    http://developer.yahoo.com/yui/examples/datatable/dt_quickedit.html

    http://developer.yahoo.com/yui/examples/datatable/dt_cellediting.html

    ReplyDelete
  3. Hello AYTDesign, SugarCRM version 7 will be coming up with inline editing this year. And yes, it is possible for older versions too, which requires customizations.

    ReplyDelete
    Replies
    1. can you make customizaation for us....we have finished the order module and we like to do this.

      Delete
    2. Would more than happy to do it for you. You may contact us on skype(david__boris) for more discussion. Thank you for your interest.

      Delete
  4. Hello,

    I'm trying to change the default sort order of case module, using your function, but it is not working.
    Can you help me find what is wrong with my code:
    processSearchForm();

    $this->params['custom_order_by'] = "ORDER BY `cases`.`date_entered` DESC";

    if (empty($_REQUEST['search_form_only']) || $_REQUEST['search_form_only'] == false) {
    $this->lv->setup($this->seed, 'include/ListView/ListViewGeneric.tpl', $this->where, $this->params);
    $savedSearchName = empty($_REQUEST['saved_search_select_name']) ? '' : (' - ' . $_REQUEST['saved_search_select_name']);
    echo $this->lv->display();
    }
    }

    }

    I would appreciate your help.

    ReplyDelete
    Replies
    1. Hello,

      From what I see, custom_order_by gives you a chance to add sorting on another field. When I changed you line to following

      $this->params['custom_order_by'] = ' ,cases.date_entered DESC';

      I got the results. Check for yourself the effect of adding that custom order by.

      Delete
  5. Hi,
    I want to write a custom select query : SELECT p1.`id` FROM `contacts` as p1 INNER JOIN (SELECT first_name,count(*) as rows FROM `contacts` where deleted = 0 GROUP BY first_name HAVING count(rows) > 1) as p WHERE p.`first_name` = p1.`first_name`
    I wrote in custom_select but it merges up with contacts module default query.How can I completely overwrite it with my query

    ReplyDelete
    Replies
    1. Hi Mukta,

      From you How to Request and from your comment here, I would like you to try following lines in your code,

      $this->params['custom_from'] = ' INNER JOIN (SELECT first_name,count(*) as rows FROM `contacts` where deleted = 0
      GROUP BY first_name HAVING count(rows) > 1) as p ';

      $this->params['custom_where'] = ' AND p.`first_name` = contacts.`first_name` ';

      And tell me if it works like you expected?

      Delete
    2. Yes it did.It worked fine.Thanks a lot.
      Can you help me in finding email duplicates in Contacts module.

      Delete
  6. Hi Team,

    I am not able to short the list view according to name in accounts page , any one will help me. I already put the above code, but there is no changes . List views as it is.
    .

    ReplyDelete
    Replies
    1. Hello Debal,

      Did you mean "sort"? Can you send us screenshot and your view.list.php to blogs(at)urdhva(dash)tech(dot)com?

      Delete
  7. Hi Team,

    I've tried the code in sugarcrm 7 but it didnt worked. I'm new to sugar, can you pls help?

    C:\wamp\www\SugarPro-Full-7.1.5\custom\modules\Leads\views\view.list.php

    processSearchForm();
    //if(!$current_user->is_admin) // remove this condition if you dont want admin user to view the "Closed Lost" Opportunities.
    $this->params['custom_where'] = ' AND leads.status <> "Converted" ';

    if (empty($_REQUEST['search_form_only']) || $_REQUEST['search_form_only'] == false) {
    $this->lv->setup($this->seed, 'include/ListView/ListViewGeneric.tpl', $this->where, $this->params);
    $savedSearchName = empty($_REQUEST['saved_search_select_name']) ? '' : (' - ' . $_REQUEST['saved_search_select_name']);
    echo $this->lv->display();
    }
    }

    }

    ReplyDelete
    Replies
    1. Hello Leijem Aziz,

      SugarCRM has changed it's architecture to work with list. This will work for SugarCRM version upto 6.7.* only.


      Thanks

      Delete
  8. Hi
    i have tried to added custom where and it started to return empty list view
    $this->params['custom_where']="AND department ='OPRS'"
    how make it works
    Please help

    ReplyDelete
    Replies
    1. Hello Polina,

      Try
      $this->params['custom_where']=" AND contacts.department ='OPRS' "

      instead.

      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