Display grid with filter on

Applications Search
#1

I’ve looked through the forums to try to find an answer to this but haven’t been able to do so. If it has been answered elsewhere I apologise, please direct me to the answer.

I have a grid that displays Timesheets for employees. When the grid is initially displayed I would like it to show the last two weeks of Timesheets. I’ve found that if I use a where clause in the underlying SQL that it will over ride the search criteria that may be entered later. I’ve looked at the onScriptInit event for the search criteria as an option but I don’t know how to trigger it to occur as soon as the application is first loaded (assuming that is the solution).

Additionally, when using the onScriptInit event for the Search is it possible to supply two values for the ‘Between’ option? I can supply one so far but would like to provide the second also for my users.

Any help would be greatly appreciated.

#2

In the onscript init you set a filter. This filter is then used in the sql statement. The most easiest way to do that is by using a global variable;

onscriptinit:
[glob_filter]=something

in the sql

where mycriteria=[glob_filter]

or

where [glob_filter]

But I’m not sure what happens if you set other criteria by using search. It’s easy to find out if you set the debugoptions in applicationsection to true, then it will show you what sql is generated. It’s useually very helpful if you have to fiddle around to see what’s happening. Hope this helps.

#3

Thank you Albert.

I’m already using a global variable as you suggested. I’ve investigated further and if I use the built in search it appears to be doing the equivalent of sc_select_where(add). This isn’t very useful when I want to use a different date range from the one already passed in originally using the global variable as it just appends an additional date criteria to the end of the WHERE clause. I attempted to update/clear the sc_where_current statement during the search OnScriptInit event but have found that it strangely isn’t passed to the search application so I couldn’t modify it there.

If there are no other suggestions on how I can clear and modify the WHERE clause I’ll write my own application to perform the filtering and bypass the inbuilt search application. I’ll put in a feature request for an sc_select_where(clear) function in Scriptcase as I would much rather use the existing functionality.

Thanks again for your help.

#4

You could make a select that ands with where 1=1 and then via a variable as aducom mentioned add criteria.
So you get: where 1=1 [myfilter]
Then build some code like for example:
[myfilter]=“and (upper(NAME) like ‘JOHN%’)”;
Then all you need to do is build your own filter and redirect to the same page. Would that be a viable solution?