Disable search on no selection

Applications Search
#1

I have an project that uses the security module and I’ve used it also to also restrict users certain records of common tables to all users. The problem is that one of the table has many records and apply ‘WHERE’ in the ‘SQL’ based on user restrictions to the table gets many time in ScriptCase (about 1 minute for results) because if you provide a ‘WHERE’ in ScriptCase, the search applies another ‘SELECT’ to the results of the ‘SQL’.
So to access many of this tables I use a ‘Search’ before show the ‘Grid’ and apply this restriction filter to the ‘Master’ table in a ‘Double select’, so there is no need to filter the ‘Detail’ table who has many records and takes long time to this. With this, the user can only select in the those ‘Master’ records who has rights and the results gets in less than one second.
My problem is the default behavior of ScriptCase to do not apply any filter if there is no selection. So if user simply click for search without any select, shows all records even those without rights. ?How can I apply a filter to the ‘SQL’ if there is no selection on this ‘Double select’ of the ‘Master’ table or simply disable the search until at least one is selected? If I want to show all (not all in the table, all that the user has rights) the user must simply move all in the ‘Double select’ that has restricted to his rights.

Thanks in advance

#2

Ok, this is less complicated than it appears. I cannot give you a pre-built code because I don’t know your applications and the previous codes you have already wrote on your application. But I’ll give you some tools to create your own validation.

There are native ways on ScriptCase to check the where statement used and to change dynamically the where clause of an application. For example, if I want to dinamically change the WHERE clause of a form or grid application, I can put a global variable to be the WHERE clause:

SELECT
   do, re, mi, fa, sol, la, si
FROM
   table
WHERE
   [my_where]

This where can be dynamically changed on the [B]application start /B, this will be the default value, to show all records:

[my_where] = "1 = 1";

But I can change 1 = 1 to 1 = 0 to show nothing by default (or when there is no search).

Another good stuff is ScriptCase macros. There are several macros to check or change the WHERE clause in runtime:

sc_select_where - Adds values to the WHERE clause
sc_where_current - Returns the current WHERE clause of the application (filters include)
sc_where_orig - Shows the original application’s WHERE clause, without filter generated rules
sc_where_filter - Shows the filter generated rules, whthout the original WHERE clause.

These macros will be useful to your purposes. Depending on your application rules, these macros can build a solid validation system.

#3

Ok thanks for the explanation, now I can add a ‘security’ filter if no filter present for the given field.
I’ve see that ScriptCase does not like a WHERE clause in the SQL in a search application. It’s do a chained SELECT of the given SQL and not adds the new filter using this macros or similar, so now I can also do manually and the resulting SELECT is more quick than automatic generated by ScriptCase because now is not a chained SELECT is a SELECT with more AND options.