Custom searches

[DN667]

Hi all,

I'm running SugarOS 5.0f. I want to perform a search / select query on the accounts module (for example, in SQL, SELECT * FROM Accounts WHERE (Field1 = A AND Field2 < 5)). Can this be done? If so, how?

I need these results for a mail merge...

Best, thanks, Robin

[andopes]

Hi, Robin

It is possible to do, you need to follow this steps:

1. Create the file modules/Accounts/controller.php. In this file you will redefine the bean to be used in the ListView. This bean can be a copy of the Account.php, overriding the method "create_new_list_query", existing in SugarBean.
2. This method will modify the $ret_array accordingly to your needs.

Cheers

[DN667]

Hi, Robin

It is possible to do, you need to follow this steps:

1. Create the file modules/Accounts/controller.php. In this file you will redefine the bean to be used in the ListView. This bean can be a copy of the Account.php, overriding the method "create_new_list_query", existing in SugarBean.
2. This method will modify the $ret_array accordingly to your needs.

Cheers

I see.. my guess'd be I'll need to change the actual SQL query to the db? What do I use for keys? Field names?

How can I access my custom search?

[andopes]

Hi, DN667

This is an real example of the content of that function:

         $_REQUEST['month_basic'] = isset($_REQUEST['month_basic']) ? $_REQUEST['month_basic'] : date('m');
        $_REQUEST['year_basic'] = isset($_REQUEST['year_basic']) ? $_REQUEST['year_basic'] : date('Y');
        $delivery_date_start = "{$_REQUEST['year_basic']}-{$_REQUEST['month_basic']}-01";
        $day_end = date('d', mktime(0, 0, 0, $_REQUEST['month_basic'] + 1, 0, $_REQUEST['year_basic']));
        $delivery_date_end = "{$_REQUEST['year_basic']}-{$_REQUEST['month_basic']}-" . $day_end;
        $placement_id_list = "";
 
        if(isset($_REQUEST['mass']) && is_array($_REQUEST['mass']) && count($_REQUEST['mass']) > 0) {
            $placement_id_list = " AND up2_placements.id IN ('" . implode("', '", $_REQUEST['mass']) . "')";
        }
 
        $invoice_no = ($_REQUEST['action_type_basic'] == 'invoiced') ? 'NOT NULL' : 'NULL';
 
        $ret_array = array();
 
        $ret_array['select'] = "SELECT invoice_placement_summary.invoice_no AS invoice_id, up2_placements.id, up2_placements.order_number, up2_insertionorders.id AS up2_insertionorders_id, up2_insertionorders.order_number AS io_order_number, invoice_placement_summary.invoice_date AS invoice_date, invoice_placement_summary.id AS invoice_placement_summary_id, up2_placements.quantity, IFNULL(up2_insertionorders.publisher_id, '') AS publisher_id ";
 
        $ret_array['from'] = " FROM invoice_placement_summary INNER JOIN up2_placements ON invoice_placement_summary.placement_number = up2_placements.order_number INNER JOIN up2_insertionorders ON up2_placements.up2_insertionorders_id = up2_insertionorders.id INNER JOIN placements_forecasts ON up2_placements.id = placements_forecasts.placement_guid ";
 
        $ret_array['where'] = " WHERE invoice_placement_summary.invoice = 1 AND invoice_placement_summary.invoice_no IS $invoice_no AND invoice_placement_summary.invoice_start_date >= '$delivery_date_start' AND invoice_placement_summary.invoice_end_date <= '$delivery_date_end' AND up2_placements.deleted=0 AND placements_forecasts.delivery_date BETWEEN '{$delivery_date_start}' AND '{$delivery_date_end}' $placement_id_list";
 
        $ret_array['order_by'] = " GROUP BY up2_placements.id HAVING publisher_id IS NOT NULL ORDER BY up2_placements.name ASC ";
 
        return $ret_array; 
  

You can access the SearchFields by $_REQUEST or $_POST

Cheers

[kenshiro]

Hi all,

I'm running SugarOS 5.0f. I want to perform a search / select query on the accounts module (for example, in SQL, SELECT * FROM Accounts WHERE (Field1 = A AND Field2 < 5)). Can this be done? If so, how?

I need these results for a mail merge...

Best, thanks, Robin

Hi,

Enhanced Search, a plugin with many useful search options, might come in use.

It adds, among the others, numeric search oprions to int, decimal and currency search fields (FULL & DEMO version).