This is semi-related to my post Issue-with-sc_select_where(add)-on-a-query-containing-a-sub-query
That issue was fixed by SC not allowing you to choose virtual fields for Quick Search. Fair enough.
Now that I have a View, so that the fields I want quick searched are not virtual anymore, I have encountered what I believe to be a bug.
The grid in question basically opens the view using the main SQL as:
SELECT *
FROM view_tbltimesheets
However, the only people that should get this unfiltered view are users of group “Administrator”. All other users (group of “Users”) have a WHERE dynamically added so they can only see the records they have created (The “User” column is also hidden from normal users). This is done in the grid’s onScripInit event:
// Hide/show a field
if ([ugroup] == 'Administrator') {
sc_field_display({User}, 'on'); // Display field
} else {
sc_field_display({User}, 'off'); // Hide field
// Only show user's recs when not an admin
sc_select_where(add) = "WHERE User = '" . [usr_login] . "' ";
}
When I use quick search when logged in as an administrator everything works as one would hope. However, if I am logged in as a normal user and try a quick search, I get an error (similar to that in the previous post) - see picture:
I believe that SC malforms the query for the quick search when a “WHERE” clause has been added by the sc-select_where(add) macro. The SQL debug option does not show me the SQL that quick search generates so I cannot see exactly where the generated query fails.
As an experiment I commented out the sc-select_where(add) and manually added the WHERE to the grid’s main SQL. In that case the quick search works fine. This means I can workaround it (I believe) by using a global variable in the main SQL to do what I need, but I think it still highlights there is a bug.
EDIT: Ok - last paragraph suggested workaround fails - you can’t add a global variable into the main SQL - so it does look like you have no choice but to use the sc_select_where(add) macro - in which case this bug does halt me.
