Grid with parameters in SQL

Applications Grid / Reports
, , , ,
#1

Hello there,

it’s been a rather long time, since I have been working with Scriptcase.
Now I’m back again :-), but unfortunately a lot of my knowledge about Scriptcase is forgotten…

What I would like to do is:
I have a very complex SQL statement, referring many tables. I want to display the results in a Grid application.
So far, that’s working fine, but I have some values, like integer thresholds, Date values and so an.
I would like to present the user some kind of form, by which he can set values to these parameters and pass these to the grid which will then run the SQL with the parameter values the user entered.

As far as I remember, using a “Form application” isn’t the right way, since Form applications are used for inserting new records into a table, which is not my case.
Could you please give me some information on how I can achieve this?

Thanks in advance!

#2

Hi,
actually, you have two choices. You can start the grid application in Search mode where the user can define the filter criteria. This approach has the benefit to be able to save the defined filter for repeated use. The other possibility would be to run a control application first to gather all the parameters and pass them as global variable to your grid application.
But I think the first option would be your best bet.
In the Grid Settings select Start by Search (Yes), that followed head over to the Search section and create your form. That’s all.

jsb

#3

Hi jsbinca!

Thanks for your answer.
I tried both of your suggestions.
Option 2) using the control application is pretty straight forward to set up and implement.

But option 1) seems a bit more different in my case: Since I do some PHP-preprocessing triggered in the Init event, I need to store the search criteria into global variables and read these variables from within the init-handler to the the preprocessing.
In the search fields I created, there seems to be no option to create variables with the provided values.

How can I do this?

#4

Hi,
you are right and the control application approach is most likely the better choice.

To make use of those values in the grid you have to iterate through the fields in the onValidate/onValidateSuccess event of your control application.
In case you need those values on different places, store them in an array and pass the array to the grid.

If you need those values in the SQL statement (i.e. where clause) build your WHERE clause and stick it in a variable and pass it along when redirecting to the grid.
Here’s a little mockup:

$mywhere = 1;
if( {field1} > 0)
{
$mywhere .= " AND field1 = ".{field1};
}
if({yob} > 0)
{
if({regyear} > 0)
{
$mywhere .= " OR ";
}
else
{
$mywhere .= " AND ";
}
$mywhere .= “YEAR(registered) = “.({yob}-2);
}
sc_redir(grid_ssp,glo_where=$mywhere,”_self”);

Your SQL statement of the grid looks like
SELECT * FROM table WHERE [glo_where]

I hope this helps.