How to pass parameters from search in a grid

[Joe]

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

$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?

[jsbinca]

May be I got something wrong but I would suggest a different approach.
Use something like this as the SQL statement for your grid.

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

[Joe]

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

[jsbinca]

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

[Joe]

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

$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

[jsbinca]

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

[Joe]

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

[Joe]

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

Joe

[jsbinca]

You just have to join all the tables you need.

jsb

[Joe]

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

[jsbinca]

You have to include all the fields you want to search for in the 'SELECT' clause of the grid..
Just a sample:

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

[Joe]

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