WHERE clause doubled after sc_redir button ( ??????? )

Applications Grid / Reports
#1

Hello ,
I got a grid called “hhh_grid_products_for_order” that work nicely using a query with a final WHERE clause

SELECT
…
…
FROM products
LEFT JOIN price_list ON (products.ProductID = price_list.ProductID) AND (price_list.CustomerID = ‘[glo_CustomerID]’ )
LEFT JOIN categories ON (products.CategoryID = categories.CategoryID )
LEFT JOIN price_list_std ON (products.ProductID = price_list_std.ProductID)
WHERE (price_list_std.RefYear = YEAR(CURDATE()) )
ORDER BY price_list.UnitPrice DESC

Obviously I did every kind of test also directly on mySQL and it works as expected.

Using a button , from grid , I can access to a form to check other things. OK works.
Then to come back to grid , I created another button (go to previous grid) with this code

[glo_flag_search]=1;
sc_redir(hhh_grid_products_for_order, parm1=’[glo_CustomerID]’, “_parent”);
sc_exit();

I did also try using only sc_redir(hhh_grid_products_for_order) ; but nothing change.

When I click over the button to come back to grid I got really strange error with the WHERE clause DOUBLED .
where (price_list_std.RefYear = YEAR(CURDATE()) ) where price_list_std.RefYear = YEAR(CURDATE())

Obviously the double WHERE is not good for mysql query.
Error
Errore durante l’accesso alla banca dati:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where price_list_std.RefYear = YEAR(CURDATE())’ at line 1
select count(*) from products LEFT JOIN price_list ON (products.ProductID = price_list.ProductID) AND (price_list.CustomerID = ‘C630’ ) LEFT JOIN categories ON (products.CategoryID = categories.CategoryID ) LEFT JOIN price_list_std ON (products.ProductID = price_list_std.ProductID) where (price_list_std.RefYear = YEAR(CURDATE()) ) where price_list_std.RefYear = YEAR(CURDATE())

I really do not understand what’s happening and I’m really stuck in this from 2 days… ;-(((
PLEASE HELP ME !!!

#2

I found the issue by myself but I’m not able to fix it.

I got a WHERE into SQL and I want also to save the refined search set before to link another app in order to filter data on the same way once returned back…
I’ve discovered that the macro take also the filter within the query and so when you come back from app to grid again SC apply all filters (refined) and the where on SQL query.
That is why it takes the WHERE clause twice …

I used sc_where_current macro to record the
IF you use macro $save_current_where = [SIZE=+2]{[/SIZE][SIZE=+2]sc_where_current[/SIZE][SIZE=+2]}[/SIZE]; to store the WHERE used on refined search of grid for apply it again later SC, when you come back from linked app apply it , but it takes also the WHERE inside the SQL . See SQL down here.

SELECT
…
…
FROM products
LEFT JOIN price_list ON (products.ProductID = price_list.ProductID) AND (price_list.CustomerID = ‘[glo_CustomerID]’ )
LEFT JOIN categories ON (products.CategoryID = categories.CategoryID )
LEFT JOIN price_list_std ON (products.ProductID = price_list_std.ProductID)
[SIZE=+1]WHERE (price_list_std.RefYear = YEAR(CURDATE()) )[/SIZE]
ORDER BY price_list.UnitPrice DESC

[SIZE=+1]So now the question is … how can I store the WHERE of refined search filters and avoid to apply twice the WHERE inside the SQL when I’m back to grid ???[/SIZE]

On the button that makes link from the main GRID to app I use following code to record the refined search filter set.
SC do it normally but grid was customized very much and so SC looses the refined filters.
As workaround I created a global variable saving the {sc_where_current} before to link the app and then using a flag I re-apply it within grid OnScripInit.

Button link From GRID to FORM (go)

$save_current_where = {sc_where_current};
[glo_save_current_where_temp]= $save_current_where;
[glo_flag_search]=0;
sc_redir(form_products_selected_rfqs,parm1=’[glo_CustomerID]’);

Button link from FORM to GRID (retun back)

[glo_flag_search]=1;
sc_redir(hhh_grid_products_for_order, parm1=’[glo_CustomerID]’, “_parent”);
sc_exit(sel);

OnScriptInit of the GRID

and whiting the OnScripInit code I do apply the sc_select_where(add) to show ONLY the records filtered before using refined search.

if([glo_flag_search]==1)
{
sc_select_where(add) = [glo_save_current_where_temp];
[glo_flag_search]=0;
}