Inserting an AND clause into the oportunity list

[peterlbrown]

Hi,

I'm tryomg to fix the problem of having all the closed opportunities show up in the opportunities list. I have added a checkbox opp_closed_c that can be checked when you no longer want to see the opportunity in the decault (cleared) list.Noe I need to insert the statement "AND opportunities_cstm.opp_closed_c is null OR opportunities_cstm.opp_closed_c=0" into the default where clause for the list. I've been through opportunity.php and listview.php but I can't work out how to get it in there. Anyone know how?

Peter.

[clee]

Hi Peter,

Assuming you've added the custom field from the Studio administration module, you should not have to worry about modifying the listview code, the application will take care of that for you. You would just need to add the field to the basic or advanced search layout and search from there. modules/Opportunities/ListView.php is where you could put your custom modifications. I think the custom field portion of the query should be stored in the $where_clauses variable (returned from SearchForm.php).

-Collin

[peterlbrown]

Hi Clee,

Thanks for the info. Yes, I did add the field from Studio, but the trouble with the search form is that you can only search for the records whre the checkbox data is true. If the checkbox is not checked in the search form the search will return all records where the check box data is null, 0 or 1. I need the default behaviour to be that the query returns only records where the checkbox data is null or 0. The search form as is works nicely when I want to limit the list to records where the check box data is equal to 1.

I'll try your suggestion for putting it into the $where_clauses variable.

Thanks again,

Peter.

[jhamill]

Hi Clee,

Thanks for the info. Yes, I did add the field from Studio, but the trouble with the search form is that you can only search for the records whre the checkbox data is true. If the checkbox is not checked in the search form the search will return all records where the check box data is null, 0 or 1. I need the default behaviour to be that the query returns only records where the checkbox data is null or 0. The search form as is works nicely when I want to limit the list to records where the check box data is equal to 1.

I'll try your suggestion for putting it into the $where_clauses variable.

Thanks again,

Peter.

if you can post what worked for you I would be keen to see it and try it here as well.

john

[peterlbrown]

That didn't work.

I replaced the lines:

$where_clauses = $searchForm->generateSearchWhere($_REQUEST, true, "Opportunities");
// First change the query string value for sales_stage
$position = array_search("opportunities.sales_stage = 'Other'", $where_clauses);
if($position !== false) {
$where_clauses[$position] = "opportunities.sales_stage not in ('Closed Won', 'Closed Lost')";
// Same as: ("SELECT DISTINCT sales_stage from opportunities where sales_stage not in ('Closed Won', 'Closed Lost')")
}

with:

$where_clauses = $searchForm->generateSearchWhere($_REQUEST, true, "Opportunities");
// First change the query string value for sales_stage
$position = array_search("opportunities.sales_stage = 'Other'", $where_clauses);
if($position !== false) {
$where_clauses[$position] = " opportunities_cstm.opp_closed_c is null OR opportunities_cstm.opp_closed_c=0";
// Same as: ("SELECT DISTINCT sales_stage from opportunities where sales_stage not in ('Closed Won', 'Closed Lost')")
}

Looks like it doesn't reach this code with the default list.


Peter.