lookup filter

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
josecnp1
User
Posts: 73

lookup filter

Post 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.


mobhar
User
Posts: 11660

Post 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).


josecnp1
User
Posts: 73

Post 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


Webmaster
User
Posts: 9425

Post by Webmaster »

Google "MySQL EXISTS" for some useful info.


josecnp1
User
Posts: 73

Post 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


mobhar
User
Posts: 11660

Post 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.


Post Reply