hi, I can’t get this “lookup selecting” to work ( I set this in server->Lookup selecting of the lookup table):
function Lookup_Selecting($fld, &$filter) {
if ($fld->Name == "TBLDESCATT_ID")
$fld->Lookup->UserFilter = "TBLDESCATT_scuola = 'sp'";
}
Infos:
TBLDESCATT_ID is the lookup field in the lookup table
TBLDESCATT_scuola is a field in the lookup table
‘sp’ is a value for the field TBLDESCATT_scuolathe filter does not filter anything…
I cannot reproduce the issue. Mine is working properly in v2023. For example, I put this following code in Lookup_Selecting server event that belongs to the orders table of demo2023 project:
if ($fld->Name == "CustomerID")
$fld->Lookup->UserFilter = "CustomerID = 'RATTC'";
and I can only see one option in that field which is customer with ID = RATTC.
actarus99 wrote:
TBLDESCATT_ID is the lookup field in the lookup table
Did you mean TBLDESCATT_ID is the lookup field in the main table? If the field name of the lookup field (in the main table) and the field name of the link field (in lookup table) are not the same, make sure your $fld->Name == “TBLDESCATT_ID” is correct, it should compare the lookup field name, not the link field name.You better debug your code by checking if your if condition is met, e.g.
var_dump($fld->Name, $fld->Name == "TBLDESCATT_ID");
Hi
the field name of the lookup field (in the main table) and the field name of the link field (in lookup table) are NOT the same,.
TBLDESCATT_ID is the lookup field in the lookup table.In my SQL log appear …nothing…neither the var_dump
actarus99 wrote:
TBLDESCATT_ID is the lookup field > in the lookup table
arbei wrote:
make sure your > $fld->Name == “TBLDESCATT_ID” > is correct, it should compare the lookup field (in the > main > table) name, > not > the link field name (in the > lookup > table).
in main table I have TBLATT_TBLDESCATT_ID as lookup field. This is linked to TBLDESCATT_ID in the lookup table. It works.In my lookup table I have:
TBLDESCATT_ID
TBLDESCATT_Tipologia
TBLDESCATT_Descrizione
TBLDESCATT_scuola
my actual code is positioned in server events->table specific->Common->Lookup_selecting of the lookup table:
function Lookup_Selecting($fld, &$filter) {
if ($fld->Name == "TBLATT_TBLDESCATT_ID")
var_dump($fld->Name, $fld->Name == "TBLATT_TBLDESCATT_ID");
$fld->Lookup->UserFilter = "TBLDESCATT_scuola = 'sp'";
}
actarus99 wrote:
var_dump($fld->Name, $fld->Name == “TBLATT_TBLDESCATT_ID”);
What was the output of var_dump()? You may check HTTP response to find them.Also enable Debug and error logging, you’ll find the error message in the log, see https://www.hkvforums.com/viewtopic.php?t=54123 on how to log.
in preview I see:
{
"page": "TabellaAttivitaAdd",
"field": "TBLATT_TBLDESCATT_ID",
"result": "OK",
"records": [],
"totalRecordCount": 0,
"sql": "SELECT `TBLDESCATT_ID` AS `lf`, `TBLDESCATT_Descrizione` AS `df`, `TBLDESCATT_scuola` AS `df2`, '' AS `df3`, '' AS `df4` FROM `tabella_descrizione_attivita` WHERE 1=0 ORDER BY `TBLDESCATT_Descrizione` ASC"
}
No trace of var dumpI specify that TBLATT_TBLDESCATT_ID has a parent filter…
You’d better post your tables schema (CREATE TABLE …) including some records inside those both tables (INSERT INTO …) so others could comprehend it by using the real data in those of your tables.
the MAIN table:
CREATE TABLE `tabella_attivita` (
`TBLATT_ID` int(11) NOT NULL,
`TBLATT_TBLTIPOATT_ID` int(11) NOT NULL,
`TBLATT_TBLDESCATT_ID` int(22) DEFAULT NULL,
`TBLATT_Data` date DEFAULT NULL,
`TBLATT_Dalle` datetime DEFAULT NULL,
`TBLATT_Alle` datetime NOT NULL,
`TBLATT_Classe` int(6) NOT NULL,
`TBLATT_TBLPLESSI_ID` int(11) NOT NULL,
`TBLATT_NumOre` int(2) NOT NULL,
`TBLATT_FORFETTARIO` int(2) NOT NULL,
`loginby` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
`TBLATT_inserimento` datetime NOT NULL,
`TBLATT_note` text COLLATE utf8mb4_unicode_ci,
`modificato` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
`autorizzato` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
some records of the main table
INSERT INTO `tabella_attivita` (`TBLATT_ID`, `TBLATT_TBLTIPOATT_ID`, `TBLATT_TBLDESCATT_ID`, `TBLATT_Data`, `TBLATT_Dalle`, `TBLATT_Alle`, `TBLATT_Classe`, `TBLATT_TBLPLESSI_ID`, `TBLATT_NumOre`, `TBLATT_FORFETTARIO`, `loginby`, `TBLATT_inserimento`, `TBLATT_note`, `modificato`, `autorizzato`) VALUES
(1, 1, 12, '2022-09-08', NULL, '2022-09-12 00:00:00', 0, 0, 45, 0, 'alex', '2022-09-13 12:52:37', 'asdsd asda sd asd asd asd asdasd sdas dasd as das dasd', '', 0),
(2, 1, 10, '2022-09-07', NULL, '2022-09-14 00:00:00', 0, 0, 56, 0, 'alex', '2022-09-14 15:44:00', NULL, '', 0),
(3, 1, 30, '2022-09-08', NULL, '2022-09-14 00:00:00', 0, 0, 44, 0, 'Admin', '2022-09-14 15:50:59', NULL, '', 0),:
the lookup table:
CREATE TABLE `tabella_descrizione_attivita` (
`TBLDESCATT_ID` int(11) NOT NULL,
`TBLDESCATT_Tipologia` int(11) NOT NULL,
`TBLDESCATT_Descrizione` varchar(58) DEFAULT NULL,
`TBLDESCATT_scuola` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
some records of the lookup table:
INSERT INTO `tabella_descrizione_attivita` (`TBLDESCATT_ID`, `TBLDESCATT_Tipologia`, `TBLDESCATT_Descrizione`, `TBLDESCATT_scuola`) VALUES
(1, 2, 'Supplenze', 'sspg'),
(2, 2, 'Aggiornamento', 'sspg'),
(3, 2, 'Potenziamento formativo\r\n(ore con o per gli alunni)', 'sspg'),
(10, 1, 'Collegio docenti unitario', 'sspg'),
(11, 1, 'Collegio di sezione', 'sspg'),
actarus99 wrote:
my actual code is positioned in server events->table specific->Common->Lookup_selecting of > the lookup table
No, the Lookup_Selecting server event should be entered for the main table (tabella_attivita with the TBLATT_TBLDESCATT_ID field), not the the lookup table (tabella_descrizione_attivita with the TBLDESCATT_ID).
In addition, let’s say you have put the code in Lookup_Selecting server event that belongs to the main table (tabella_attivita), then you should evaluate your filter string.
As you can see, from the data in your lookup table, there is no value that equals to ‘sp’ for TBLDESCATT_scuola field. All of the values for that field is ‘sspg’.
That’s why your filter seems not working at all, and there are no records found.
ok,
what I need is filter the values of the lookup table named tabella_descrizione_attivita WHERE TBLDESCATT_scuola = ‘sp’ or TBLDESCATT_scuola = ‘sspg’ depending of a field in another table dtd_users.
So…my actual code
function Lookup_Selecting($fld, &$filter) {
if ($fld->Name == "TBLATT_TBLDESCATT_ID")
// var_dump($fld->Name, $fld->Name == "TBLATT_TBLDESCATT_ID");
$scuolautente = ExecuteScalar("SELECT scuola FROM dtd_users WHERE user_login = '".CurrentUserName()."'");
$scuola = ExecuteScalar("SELECT TBLDESCATT_scuola FROM tabella_descrizione_attivita WHERE user_login = '".CurrentUserName()."'");
$fld->Lookup->UserFilter = "$scuolautente = $scuola";
}
actarus99 wrote:
$fld->Lookup->UserFilter = “$scuolautente = $scuola”;
Your code adds a boolean to the filter, that will only enable or disable existing filter, not adding filter.
I need to propose in the select field not all the values stored in the lookup table but only some of these.I try to use the Filter setting under Lookup Table panel.When I set
“TBLDESCATT_scuola = ‘sp’”
the filter works perfectly!BUT I WANT this:
"TBLDESCATT_scuola = '"ExecuteScalar("SELECT scuola FROM dtd_users WHERE user_login = '".CurrentUserName()."'")"'"
I got syntax error…tried many times…Is for You a promising way?
Try:
$filter_value = ExecuteScalar("SELECT scuola FROM dtd_users WHERE user_login = '".CurrentUserName()."'");
"TBLDESCATT_scuola = '" . $filter_value . "'";
actarus99 wrote:
“TBLDESCATT_scuola = ‘“ExecuteScalar(“SELECT scuola FROM dtd_users WHERE user_login = '”.CurrentUserName().”’”)“'”
I got syntax error…tried many times…
You may see String Operators to learn how to concatenate PHP string, it should be like:
"TBLDESCATT_scuola = '" . ExecuteScalar("SELECT...") . "'"
WONDERFUL!
Now the select field can filter the desired values of the lookup table!
I’m going to study String Operators!TNX!!!