sc_select_where() or method to clear where?

Applications Grid / Reports
, , , ,
#1

I have an app that needs to use express search and dynamically filter by customer as set by parent app. The issue is using sc_select_where(add) results in this sql format.
select from tbl
where col1 like"%val%", col2 like"%val%",col3 like"%val%" and cust = [cust];

Unfortunately this format still returns all customers. I need to format like:
select from tbl
where cust = [cust] and (col1 like"%val%", col2 like"%val%",col3 like"%val%");

While this returns the correct value my function cannot get sc to query this way.

My func:
if (empty({sc_where_current}))
{
sc_select_where(add) = " where cust_id = “.$usr_num.”";
}
else
{
// strip where str from current query
$modQ = substr({sc_where_current},6);
//trying to clear query
{sc_where_orig}="";
{sc_where_current}="";
// new query properly formatted
sc_select_where() = “WHERE cust_id = “.$usr_num.” AND (”.$modQ.")";

            // echo new query for verification.
		echo({sc_where_current});
	}

So this echos out sc_where_current as the sql string in my second example but sc does not use this query.

Is there a method similar to sc_select_where(add); that would effectively replace the select where clause with my restructured query string?
Thanks

#2

I have the same issue. Is there any solution?

#3

Hi,
if you want total flexibility, just use a global variable for your where-clause.
Fill in your SQL statement for your grid without the condition.

SELECT * FROM ‘yourtable’ WHERE [glo_where]

In the onScriptInit section create your condition.

[glo_where] = 1; //make sure this variable isn’t empty

If($something == true)
{
[glo_where] = “stick what ever you want in it”; //well, it should be something useful
}

That’s it. See the magic happen.

Later on
jsb

#4

I recommend, that you init the [glo_where] Variable with “1=1”. That’s always true and no errors …

#5

Hi Reinhard,
you’re up late, aren’t you?

jsb

#6

:cool: Thank you!

#7

Hi everyone.
First I thank you for the advice about the global variable, which sounds to me the best approach so far.
But in my case, it does not work.
I do exactly what jsbinca explained in “OnScriptInit” et my SQL looks like this “SELECT * FROM client WHERE [glo_var]”
In the “OnScriptInit”, I added “sc_alert” to check the [glo_var] value. It is set correctly. But the SQL is not taking the update into consideration and always applied the same code as the first execution.
Did I miss anything in order to make the SQL statement taking the [glo_var] modification into effect?
Thanks a lot.

#8

I found out that the Advanced search replaces the WHERE clause when a search field is typed. For example, I have a db field “client_name” and a fake added search field “lang_id” (which corresponds to a detail table with maybe several records for each client). Here is below the code I wrote to work around these issues.
I’d still would like to know why the solution jsbinca presented does not work.

if (empty({search_lang_id}) OR ({search_lang_id} == 0))
{
[glo_where] = 0;
}
else
{
[glo_where] = {search_lang_id};

if ({sc_where_current} == "")
{
	sc_select_where(add) = "WHERE (client_id in (SELECT cll.clilanlnk_client_id FROM client_language_link cll WHERE cll.clilanlnk_language_id = " . [glo_where] . "))";
}
else
{
	$sql_string = {sc_where_current};
	$word = "client_id in";
	if(strpos($sql_string, $word) !== false)
	{
		// already OK
	}
	else
	{			
		sc_select_where(add) = " AND (client_id in (SELECT cll.clilanlnk_client_id FROM client_language_link cll WHERE cll.clilanlnk_language_id = " . [glo_where] . "))";
	}
	
}

}