Page 1 of 1
lookup filter
Posted: Wed Jan 11, 2017 6:37 pm
by josecnp1
I would like to have a solution for this problem:
I have two tables: one main and one secondary
Main Table: usuario
id_nombre (Int)
nombre (Varchar)
Secondary table: nombre
id_nom (int)
nom (Varchar)
i use lookup filter in the main table
In the filter section I put this code:
"id_nom
<>'".n_nombre()."'"
Previously I put this code in global code:
function n_nombre(){
$nom = ew_ExecuteScalar("SELECT nom FROM usuario
");
return $nom;
}
The filter only works with the first result. When I create another new record I see all records in the child table. And I'm only interested in seeing the records that are not in the main table recorded.
thanks.
Re: lookups filter
Posted: Wed Jan 11, 2017 8:32 pm
by mobhar
josecnp1 wrote:
The filter only works with the first result.
Yes, because your $nom will return only the first record that found.
josecnp1 wrote:
And I'm only interested in seeing the records that are not in the main table recorded.
Then you need to define your own filter for the child table, too (similar to your filter in main table, but vice versa).
Re: lookups filter
Posted: Thu Jan 12, 2017 6:26 pm
by josecnp1
I've tried it in every way but it does not work
I put in global code:
function n_taquilla(){
$rs = Conn()->Execute("SELECT n_taquilla FROM taquillas
");
}
and this alternativa:
function n_taquilla(){
global $conn;
$query = "SELECT n_taquilla FROM taquillas
";
$conn->Execute($query);
}
and:
function n_taquilla(){
$GLOBALS["conn"]->Execute("SELECT n_taquilla FROM taquillas
");
}
I'm lost. This error continues
Catchable fatal error: Object of class mysqlt_driver_ResultSet could not be converted to string in C:\Web\mantenimiento\taquillasadd.php on line 1123
Re: lookup filter
Posted: Thu Jan 12, 2017 6:58 pm
by Webmaster
Google "MySQL EXISTS" for some useful info.
Re: lookup filter
Posted: Thu Jan 12, 2017 9:12 pm
by josecnp1
Use this query and it works correctly, give the correct results:
SELECT DISTINCT id_llave FROM v_ubicacion_llave WHERE NOT EXISTS (SELECT n_taquilla FROM taquillas WHERE taquillas.n_taquilla = v_ubicacion_llave.id_llave)
And I create the filter:
"id_llave
= (SELECT DISTINCT id_llave FROM v_ubicacion_llave WHERE NOT EXISTS (SELECT n_taquilla FROM taquillas WHERE taquillas.n_taquilla = v_ubicacion_llave.id_llave))"
But in debug mode I still give the same error:
(mysqlt): SELECT id_llave
, n_taquilla
AS DispFld
, '' AS Disp2Fld
, '' AS Disp3Fld
, '' AS Disp4Fld
, id_ubicacion
AS SelectFilterFld
, '' AS SelectFilterFld2
, '' AS SelectFilterFld3
, '' AS SelectFilterFld4
FROM v_ubicacion_llave
WHERE id_llave
= (SELECT DISTINCT id_llave FROM v_ubicacion_llave WHERE NOT EXISTS (SELECT n_taquilla FROM taquillas WHERE taquillas.n_taquilla = v_ubicacion_llave.id_llave)) ORDER BY n_taquilla
ASC
Error (1242): Subquery returns more than 1 row
I'm still lost
Re: lookup filter
Posted: Thu Jan 19, 2017 8:42 pm
by mobhar
Then make sure the SQL will return 1 record only. Use LIMIT clausa that belongs to MySQL at the end of your SQL to limit the result become one record.