Simple filter by button?

Applications Grid / Reports
#1

Hi All,

If I have a grid and the SQL WHERE clause is dependent on a numeric figure eg. type <= 3

This works great and excludes the records of record type 4, but can I have a button on the grid toolbar that will reset the grid and use type <= 4 so I can see the extra records?

I have tried some code under a PHP button, that sets a global variable that in turn is used to alter the WHERE clause in the scriptinit event, and the button then does a sc_redir(grid,[gobal_var]); to sort of “restart” the application.

Needless to say, that did not work.

I do not really wish to use the advanced search as it would confuse the user.

Any ideas?

Thanks

Tony

#2

Yes it is possible. First you have to add a global variable to your sql statement, something like ‘where [myarg]’; Initially you fill this with a filter. In the button code you change [myarg] to the filter you need. Then do a sc_exit(ref); to restart the same application.

#3

Thanks Audcom,

However, I seem to have a bit of an issue with assigning the where clause to the global variable.

I get this error message when I run the application -

Parse error: syntax error, unexpected ‘}’ in C:\Program Files (x86)\NetMake\v8\wwwroot\scriptcase\app radie_accts\grid_allocations_bas\index.php on line 2144

This is the line of code in the source file -

2138| if (!isset($this->sc_temp_filter_number) || empty($this->sc_temp_filter_number)){
2139|
2140| if ($this->sc_temp_filter_number == “1”){
2141| $this->sc_temp_vwhere = “(ta_date BETWEEN ’ $this->sc_temp_bas_startdate’ AND ‘$this->sc_temp_bas_finishdate’) AND ta_bastype < 6”
2142| }
2143| if ($this->sc_temp_filter_number == “2”){
2144| $this->sc_temp_vwhere = “(ta_date BETWEEN ’ $this->sc_temp_bas_startdate’ AND ‘$this->sc_temp_bas_finishdate’) AND ta_bastype < 9”
2145| }else{
2146| $this->sc_temp_vwhere = “(ta_date BETWEEN ’ $this->sc_temp_bas_startdate’ AND ‘$this->sc_temp_bas_finishdate’) AND ta_bastype < 6”
2147| }
2148|
2149| }

This is that code in the PHP function I created -

if (!isset([filter_number]) || empty([filter_number])){

if ([filter_number] == “1”){
[vwhere] = “(ta_date BETWEEN ’ [bas_startdate]’ AND ‘[bas_finishdate]’) AND ta_bastype < 6”
}
if ([filter_number] == “2”){
[vwhere] = “(ta_date BETWEEN '”.[bas_startdate]."’ AND ‘".[bas_finishdate]."’) AND ta_bastype < 9"
}else{
[vwhere] = “(ta_date BETWEEN ’ [bas_startdate]’ AND ‘[bas_finishdate]’) AND ta_bastype < 6”
}

}

The parse error states there is an unexpected ‘}’ but I cannot see it anywhere. All other events have properly terminated code, so it is nothing that I have written, at least I cannot see it.

Any help on that, or is it maybe the fact that it has dates in the where clause.

At this stage I am stumped.

Thanks

Tony

#4

I found the reason for the parse error - I forgot the ; after each line assigning the where clause to the global variable.

However, it still is not displaying the extra rows when I click on the button.

I am assuming that the global variables be set to IN (I did try OUT and it did not work either).

Anything further?

Thanks

Tony

#5

[QUOTE=fiscal;35441]I found the reason for the parse error - I forgot the ; after each line assigning the where clause to the global variable.

However, it still is not displaying the extra rows when I click on the button.

I am assuming that the global variables be set to IN (I did try OUT and it did not work either).

Anything further?

Thanks

Tony[/QUOTE]

Set the debug option in the application all to on so that you can see the sql statements fired. I have a strong feeling that you are overwriting your filters with it’s initial state.

#6

Right, I have gone back to bare bones -

In event OnScriptInit I placed the following code

[vwhere] = “(ta_date BETWEEN ’ [bas_startdate]’ AND ‘[bas_finishdate]’) AND ta_bastype < 6”;

This correctly displays the data (without the extra rows)

In the button PHP code I place the following

[vwhere] = “(ta_date BETWEEN ’ [bas_startdate]’ AND ‘[bas_finishdate]’) AND ta_bastype < 9”;
sc_exit(ref);

When I click on the button, the extra trows are not displayed.

If I manually use the string in the SQL WHERE then it does display extra data as expected.

So maybe it is sc_exit(ref); that is not functioning as expected?

Thanks

Tony

#7

Thanks Albert,

I have set the debug on in the application. The original SQL statement is as follows

SELECT
ta_id,
ta_tran,
ta_date,
ta_ref,
ta_glcode,
ta_amount,
ta_gst,
ta_net,
ta_drcr,
ta_bastype,
ta_note,
ta_type
FROM
allocations
WHERE
[vwhere]
ORDER BY
ta_bastype, ta_date

When I run the debug there are 2 select statements shown for the allocations table. The first one is the first message issued -

SELECT ta_tran, ta_gst, ta_net, ta_drcr, ta_bastype, ta_glcode from allocations where (ta_date BETWEEN ’ 2015-01-01’ AND ‘2015-03-31’) AND ta_bastype < 6 order by ta_bastype asc

The second select statement is shown just before the grid is displayed -

SELECT ta_date, ta_type, ta_ref, ta_id, ta_tran, ta_glcode, ta_amount, ta_gst, ta_net, ta_drcr, ta_bastype from allocations where (ta_date BETWEEN ’ 2015-01-01’ AND ‘2015-03-31’) AND ta_bastype < 6 order by ta_bastype asc, ta_date asc LIMIT 0,12

The grid is grouped by the ta_bastype field, so I am wondering if that may be affecting the process? maybe I am missing a setting in the group by section, although I cannot see on that would be relevant.

Thanks

Tony

#8

SOLVED - Simple Filter by Button

This is for reference in case anyone else faces the same thing. It can be very frustrating, but at least the answer is easy to implement.

  1. The conditional where clause was only different on one numeric figure.
  2. I made the numeric figure into a global variable [filter_number]
  3. The app was being called by a control app, so I set the global variable’s initial value in that app, and set the type to out.
  4. I placed the where clause as it’s own global variable in scriptinit

[vwhere] = “(a.ta_date BETWEEN ’ [bas_startdate]’ AND ‘[bas_finishdate]’) AND a.ta_bastype < [filter_number]”;

  1. Under the button code I did NOT use sc_exit(ref) as it does NOT work, it keeps the original where clause and does NOT use the updated one.
  2. Instead, I set the numeric value and did a sc_redir as follows.

// code under the toolbar button
if([filter_number] == 6){
$filter_num = 9;
}else{
$filter_num = 6;
}

sc_redir(grid_allocations_bas1,filter_number=$filter_num,"_self");

This works and filters the grid properly

Now I just have to work out how to change the label on the button (probably the same way)