lookup filter

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

lookup filter

Postby josecnp1 » Wed Jan 11, 2017 6:37 pm

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.
josecnp1
 
Posts: 60
Joined: Thu Mar 27, 2014 8:42 pm

Re: lookups filter

Postby mobhar » Wed Jan 11, 2017 8:32 pm

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).
mobhar
 
Posts: 4300
Joined: Wed Jun 05, 2013 2:11 pm
Location: https://codecanyon.net/user/masino_sinaga/portfolio

Re: lookups filter

Postby josecnp1 » Thu Jan 12, 2017 6:26 pm

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
josecnp1
 
Posts: 60
Joined: Thu Mar 27, 2014 8:42 pm

Re: lookup filter

Postby Webmaster » Thu Jan 12, 2017 6:58 pm

Google "MySQL EXISTS" for some useful info.
Webmaster
 
Posts: 10296
Joined: Fri Aug 06, 2004 5:59 pm
Location: http://www.hkvstore.com/

Re: lookup filter

Postby josecnp1 » Thu Jan 12, 2017 9:12 pm

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
josecnp1
 
Posts: 60
Joined: Thu Mar 27, 2014 8:42 pm

Re: lookup filter

Postby mobhar » Thu Jan 19, 2017 8:42 pm

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.
mobhar
 
Posts: 4300
Joined: Wed Jun 05, 2013 2:11 pm
Location: https://codecanyon.net/user/masino_sinaga/portfolio


Return to General Discussion (PHPMaker)