Announcement

Collapse
No announcement yet.

How to pass parameters from search in a grid

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

  • How to pass parameters from search in a grid

    Hi,

    I make technical reports for machines. Every year a new report is done with a license date for one year which is
    in a data field "valid until".
    After the years you have some reports for one machine.
    In a grid overview of the machines I like to show the field "valid until" from the latest technical report that was
    made for the specific machine.

    I tried in the grid events OnRecord

    Code:
    $sqlabndat = "SELECT MAX( valid_until )
    FROM machine_data
    GROUP BY valid_until DESC
    LIMIT 1";
    
    sc_lookup(licence_data, $sqlabndat);
    
    if ({licence_data} === false) {
    	echo "<script type='text/javascript'>alert('Access error. Message = " . {licence_data_error} . "');</script>";
    
    } elseif (empty({licence_data})) {
       {valid_until} = 0;
    
    } else {
       {valid_until} = {licence_data[0][0]};	
    }
    this shows the latest valid_until date of ALL machines.
    I start the grid with a search form that displays the specific machine.

    How can I set and transfer the parameter (machine_id) in the search form to the grid so that the search result
    shows the correct valid_until date for the machine?

    I know I need a WHERE in the sql query.... WHERE machine_id=???????

    Any suggestions?

  • #2
    May be I got something wrong but I would suggest a different approach.
    Use something like this as the SQL statement for your grid.
    Code:
    SELECT  m1.machine_id, m1.machine_name, m1.valid_until, ...
    FROM machine_data m1
            INNER JOIN
            (
                SELECT  machine_id, MAX(valid_until) valid_until
                FROM    machine_data
                GROUP   BY macine_id
            ) m2 ON m1.machine_id = m2.machine_id AND m1.valid_until = m2.valid_until
    This gives you one record per machine with the latest date. Now you can just use Quicksearch (or advanced search) to filter the for a specific machine.

    jsb

    Comment


    • #3
      Hi jsb, thanks for your help

      I tried your sql query but the result is strange to me. It brings "00.00.1" as a date but should be 2016-12-19 regarding my data set.
      To be honest I don't really understand the query and where this result comes from.... so I don't know where to search for the problem.

      Joe

      Comment


      • #4
        It looks like your date field in the grid is not correct configured.
        Try the statement in phpmyadmin or the built in Database Builder to make sure it is working.

        What it does is pretty simple. First it grabs the id and max. date per machine (as a 'temporary' table m2)
        and than combines the original table m1 with the temporary table m2 and selects all the records from m1
        where id and date are equal.

        jsb

        Comment


        • #5
          In phpmyadmin I get two dataset results with the query:
          - the machine with the id "1" with the correct date of the latest valid_until
          - the machine id of the one I searched for in the search form with the correct date (this would be the result of date I 'm looking for)

          My complete OnRecord code looks like this

          Code:
          $sqlabndat = "SELECT  m1.machine_id, m1.valid_until
          FROM machine_data m1
                  INNER JOIN
                  (
                      SELECT  machine_id, MAX(valid_until) 
          			valid_until
                      FROM    machine_data
                      GROUP   BY machine_id
                  ) m2 ON m1.machine_id = m2.machine_id AND m1.valid_until = m2.valid_until";
          
          sc_lookup(licence_data, $sqlabndat);
          
          if ({licence_data} === false) {
          	echo "<script type='text/javascript'>alert('Access error. Message = " . {licence_data_error} . "');</script>";
          
          } elseif (empty({licence_data})) {
             {valid_until} = 0;
          
          } else {
             {valid_until} = {licence_data[0][0]};	
          }
          Joe
          Last edited by Joe; 05-29-2015, 07:15 AM.

          Comment


          • #6
            The sql statement is meant to be used as the statement for your grid itself, NOT in the onRecord event.

            Since I don't know your application I assumed you have a grid based on the machine_data table. If you use the above statement
            for your grid, as I said, you will get a list with one record per machine with the latest date. You can of course add as many fields
            from the machine_data table as you need to the first SELECT.
            There is nothing to do in the onRecord event.

            Add the Quicksearch to the tool bar and all you need to do is type in the machine you are looking for.

            jsb

            Comment


            • #7
              In the application should be an overview grid that displays mainly data from the last technical report, so I think I should create a new grid and start again. But there are also some fields to be shown from another table (i. e. the data of the report writer) so how could I combine all these fields from different tables in your sql query?
              And - I don't have a quick search in my grid - I start the grid with a search.
              Joe

              Comment


              • #8
                .... and because the main data of the machines (machine_name, machine_location ....) are in another table the search doesn't work anymore.

                Joe

                Comment


                • #9
                  You just have to join all the tables you need.

                  jsb

                  Comment


                  • #10
                    Ok after hours of trial and error I got it ;-)

                    But the search doesn't work anymore. Same with the quicksearch.
                    FYI I'm searching for fields of the joined table.

                    Joe

                    Comment


                    • #11
                      You have to include all the fields you want to search for in the 'SELECT' clause of the grid..
                      Just a sample:
                      Code:
                      SELECT 
                          p.program_id,
                          p.program_name AS program,
                          p.program_type AS type,
                          a.city AS city,
                          sp.province AS province
                      FROM 
                          program p 
                          LEFT OUTER JOIN program_type pt ON p.program_type_id = pt.program_type_id
                          LEFT OUTER JOIN address a ON p.address_id = a.address_id
                          LEFT OUTER JOIN state_province sp ON sp.state_province_id = a.state_province_id
                      jsb

                      Comment


                      • #12
                        Thanks,
                        what happened is that if you manipulate the existing sql statement and adding fields manually to the grid after that SC doesn't recognize that. The fields are not shown even the sql statement is correct and nothing of the search function works as expected. That wasn't helpful for me as a beginner.
                        If you create a new grid and insert the sql statement before the creating process starts everything works fine.

                        I beg the SC team will fix this bug.

                        Joe

                        Comment

                        Working...
                        X