Announcement

Collapse
No announcement yet.

Simple filter by button?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Simple filter by button?

    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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\tradie_acc ts\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

      Comment


      • #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

        Comment


        • #5
          Originally posted by fiscal View Post
          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
          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.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #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

            Comment


            • #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

              Comment


              • #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]";

                5. 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.
                6. 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=$filt er_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)

                Comment

                Working...
                X